Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7 Home Premium : Excel 2010)
2つのリストボックス連携して科目・番号を取得したい
投稿日時: 18/06/14 12:00:25
投稿者: 消しゴム

サンプルコードをいじっては、動作を確認するレベルなのですが、よろしくお願いします。
 
ユーザーフォーム上に2つのリストボックス2つのテキストボックスを配置させ、
リストボックス1にユーザーに解り易く簡略番号を選択(1000、2000、…9000)をすると、その簡略番号に該当する科目と番号の2列をリストボックス2にを表示させ、該当する行をWクリックすればテキストボックス1に科目、テキストボックス2に番号をそれぞれ転記する方法はどのようにすればいいでしょうか?
 
リストボックス1には、
.AddItem "1000"
.AddItem "2000"
 
.AddItem "9000"
で追加していけばいいかと思うのですが、科目・番号は直接【科目番号表】sheetを参照したいのですが…。
 
よろしくお願いします。
 
↓【科目番号表】sheet
A列    B列    C列
簡略番号    科目    番号
1000    現金    1111
1000    当座預金    1112
1000    貸倒引当金    1259
2000    支払手形    2111
2000    短期借入金    2113
2000    長期借入金    2212
2000    退職金給与引当金    2214
4000    売上高    4111
4000    売上値引・戻り高    4115
5000    期首棚卸高    5111
5000    期首仕掛品棚卸高    5471
5000    期末仕掛品棚卸高    5472
6000    販売員給与    6111
6000    雑費    6231
7000    受取利息割引料    7111
7000    受取配当金    7114
7000    雑収入    7118
7000    支払利息割引料    7511
8000    価格変動準備金戻入額    8113
8000    法人税及び住民税    8311
9000    当期利益(損失)    9111
9000    前期繰越利益(損失)    9211
9000    利益準備金積立額    9511
9000    当期未処分利益(損失)    9611

回答
投稿日時: 18/06/15 20:17:48
投稿者: WinArrow
投稿者のウェブサイトに移動

サンプルコード
 
1つ目:ComboBox
2つ目:ListBox
と仮定して
 
TextBox1はなくても処理可能と思います。
 
ユーザーフォームモジュールに
Option Explicit
 
Const STNM As String = "科目番号表"
 
 
Private Sub ComboBox1_Change()
Dim Rx As Long
    If Me.ComboBox1.ListIndex < 0 Then Exit Sub
    With Sheets(STNM)
        Rx = WorksheetFunction.Match(Me.ComboBox1.Value, .Columns("A"), 0)
        Me.ListBox1.List = .Cells(Rx, "B").Resize(Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1), 2).Value
    End With
End Sub
 
Private Sub ComboBox1_Enter()
        With Sheets(STNM)
        .Range("Z1").Consolidate _
            Sources:=.Name & "!R1C1:R" & .Range("A" & .Rows.Count).End(xlUp).Row & "C2", _
            Function:=xlCount, _
            TopRow:=False, _
            LeftColumn:=True, _
            CreateLinks:=False
    End With
     
    Me.ComboBox1.List = Sheets(STNM).Range("Z1").CurrentRegion.Offset(1).Value
    Sheets(STNM).Range("Z1").CurrentRegion.ClearContents
     
End Sub
 
Private Sub UserForm_Initialize()
    Me.ListBox1.ColumnCount = 2
    Me.ListBox1.ColumnWidths = "100;20"
 
End Sub
 

回答
投稿日時: 18/06/15 20:38:15
投稿者: WinArrow
投稿者のウェブサイトに移動

テキストボックスが存在しなくても処理可能な
 
 
コマンドボタンで利用すると仮定して
 
Private Sub CommandButton1_Click()
    With Me.ListBox1
        If .ListIndex < 0 Then Exit Sub
        MsgBox "科目は、" & .List(.ListIndex, 0) & " 番号は、" & .List(.ListIndex, 1)
    End With
End Sub
というような方法で参照できるので、敢えてテキストボックスに入れなくてもよいのでは?
と感じました。
 

引用:
.AddItem "1000"
 .AddItem "2000"
   
 .AddItem "9000"
で追加していけばいいかと

ここのところは、変更があった場合、コード修正になってしまうので
シートを変更のみで(コード修正せず)対応できた方がよいと思いました。

投稿日時: 18/06/16 19:23:35
投稿者: 消しゴム

WinArrowさん 回答ありがとうございます。
再現できずに…悶々としていました。
 実行時エラー1004:WorksheetFunctionクラスのMatchプロパティを取得できません
 
簡略番号がセルの書式で文字列に変更したもののエラー表示。
新たに打ち直して初めて実行に至りました。
 
> Private Sub ComboBox1_Enter()
'ボタン押下げた時点で1000番2000番…9000番に対応するデータ数をZ1に作業セルに書出し
 
> rivate Sub ComboBox1_Change()
 '1000番2000番…9000番を選択時にListBox1.List表示
 
上記の部分、なんとなく雰囲気がつかめた状態です。VBA触っているものの、初心者レベルであり
理解薄くて申し訳なく思います。
細部まで、こだわっていただいて頭がさがります。
 
WinArrowさん ありがとうございました。

回答
投稿日時: 18/06/16 20:32:34
投稿者: WinArrow
投稿者のウェブサイトに移動

> 実行時エラー1004:WorksheetFunctionクラスのMatchプロパティを取得できません
 
Matchメソッドの引数のデータ型を合わせないと、このエラーが出ます。
ComboBoxでも、ListBoxでも同じですが、文字列ですから、
セルに合わせるならば
WorksheetFunction.Match(CLng(Me.ComboBox1.Value), .Columns("A"), 0)
のように数値化する必要があります。
数値化するには
Me.ComboBox1.Value * 1
または
Val(Me.ComboBox1.Value)
という方法もあります。
 

回答
投稿日時: 18/06/16 20:44:16
投稿者: WinArrow
投稿者のウェブサイトに移動

>簡略番号がセルの書式で文字列に変更したもののエラー表示。
 
セルの書式設定ー表示形式を「文字列」に変更しただけでは、文字列いはなりません。
再入力という方法もありますが、
 
A列を選択(表示形式変更しなくても)し、
「データ」「区切り位置」−−「文字列」指定で、文字列になります。

回答
投稿日時: 18/06/17 14:38:19
投稿者: hatena
投稿者のウェブサイトに移動

よくありそうな要件ですね。
AccessだとRowSourceにSQLを設定するだけで簡単に実現できますね。
 
WinArrowさんのコードは勉強になります。
Consolidateというのも初めて知りました。
(Accessメインなので、Excelの関数はあまり知りません。)
 
自分なりに考えてみました。セル範囲に名前の定義を付けて、それをリストボックスのRowSourceに設定するという考え方でコーディングしてみました。
 
ユーザーフォーム上にListBox1, ListBox2 を配置します。
 
ユーザーフォームモジュールは、
 

Option Explicit

Private Sub ListBox1_AfterUpdate()
    Me.ListBox2.RowSource = "_" & Me.ListBox1.Value
End Sub

Private Sub UserForm_Initialize()
    Dim r As Range, i As Long, sNum As Long, cnt As Long
    
    With Worksheets("科目番号表")
        Set r = .Range(.Cells(2, 1), .Cells(1, 1).End(xlDown))
        i = 2
        Do Until .Cells(i, 1).Value = ""
            sNum = .Cells(i, 1).Value
            cnt = WorksheetFunction.CountIf(r, sNum)
            .Cells(i, 1).Offset(, 1).Resize(cnt, 2).Name = "_" & sNum
            Me.ListBox1.AddItem sNum
            i = i + cnt
        Loop
    End With
    
    Me.ListBox2.ColumnCount = 2
    Me.ListBox2.ColumnWidths = "100;20"
End Sub

回答
投稿日時: 18/06/17 14:52:05
投稿者: hatena
投稿者のウェブサイトに移動

ListBox2の各列の値にアクセスするには、
下記のような方法もあります。
 
プロパティを下記のように設定しておいて、
    Me.ListBox2.BoundColumn = 2
    Me.ListBox2.TextColumn = 1
 
ValueプロパティとTextプロパティで取得できます。
 

Private Sub CommandButton1_Click()
    With Me.ListBox2
        If .ListIndex < 0 Then Exit Sub
        MsgBox "科目は、" & .Text & " 番号は、" & .Value
    End With
End Sub

トピックに返信