エクセルVBAでフォームのListboxをスクロールするには？

質問

エクセルVBAでフォームのListboxをスクロールするには？
ワークシート上に貼り付けたリストボックスがあります。
このリストボックスはOLEObjectではなくフォームのリストボックスです。

For Each lb In .ListBoxes
If lb.ListCount <= 4 Then
lb.ListIndex = 1
Else
lb.ListIndex = 16
ここで16番目が見えるようにスクロールしたいのです。
End If
Next lb

このスクロールさせる方法がわかりません。
ご教示いただければ幸いです。

回答No.3

end-u · ベストアンサー

>手動スクロールの動きが変ですし
失礼。
'-----------------------------------------------------------------
Sub sbChange()
　　'ListBoxes(1)の表示行数でMXを設定
　　Const MX As Long = 11
　　Dim x As Long
　　With ActiveSheet
　　　　x = n - .ScrollBars(1).Value + 1
　　　　If x < 1 Or x > MX Then
　　　　　　.ListBoxes(1).ListIndex = 0
　　　　Else
　　　　　　.ListBoxes(1).ListIndex = x
　　　　End If
　　End With
End Sub
'-----------------------------------------------------------------
Sub test()
　　Dim x
　　x = InputBox("ListIndex")
　　With ActiveSheet
　　　　.ListBoxes(1).ListIndex = 1
　　　　.ScrollBars(1).Value = x
　　　　n = x
　　End With
End Sub
'-----------------------------------------------------------------
Sub LbChange()
　　With ActiveSheet
　　　　n = .ListBoxes(1).ListIndex + .ScrollBars(1).Value - 1
　　End With
End Sub
'-----------------------------------------------------------------
上記に差し替え＆追加してsbChangeのMXを設定し、
LbChangeをListBoxes(1)にマクロ登録すれば少しはマシになります。
ですが、手が込み過ぎておすすめしないのは変わりありません。

また、
>モードレスなUserFormにListBoxを配置して使う方が良いと思います。
ここで書いたListBoxはActiveXコントロール(コントロールツールボックス)のListBoxの事です。
#もちろんExcel.ListBoxはUserFormに置けませんから
#お判りでしょうけど念のため。
#ActiveXコントロールは直接ワークシート上に配置するよりも
#UserFormに配置して使ったほうが不具合少なく、良いかと思ったもので。

お礼

end-uさま、おはようございます、merlionXXです。
いつもありがとうございます。

何かものすごい仕掛けですね。
まだよく理解できていませんが、実装するにはちょっと無理があるようです。

(￣～￣;)う～ん、やはりフォームのリストボックスではマクロでスクロールはできないのが仕様なんですね、しようがない。

ワークシート上に10数個のリストボックスを配置し、デフォルトボタンを用意して初期化したら選択肢が４つ以下のリストボックスは1番目、選択肢が47(都道府県)あるものは16番目の東京を表示させようと思ったのです。
ワークシート上にリストボックスを残しておきたいのでユーザーフォームは今回使用できません。
あまりお勧めでないという、シートにOLEオブジェクト（ActiveX）のリストボックスをはるか、デフォルトボタンを押した場合、何も選択されていない状態にしてしまうなど、他の方法を考えてみます。
ありがとうございました。

回答No.4

end-u

>選択肢が47(都道府県)あるものは16番目の東京を表示させようと思ったのです。

そういう用途であれば、DropDownsや[入力規則]リストが使われる事が多いのではないでしょうか。
DropDownsのリストをコードでDropDownする事はできませんが、
DropDownLinesを47、Value = 16 にしてDropDownsクリックしてみてください。
DropDownsのWindows相対位置にもよりますが、全リストが展開されます。
普段は折り畳まれて省スペースですし、ユーザー操作的にもそれほど差異は無いように思えます。

#仕様にダメ出しするつもりはありません。
#他の要件もある事でしょうし、あくまで参考意見です。

お礼

何度もおせわになりありがとうございました。
とても助かりました。

回答No.2

end-u

シート上のExcel.ListBoxでは ListIndex、Value、Selected(x) = True、
いずれもスクロールしません。仕様です。
諦めてください。

なんとかそれっぽく、
Option Explicit
Dim n As Long
'---------------------------------------------------------------------
'#Sub 準備()でシート追加しListBoxを配置。
'#Sub test()を実行してみてください。
Sub 準備()
　　With Sheets.Add
　　　　.Names.Add "List", "=OFFSET($A$1,$B$1-1,,20)"
　　　　With .Range("A1")
　　　　　　.Value = "F1"
　　　　　　.AutoFill .Resize(40), xlFillDefault
　　　　End With
　　　　With .ListBoxes.Add(.Range("C1").Left, 0, 100, 100)
　　　　　　.ListFillRange = "List"
　　　　　　.LinkedCell = "$B$2"
　　　　End With
　　　　With .ScrollBars.Add(.Range("C1").Left + 87, 0, 13, 102)
　　　　　　.Min = 1
　　　　　　.Max = 20
　　　　　　.SmallChange = 1
　　　　　　.LargeChange = 10
　　　　　　.LinkedCell = "$B$1"
　　　　　　.OnAction = "sbChange"
　　　　　　.ShapeRange.ZOrder msoBringToFront
　　　　End With
　　End With
End Sub
'---------------------------------------------------------------------
Sub sbChange()
　　Dim x As Long
　　With ActiveSheet
　　　　With .ScrollBars(Application.Caller)
　　　　　　x = .Value - n
　　　　　　n = .Value
　　　　End With
　　　　With .ListBoxes(1)
　　　　　　x = .ListIndex - x
　　　　　　If x < 0 Then
　　　　　　　　x = 0
　　　　　　ElseIf x > 20 Then
　　　　　　　　x = 20
　　　　　　End If
　　　　　　.ListIndex = x
　　　　End With
　　End With
End Sub
'---------------------------------------------------------------------
Sub test()
　　Dim x
　　x = InputBox("ListIndex")
　　With ActiveSheet
　　　　.ListBoxes(1).ListIndex = 1
　　　　.ScrollBars(1).Value = x
　　End With
End Sub
'---------------------------------------------------------------------

...こんな感じで実現してみましたが
トリッキーだし、手動スクロールの動きが変ですし、
実用的ではないです orz

複数のListBoxに設定するのも大変そうなので
モードレスなUserFormにListBoxを配置して使う方が良いと思います。

回答No.1

hana-hana3

＞lb.ListIndex = 16

ではなくて、lb.Value = "16番目のアイテム名" ではいかがですか？

お礼

hana-hana3さま、ありがとうございます。
説明が足りなかったのでもう少し具体的に書きます。
ワークシート上に複数のListboxがあります。
質問でも書いたようにこれはフォームのListboxで、OLEオブジェクトではありません。
Ｌｉｓｔboxのうち設定されているリストの数が４以下のものは１番目を選択させます。
これらは最初からリストが全部表示されているのでスクロールの必要はありません。
リストの数が５以上のものは１６番目を選択させます。
これらはリストが全部表示されていないので１６番目を見えるようにスクロールの必要があります。

テスト用にリストの数が５以上のものはA～Tまでリストを設定しました。
したがって１６番目は"P"になります。

ご教示の通り、
Sub test01()
　　Dim lb As ListBox
　　With ActiveSheet
　　　　For Each lb In .ListBoxes
　　　　　　If lb.ListCount <= 4 Then 'Listが４つ以下のものなら
　　　　　　　　lb.ListIndex = 1 'Listの一番目を選択
　　　　　　Else 'そうでなければ
　　　　　　　　lb.Value = "P" '16番目のPを表示
　　　　　　End If
　　　　Next lb '繰り返し
　　End With
End Sub
とやってみましたが、lb.Value = "P" のところで実行時エラー13「型が一致しません」となります。

lb.Value = 16
または
lb.ListIndex = 16 とすれば、16番目の"P"が選択されますが、スクロールはされません。

どのように直せばよいのでしょうか？

補足

いま、ためしにフォームではなくOLEObjectのListboxをワークシート上に一つ配置して以下のコードでやってみました。

Sub test03()
　　ActiveSheet.ListBox1.Value = "P"
End Sub

で、スクロールされて16番目の　P　が表示されました。
ただ、わたしがやりたいのはOLEObjectのListboxではなくフォームのListboxでのスクロールなのです。

よろしくお願いします。