エクセルでフォームのDropDownとオートフィルタのDropDown

質問

エクセルでフォームのDropDownとオートフィルタのDropDownについて。
エクセル2000です。
ワークシート上にフォームのDropDown(コンボボックス)や、オートフィルター、そして入力規則でリストの設定があります。

今のVBAの書き方だとこれらはすべてShapeです。
そうすると、フォームのDropDownとオートフィルター、そして入力規則のリストの識別がVBAではとても難しいです。これらはすべてTypeは msoFormControlですし、さらにFormControlTypeも xlDropDownとまったく同一です。

なんとか識別する方法がないか昨夜から試行錯誤の結果、オートフィルター、そして入力規則のリストはシート上に貼り付けてあるわけではないためか、TopLeftCellが取得できないことに気づきました。そこで下記のような識別のための実験コードを書いてみたのですが、多分もっといい方法があるのではと思い、質問させていただきます。

もちろん、Shapesコレクションを使用せず、古いDrawingObjectsコレクションを持ち出せばオートフィルターや入力規則は最初から対象外となるのは存じておりますのでそれ以外の方法をご教示くださいませ。
(o。_。)oペコッ.

Sub hantei()
　Dim i As Integer, x As String
　Dim Obj As Object
　　For Each Obj In ActiveSheet.Shapes
　　　　If Obj.Type = msoFormControl Then
　　　　　　If Obj.FormControlType = xlDropDown Then
　　　　　　　　On Error Resume Next
　　　　　　　　x = Obj.TopLeftCell.Address
　　　　　　　　On Error GoTo 0
　　　　　　　　If x = "" Then
　　　　　　　　　　i = i + 1
　　　　　　　　Else
　　　　　　　　　　x = ""
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　Next
　　MsgBox i & "個、入力規則のリストまたはオートフィルターがあります。"
End Sub

回答No.3

end-u · ベストアンサー

意図が理解できません。
『DrawingObjectsコレクションは古い書き方だから使いたくないのでShapesで回したい』
という事ではなかったのですか？

OLEObjectsは別に古くないからそのまま使えば良いだけでは？
どうしてもShapesで回したいなら
Dim s As Shape
For Each s In ActiveSheet.Shapes
　　If s.Type = msoOLEControlObject Then
　　　　Debug.Print s.Name, s.OLEFormat.ProgId
　　End If
Next
とでも？

#以下個人的意見。
[ActiveXコントロール]をシート上に多数配置して使うのは個人的にはあまり好きではありません。
>素直にDrawingObjectsを使う事をおすすめします。
って書いたように、[フォーム]コントロールは元々Excelライブラリのものですから、
シート上に配置したとしても親和性が高く、『古い』というより『枯れて安定している』ように思います。
安定した[フォーム]コントロールを使うのであればそれに適したDrawingObjectsコレクションを使うのが良いでしょう。
何に拘りがあるのかわかりませんが、扱うObjectによってよりベターな手法を選択するのが普通だとおもいますけど？

お礼

何度もありがとうございます。
shapeでまわした場合は.OLEFormat.ProgId　で取得できるんですね、初めて知りました。

別にたいしてこだわりがあるわけではないんです。
先ほども書いたようにOLEオブジェクトはなじみがないので書き方自体を知らず、今回は実験的なコードで試していて湧いた疑問だったのです。
end-uさまにはご丁寧にご回答いただきましてほんとうにありがとうございました。

回答No.2

end-u

>あくまでShapeとしてとらえて、そのなかでのDropDownの識別方法を...
言い切ってしまっていいものかどうか一抹の不安はありますが、
他には無いようです。

FormControlTypeまで一緒ですから、
ご提示コードのように、アクセスしたらエラーが出るプロパティがある事を利用するしかないと思います。

Sub test()
　　Dim s As Shape
　　Dim n As Long

　　With ActiveSheet
　　　　For Each s In .Shapes
　　　　　　If s.Type = msoFormControl Then
　　　　　　　　If s.FormControlType = xlDropDown Then
　　　　　　　　　　If ddChk(s) Then
　　　　　　　　　　　　n = n + 1
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　MsgBox "DropDowns " & n & "/Shapes " & .Shapes.Count
　　End With
End Sub

Function ddChk(ByRef dd As Object) As Boolean
　　On Error Resume Next
　　ddChk = (VarType(dd.Locked) = vbBoolean)
　　On Error GoTo 0
End Function

>Shapesコレクションを使用せず、古いDrawingObjectsコレクションを持ち出せば
>オートフィルターや入力規則は最初から対象外となるのは存じております...
素直にDrawingObjectsを使う事をおすすめします。

『古い』やり方をしたくないならば『古い』オブジェクトを使わない事です。
[フォーム]コントロールのコンボボックスはExcel.DropDownです。
（ExcelライブラリのDropDownクラス。オブジェクトブラウザで[非表示のメンバを表示]して確認できます）
Excelライブラリのものを扱う時に、
同じExcelライブラリで用意されてるDrawingObjectsコレクションを使う事を
私は古いとは思えないんですけどね。

お礼

ご回答ありがとうございます。

>『古い』やり方をしたくないならば『古い』オブジェクトを使わない事です。

なるほどそのとおりですね。
実は新しいOLEオブジェクトはこれまで使ったことがなく、なじみがないのです。
フォームコントロールをやめて全部OLEオブジェクトに変えれば問題はなくなりますね。

試行錯誤の結果、

Sub test1()
　　With ActiveSheet
　　　　For Each s In .OLEObjects
　　　　　　i = i + 1
　　　　　　.Cells(i, 1) = s.Name
　　　　　　.Cells(i, 2) = s.progID
　　　　Next s
　　End With
End Sub

でなんとか、名前と種類が特定できるようになりましたが、これも
For Each s In .OLEObjects　をFor Each s In .Shapes にすると、progIDがエラーになり、種類を判別できません。
古いフォームコントロールは使わないとすると、どうやってOLEObjectの種類を区別したらよいのでしょうか？

回答No.1

kybo

フォームのコンボボックスだけの数が数えればいいのでしょうか？

Sub macro()
Dim i As Integer, Obj As DropDown
For Each Obj In ActiveSheet.DropDowns
i = i + 1
Next
MsgBox i & "個、フォームのコンボボックスがあります。"
End Sub

お礼

すみません。わたしの質問が手を抜き過ぎてました。
もちろんDropDownsコレクションで行けるのは、DrawingObjects同様存じております。
ただ、DropDownsコレクションも、DrawingObjectsコレクションとおなじく古いやりかたですよね。それに手を抜いてコンボボックスのことしか書きませんでしたが、実際にはワークシート上の他のmsoFormControlも対象とするのです。
ですからあくまでShapeとしてとらえて、そのなかでのDropDownの識別方法を探していたのです。ごめんなさい。