スキルアップ・問題解決なら、モーグにおまかせ！

ActiveXコントロールのリストボックスを使って"選択肢を複数選択可能で、選択されたデータをセルに書き込むプルダウンリスト"をExcelに実装しようとしています。選択肢となる範囲は、Excelの機能である「名前の定義」でOFFSET関数で設定した可変の範囲を、リストボックスのプロパティの"Listfillrange"に設定しています。リストボックスには"ショップ"と名前を付けています。
 
図形挿入で作成したボタンを押すとプルダウンの表示/非表示を切り替えることができるようにしたいと思い、はじめに以下で試してみました。
 

Option Explicit

Sub リスト表示非表示()

    With ActiveSheet.Shapes("ショップ")
        If .Visible = True Then
            Call リスト非表示
        Else
            Call リスト表示
        End If
    End With

End Sub

Sub リスト表示()

    With ActiveSheet.ショップ
        .Visible = True
        .Activate
    End With

End Sub

Sub リスト非表示()

    With ActiveSheet.ショップ
        .Visible = False
    End With
    
End Sub

Sub セルの値表示()

    Dim セルの値 As String
    Dim I As Long
    
    セルの値 = ""
     
    With ActiveSheet.ショップ
        For I = 0 To .ListCount - 1                         ' リストの先頭から最後までループ
            If .Selected(I) = True Then                     ' リストが選択されていたら
                セルの値 = セルの値 & "," & .List(I)        ' カンマ区切りでリストの値をつなげる
            End If
        Next I
    End With
    セルの値 = Mid(セルの値, 2)
    Range("B2").Value = セルの値

End Sub

Option Explicit

Dim 初期化済 As Boolean

Sub リスト表示非表示()
    With ActiveSheet.Shapes("ショップ")
        If .Visible = True Then
            Call リスト非表示
        Else
            Call リスト表示
        End If
    End With
End Sub

Sub リスト表示()
    With ActiveSheet.Shapes("ショップ")
        .Visible = True
        ' 初めてリストを表示する場合にのみ初期化を行う
        If Not 初期化済 Then
            初期化リストボックス
            初期化済 = True
        End If
    End With
End Sub

Sub リスト非表示()
    With ActiveSheet.Shapes("ショップ")
        .Visible = False
        ' リスト非表示時にリストボックスの選択をクリアする
        With .OLEFormat.Object.Object
            .MultiSelect = fmMultiSelectMulti
            .Selected(-1) = False
        End With
    End With
End Sub

Sub セルの値表示()
    Dim セルの値 As String
    Dim i As Long
    セルの値 = ""
    With ActiveSheet.Shapes("ショップ").OLEFormat.Object.Object
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                セルの値 = セルの値 & "," & .List(i)
            End If
        Next i
    End With
    セルの値 = Mid(セルの値, 2)
    Range("B2").Value = セルの値
End Sub

Sub 初期化リストボックス()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lb As MSForms.listBox
    Dim i As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet2") ' 
    Set rng = ws.Range("SHOP") ' 
    Set lb = ActiveSheet.Shapes("ショップ").OLEFormat.Object.Object
    
    ' リストボックスの内容をクリア
    lb.Clear
    
    ' リストボックスに新しい範囲の値をセット
    For i = 1 To rng.Rows.Count
        lb.AddItem rng.Cells(i, 1).Value
    Next i
End Sub

プルダウンが表示されていないときにマクロ登録で"リスト表示非表示"を登録したボタンを押すと
 
lb.Clear
 
ここで「予期せぬエラー」が発生しています。
 
プルダウンが表示されていないときにボタンを押すと
 
.Selected(-1) = False
 
ここで「selectプロパティを設定できません。プロパティの値が無効です」となります

引用:
選択肢となる範囲は、Excelの機能である「名前の定義」で
OFFSET関数で設定した可変の範囲を、リストのプロパティの
"Listfillrange"に設定しています。

引用:
現在発生しているエラーは
lb.Clear
ここで「予期せぬエラー」が発生しています。

nari99 さんの引用:
プルダウンが表示されていないときにマクロ登録で"リスト表示非表示"を登録したボタンを押すと
 
lb.Clear
 
ここで「予期せぬエラー」が発生しています。
 
プルダウンが表示されていないときにボタンを押すと
 
.Selected(-1) = False
 
ここで「selectプロパティを設定できません。プロパティの値が無効です」となります

skさん
 
ありがとうございます。
 
ご指摘の意図は
＞ListFillRange プロパティの設定を活かしたまま
＞そのコードを実行しているからではないかと。
選択肢データを都度ListFillRageから再取得できるようにすれば、リストボックスのクリアは必要ない、という認識で間違いないでしょうか。
試しに"lb.Clear"を削除したところまた別のエラーが出ましたが、上記の認識でしたら消した後出るエラーは別の原因かと思うので。

引用:
選択肢データを都度ListFillRageから再取得できるようにすれば、
リストボックスのクリアは必要ない、という認識で間違いないでしょうか。

引用:
選択肢となる範囲は、Excelの機能である「名前の定義」でOFFSET関数で設定した可変の範囲

引用:
Set rng = ws.Range("SHOP")

引用:
・選択肢の範囲が増えたときに増えた分の選択肢をプルダウンに反映できない
・選択肢範囲が減った時にプルダウンに減った分の空白の選択肢が残ってしまう
という問題がありました。

引用:

ListFillRange プロパティに任意のセル範囲のアドレス/名前が
設定されているリストボックスについては、参照された範囲から
取得したリストを直接編集できないため、Clear, AddItem, RemoveItem など
リストのアイテムの追加/削除に関わるメソッドを呼び出すことは出来ません。

引用:
あるセルに何らかの値を入力すると OFFSET 関数によって
返される範囲の行数が増減するような数式が名前定義[SHOP]の
参照範囲として設定されているのであれば

=OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A$4:$A))

引用:
COUNTA(Sheet2!$A$4:$A)

引用:
=OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A$4:$A))

引用:
あるセルに何らかの値を入力すると OFFSET 関数によって
返される範囲の行数が増減するような数式が名前定義[SHOP]の
参照範囲として設定されているのであれば、そのセルの値が
変更されたタイミングでリストボックスの ListFillRange プロパティを
再設定する

質問させて頂いていることに加え、別の懸念点までご指摘いただきありがとうございます。

引用:
こういう参照の仕方は Excel では出来ないでしょう、
という点は置いておくとして。

引用:
しかし、もし A5 セルのみが空白セルだった場合、A4 セル以降の
行を参照範囲とする COUNTA 関数の戻り値は 9 となります。
その結果、OFFSET 関数が返す範囲は A4:A12 となり、
空白である A5 セルを含み、かつ空白セルではない A13 セルが
含まれていない状態となります。

=FILTER(IF(A4:A2000="","",A4:A2000),A4:A2000<>"")

=OFFSET(Sheet2!$B4,0,0,COUNTA(Sheet2!$B4:$B2000))

引用:
=FILTER(IF(A4:A2000="","",A4:A2000),A4:A2000<>"")

引用:
=OFFSET(Sheet2!$B4,0,0,COUNTA(Sheet2!$B4:$B2000))

引用:
Excel 2016 では FILTER 関数を
使用することはできません。

引用:
シートの再計算が発生するたびに
OFFSET 関数と COUNTA 関数が呼び出され、それに伴ってリストボックスの
リストの再読み込み処理が実行され、その時点で複数選択されていたアイテムの
選択状態も全てリセットされることになります。