[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロ:ListBox値とセル値が一致したら選択状態にする(複数選択)』（マイン）
いつもお世話になっております。
配列に取り込んだセル値を
ListBox内を検索し
該当すればListBox値を選択状態にする
といった処理を作成しております。
(仕様)
・配列に取り込むセル値
→単一セルに「名前 名前 名前・・・」と半角空白区切りで列挙された値
→ v = Split(key, " ")で空白区切り単位で分割して1次元配列化
・ListBoxは3つあります(.MultiSelect = fmMultiSelectMulti)
(処理ながれ)
・セル値の値を配列に取り込む
・ListBox名をFor Each List In Array(1, 2, 3)で3つのListBoxに対して
・配列値とListBox値が一致したら、その値を選択状態にする
といった処理となります。
以下がそのコードです。
(質問)
'該当すれば If .List(i) = v(x) Then
'ListBoxの値を選択状態にする・・・・・・・※選択されない
.ListIndex = i
Exit Sub'次に移る
End If
(1)配列とリスト内の値が一致しているところは認識できているようなのですが
※該当する値を選択してくれません
(2)ListBox値の選択状態を保持しながら、3つのListBoxに対して処理する方法
※(1)で躓いているため選択されない状態でループ完了します。
上記についてアドバイスのほどよろしくお願いいたします。
Private Sub CB_セル値選択_Click()
Dim sh1 As Worksheet: Set sh1 = ActiveSheet '当日:現在のシート
Dim key As String: key = sh1.Cells(61, 3)
If Not key = "" Then'セルに値があれば以下の処理
'▼指定セル内の値を格納(文字列分割)
'※指定セル内:1つのセルに「名前 名前 名前・・・」と空白区切りで入力されている
Dim v As Variant: v = Split(key, " ")
'▼各ListBoxから該当する氏名を検索しながら選択状態にしていく
Dim List As Variant, List_na As String, TOU_na As String
For Each List In Array(1, 2, 3)
'▼処理するListBox名の特定
Select Case List
Case 1: TOU_na = "[A]"
Case 2: TOU_na = "[B]"
Case 3: TOU_na = "[C]"
End Select
List_na = "ListBox_To" & CStr(List) '名前の定義
'▼セル値とListBox値が一致すればListBox選択状態にする
'※複数選択可: 該当分を選択保持していく
Dim i As Integer, n As Long, x As Long
With Me.Controls(List_na)
'配列に読み込んだ値を順番に
For x = LBound(v) To UBound(v)
'ListBox内を検索し
For i = 0 To .ListCount - 1
'該当すれば
If .List(i) = v(x) Then
'ListBoxの値を選択状態にする・・・・・・・※選択されない
.ListIndex = i
Exit Sub'次に移る
End If
Next i
Next x
End With
Next List
End If
End Sub
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
ListBoxプロパティのSelectedを使用してください。 また、Selectedを使用しても、上手く表示されない場合は、 ListIndexを-1を試してみてください。 （tkit） 2020/01/08(水) 09:07
以下の通り試してみました
'ListBoxの値を選択状態にする・・・・・・・※選択されない
.ListIndex = i
↓
.Selected
.ListIndex = -1
両方とも試しましたが反応変わりませんでした。
ローカルウィンドウでは、変数読込やエラーなく動作しているようなのですが・・・
（マイン） 2020/01/08(水) 12:04
まず、以下を確認ください。 https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/selected-property
.ListIndex = i
を、 .ListIndex = -1 .Selected(i)=True
に変更してみてください。
また、選択箇所のIndexを取得する際は、Selected(n)[nはListIndexです]=Trueを 検索します。
（tkit） 2020/01/08(水) 13:01
とりあえず、実験として適当なブックを用意して
(1) 1番目のシートのA1セルに「子,猫,卯」と入力
(2) ユーザーフォームを用意（挿入）して、「実験用1」という名前のリストボックスとコマンドボタンを用意
(リストボックスは MultiSelect = fmMultiSelectMulti にしておく)
(3) ユーザーフォームのモジュールに↓を記述
'---------------------------------------------------------------------------------
Private Sub UserForm_Initialize()
実験用1.List = Split("子・丑・寅・卯・辰・巳", "・")
End Sub
'---------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim tmp As Variant
Dim i As Long
Stop 'ブレークポイントの代わり
For Each tmp In Split(Worksheets(1).Range("A1").Value, ",")
With Me.Controls("実験用1")
For i = 0 To .ListCount - 1
If .List(i) = tmp Then .Selected(i) = True
Next
End With
Next tmp
MsgBox "終わり"
End Sub
'--------------------------------------------------------------------------------
(4) ユーザーフォームを実行して、コマンドボタンを押下
(5) Stop で止まるので、ステップ実行して実験用1がどうなるか研究
これが済んで（理解出来て）から、「Controls(●●)」や、「sh1.Range("◆◆").Value」をどうやって入れ替えればよいか考えたほうが分かりやすそうに思えます。
（もこな２） 2020/01/08(水) 18:23
0）ユーザーフォーム上の話ですか？
1）選択できたとして、その後何をするのですか？
2）リストボックスを使わないでシート上で表現するのはだめですか？
3）なぜリストボックスが3つに分かれているのですか？
（まっつわん） 2020/01/08(水) 19:00
問1) UserForm上に配置したListBoxです。
問2と問3) 日報に勤務者入力後の修正用に使用します(以下説明)
(通常処理)
・部署「A・B・C」毎にListBoxへ当該職員一覧を読み込み
・勤務者をそれぞれListBoxから選びます
・コマンドボタンで単一セルに転記します
セル: [部署A] aさん bさん cさん [部署B] pさん kさん uさん [部署C] dさん wさん xさん
という感じで列挙されます(仕様です・・・)
(修正処理)←今回の質問部分
職員転記後に「あとから職員入力ミスの修正」時にListBoxを使用して転記したいです。
そこで考えたのが
・セル値を配列に取込み
↓
・部署毎のListBoxから該当する職員名を選択状態にする
(最初から選択し直すのが大変なので・・・知恵を絞って考えてみました)
↓
・ListBoxから間違った職員の選択を外して、正しい職員を選び
↓
・元のセルに再転記する
といった流れです。
問2) 日報自体手入力の部分もありますが、ほぼformからの入力で行うように仕様変更しました。
(それまでは、セル右クリックイベントにリスト表示させて入力などでした)
なんとか出来そうな気はするのですが頓挫してしまいました。
（マイン） 2020/01/08(水) 20:20
'ListBoxの値を選択状態にする・・・・・・・※選択されない
.ListIndex = i
Exit Sub '次に移る←Exit For で抜けるでした （マイン） 2020/01/08(水) 20:28
報告です。完璧でした。
Selectedプロパティなどのコントロールにまつわる部分が理解不足でした。
'該当すれば
If .List(i) = v(x) Then
'ListBoxの値を選択状態にする
.ListIndex = -1
.Selected(i) = True
Exit For
End If
もこな２ さん
アドバイスありがとうございます。
試してみます。
またご報告します。
（マイン） 2020/01/08(水) 20:32
＞問2) 日報自体手入力の部分もありますが、ほぼformからの入力で行うように仕様変更しました。
僕なら多分、シートをユーザーフォームのように使います。
見た目も派手に出来るし、
（ユーザーフォームのデザインは古いうえにマウススクロールが簡単には使えるようにならない）
コードは簡単になる気がしますが。。。（ならないかな。。。＾＾；）
別シートに表示し新しいウィンドウで見せれば、
いいかなと思います。（データベースは見せなくてもいいかもですが。。。）
（まっつわん） 2020/01/08(水) 20:37
ご報告します。かなりシェイプできました。
単一ListBoxで動作確認しながら現状に合せて調整したところ・・・・びっくりダイエット！！
類似したコードも見直したら
ざっと半分まで減りコード全体が1画面に収まり見やすい。分かりやすい。
アドバイス感謝します。
Private Sub CB_セル値選択_Click()
Dim key As String: key = ActiveSheet.Cells(61, 3)
If Not key = "" Then
Dim List As Variant
For Each List In Array("ListBox_To1", "ListBox_To2", "ListBox_To3")
'配列値とListBox値が一致すればListBox選択状態にする
Dim tmp As Variant, i As Integer
With Me.Controls(List)
For Each tmp In Split(key, " ") 'セル値区切り単位で処理
For i = 0 To .ListCount - 1
If .List(i) = tmp Then
.Selected(i) = True
Exit For
End If
Next i
Next tmp
End With
Next List
End If
End Sub
（マイン） 2020/01/08(水) 22:04
なるほどですね。
現在Sheetタブが日付分ズラリ並んでいます。
・これを「入力用Sheet」のみにして
データはデータベース化という方法・・・もっと早い段階で気づけば・・・
あたまから1日1Sheetと思い込んでいました
(今のSheet構成)
・マスタ
↓
・日報31日分
↓
・集計用No.1
・集計用No.2
と分かれているので、いっそうのこと、日報ワンペーパーにしてみようかと思います。
ただ、今のは1月からすでに導入してバグとりしながら運用しているので(無計画・・)
いいタイミングで、検討したいと思います。
アイディアありがとうございます。
（マイン） 2020/01/08(水) 22:23
＞ データはデータベース化という方法・・・もっと早い段階で気づけば・・・
エクセルでは1件のデータを1行で表せば、
いろいろな機能が利用できますので、
アプリの開発が楽になるかと思います。
＞これを「入力用Sheet」のみにして
現状どのように表示されているかわかりませんが、
やりたいことが、
＞「あとから職員入力ミスの修正」
ならば、
＞→単一セルに「名前 名前 名前・・・」と半角空白区切りで列挙された値
↑このような入力は無駄では？
日付を選んだら、
その日の日報が表示され、
その横にメンバーのリストがあって、
日報に記入されたメンバーが、
リスト上で、塗りつぶし等で強調される。
間違いがあれば、
Ctrlキーを押しながら、メンバーの名前を選択し、
登録ボタン押下で、訂正されるというような流れでやればよいかと。
それから、どのような形でデータを保持するにせよ、
溜める、見せるをまず分ける。
ということは、ユーザーにデータベースを触らせない。
（無意識の誤操作を防ぐため、既定の操作以外の操作をマクロで制限する）
ということに注意して開発を進めることをお勧めします。
そのときにユーザーフォームより、シートの方が
表現の自由度が高いかと思います。（制御が難しくなるかもしれませんが。）
現状のバグとりが落ち着いたら、
バージョンアップの検討をお勧めします。
（際限なく、バグ取り→バージョンアップのループが続くかもしれませんが
頑張ってください。）
簡易データベースを作られるならば、
http://home.att.ne.jp/zeta/gen/excel/c03p04.htm
↑ここが、参考になると思います。
（まっつわん） 2020/01/09(木) 00:23
Private Sub CB_セル値選択_Click()
Dim c As Long, i As Long
Dim 配列 As Variant, tmp As Variant
With ActiveSheet.Range("C61")
If .Value = "" Then
MsgBox "データが入力されていません"
Exit Sub
Else
配列 = Split(.Value, " ")
End If
End With
For c = 1 To 3
For Each tmp In 配列
For i = 0 To Me.Controls("ListBox_To" & c).ListCount - 1
If Me.Controls("ListBox_To" & c).List(i) = tmp Then
Me.Controls("ListBox_To" & c).Selected(i) = True
Exit For
End If
Next i
Next tmp
Next c
End Sub
（もこな２） 2020/01/09(木) 18:20
ありがとうございます。今確認しました。
アドバイスのコード試します。
（マイン） 2020/01/10(金) 15:23
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.