オブジェクト名を変化させながら使うには? (ワークシート)

Question 54.7 Excel VBA Borad (掲示板)より Previous Next
始めまして、Excel初心者のriEです。もう、一週間悩んでおります。コンボボックスのオブジェクトを動的に取得したいのですが、うまく動きません。
こちらの[オブジェクト名を変化させながら使うには?]を参考にしたのですが、標準モジュール上に記述しているせいか下記の(方法1)では “オブジェクトが必要です”と出てしまい、(方法2)では “subまたはFunctionが定義されていません”と表示されてしまいます。
標準モジュール上ではcontrolsコレクションは使えないのでしょうか? 使えないとしたら、うまくcontrolsをとってこれる方法はないでしょうか? お手数ですが、お分かりになる方、ぜひ教えてくださいm(_ _)m
コンボボックスにリストを設定する---------------
controls(オブジェクト名)を使う方法
(方法1)===============================
Dim myCtrl As Control
Dim mycnt As Long
    For Each myCtrl In Controls
        If TypeName(myCtrl) = "ComboBox" Then
            If myCtrl Then mycnt = mycnt + 1
        End If
    Next
    MsgBox mycnt
(方法2)================================
Dim y As Long
Dim mycnt As Long
    For y = 1 To 5
        Controls(ComboBox & y).AddItem (nms(i).Name)
    Next
    MsgBox mycnt
----------------------------------------
Answer   Copyright (C) 2002.9.3 永井善王
そのせいでしょうね。標準モジュールに記述したい場合は、次のようにオブジェクト名を付けます。
(方法1)===============================
Dim myCtrl As Control
Dim mycnt As Long
    For Each myCtrl In UserForm1.Controls
        If TypeName(myCtrl) = "Userform1.ComboBox" Then
            mycnt = mycnt + 1
        End If
    Next
    MsgBox mycnt
おそらく、6行目も上記のように修正が必要でしょう。 If myCtrl.Value Then は、トグルボタンがオンのものだけをカウントしています。
(方法2)は詳しく見てないですが、
controls("combobox" & y).AddItem (nms(i).Name)と " が抜けているように思います。
Question
お答えありがとうございますっ!m(_ _)m
教えていただいたとおり(方法1)は、
UserForm1.Controls をつけたらエラー[オブジェクトが必要です]が解消しました!
ただ、コンボボックスはUserForm1上ではなくsheet1上にあるため、
sheet1.Controls のように入力してみました。
・・・が、worksheet上ではcontrolsコレクションの定義が無いようで、再びエラー[メゾットまたはデータメンバが見つかりません]と出てしまいます。
説明不足で申し訳ありませんが、sheet1上のコンボボックスは controls で取ってこれないものなんでしょうか???
解決しました(*>_<*)
上記に似た問題を他のページで見つけることが出来ました!
worksheet上ではcontrolsコレクションは使用できないようです。下記の処理で回避することができました。
また、わからないことが出てきたら質問させてくださいm(_ _)m ありがとうございました!
Comboboxの存在を確認--------------------------------------
Dim mycmb As MSForms.ComboBox
Dim myO As OLEObject, WS As Worksheet
Dim mycnt As Long
    For Each WS In Worksheets
        For Each myO In WS.OLEObjects
            If myO.progID = "Forms.ComboBox.1" Then
                mycnt = mycnt + 1
            End If
        Next
    Next
    MsgBox mycnt

comboboxをオブジェクト変数へ------------------------------
Dim com(10) As Object
    For i = 1 To mycnt
        Set com(i) = Worksheets("Sheet1").OLEObjects("ComboBox" & i).Object
    Next
---------------------------------------------------------
参考HP→http://www.ae.wakwak.com/~efc21/cgi-bin/wwwlng.cgi?print+200205/02050563.txt

Excel VBA Macro