値を渡したい!

ユーザーフォーム

値を渡して、プログラムを実行したいことありませんか?
今回は、2種類の方法で値を渡します
ドロップダウンリストを使う方法とユーザーフォームを使う方法です
ドロップダウンリストを使う方法はとっても簡単です
ユーザーフォームにも挑戦してみましょう!
グレードがぐっと上がるかも…?
難しいことはしていません ぜひ、やってみて!

リストで選択

店名を選んでプログラムに渡します

F列に検索したい店名を書き出します
C3セルに店名が表示されるようにしましょう!


データ→データの入力規則で入力値の種類リストにします
元の値はF3セルからF7セルとします

C3セルで店名を選択できるようになりました

選択した値をプログラムに渡してみよう!

標準モジュールTenmei_Selectプロシージャを作成します
C3セルの値をTenmeiに代入します
MsgBoxで表示してみましょう!

Tenmei_Selectプロシージャに値が渡りましたね!
MsgBoxをプログラムに変えていきましょう~

こちらを使います!
売上明細データから店名別に売上表を作成しています
これも見てね! ブログもあります
プログラムソースは、ブログにあります

このプログラムを使って、渡ってきた店名から売上表を作成してみましょう!

こちらのuriage_keisan6プロシージャをTenmeiを引数として呼び出します
渡した引数を実引数と呼びます

uriage_keisan6□の箇所を…

このように変更します
渡されたTenmeiを使います

件数も最終行を取得できるように変更しましょう

渡される側の引数は、仮引数と言います
Tenmeiでなく、他の変数名を指定してもいいですよ!
そのように使う方が多いです

 'Tenmeiを渡して売上表作成 Tenmeiは実引数 
    Call Uriage_Keisan(Tenmei)

’TenmeiをMisemeiとして受け取る Misemeiは仮引数
Sub Uriage_Keisan(Misemei As String)

実行ボタンを作りTenmei_Select紐づけます
実行してみましょう
店名別に売上表が作成できましたか?

Sub Tenmei_Select()

    Dim Tenmei As String
    Tenmei = Range("c3")
    
    'Tenmeiを渡して売上表作成
    Call uriage_keisan6(Tenmei)

End Sub
'Uriage_Keisan5をWithステートメントを使って
Sub uriage_keisan6(Tenmei As String)

Dim i As Long   '売上データの行数
Dim j As Long   '作成する売上表の行数
Dim k As Long   '売上の項目の位置(列)

With Worksheets("売上明細")
    j = 5
    For i = 3 To .Cells(Rows.Count, 3).End(xlUp).Row
        
        If .Cells(i, 3) = Tenmei Then       '店名がTenmeiかどうか
              
            '売上表に転記
            For k = 4 To 7
                Worksheets(Tenmei).Cells(j, k - 2) = .Cells(i, k)    '商品名~金額
            Next k
            j = j + 1 '次に作成する売上表の行数の計算
            
        End If
    Next i
End With

End Sub

オートフィルターも使ってみよう!

ちょと寄り道~

IF文で判断したくないな~
オートフィルターを使ってみましょう!

AutoFilter

オートフィルターする範囲対象列番号抽出条件で絞り込みます

オートフィルターする範囲は、範囲すべてでなくとも対応する表の
どこかを指定すれば良いようです

ABCストアを選択して、2列目の店名をTenmeiで抽出します

オートフィルターを掛けた表の行数は28でした

この範囲をコピーして…

TenmeiシートのB5セルに値のみ貼り付け!

オートフィルターを解除して終わります

でも、IF文で判断するよりオートフィルターを使う方が時間がかかる!?
データ件数にもよるだろうけど…

Sub Tenmei_Select()

  Dim Tenmei As String
  Tenmei = Range("c3")

  Call Tenmei_Filter(Tenmei)

End Sub
Sub Tenmei_Filter(Tenmei As String)
  Dim Gyo As Long

  With Worksheets("売上明細")

      '売上明細データの2列目の店名をTenmeiで抽出する   
      .Range("B2").AutoFilter Field:=2, Criteria1:=Tenmei
    
      'フィルターをかけたデータの行数
      Gyo = .Cells(Rows.Count, 3).End(xlUp).Row
    
      '店名と見出しはいらない d3セルからg列の最終行までコピー
      .Range(.Range("d3"), .Cells(Gyo, 7)).Copy
    
      '店名シートのb5セルに値のみ貼付け
      Worksheets(Tenmei).Range("b5").PasteSpecial Paste:=xlPasteValues
    
      'オートフィルターの解除
      .Range("B2").AutoFilter
    
  End With

  '店名シートをアクティブにして終わりますね!
  Worksheets(Tenmei).Activate

End Sub

ユーザーフォームから選択

ユーザーフォーム作成

ユーザーフォームを作成します
次のようなオブジェクト名にしました

ユーザーフォームの作り方はこちらもどうぞ❕

標準モジュールプログラム

標準モジュール

Tenmei_Selectをこのように書き直します
作成したフォームを呼び出すだけです

ユーザーフォームプログラム

ユーザーフォーム

初期設定

UserForm_Initializeで、
ユーザーフォームが表示される直前に行いたい処理を書きます

このように表示されるので…

コンボボックスの設定をします
店名検索シートの値をコンボボックスのTenmeiBox.Listに代入します

標準モジュールのTenmei_Selectを実行してみましょう
コンボボックスに指定した店名が代入されましたか?

TenmeiBox.List = Worksheets(“店名検索”).Range(“F3:F7”).Value

店名の値をRange(“F3:F7”)と決め打ちにしていますが、
最終行の取得を使って、このようにすれば、店名の数が増減しても対応できます

Gyo = Worksheets(“店名検索”).Cells(Rows.Count, 6).End(xlUp).Row
TenmeiBox.List = Worksheets(“店名検索”).Range(“F3:F” & Gyo).Value
あるいは、
TenmeiBox.List = Worksheets(“店名検索”).Range(Cells(3, 6), Cells(Gyo, 6)).Value

コマンドボタンの設定

OKbuttonをダブルクリックします

このように表示されるので…

先ほど作成したuriage_keisan6あるいは、Tenmei_Filterを呼び出します
引数はコンボボックスのTenmeiBox.Textです
選択した店名がここに入っているので、この値を渡します

処理が終わって戻ってきたら、Unloadでフォームを閉じます

コマンドボタンを作らずに、コンボボックスに変更があったら、
プログラムを実行する方法もあります

こちらを使うと店名を選択したらすぐ実行されます
?何選んだ??となるので、コマンドボタンで確定する方がいいのではないかと思います
せっかちさんはこちらもどうぞ!
(こちらにする場合は、OKbutton_Clickはなしです)
上の呼び出すプログラムはどちらかね!(^^;)

フローチャート

ユーザーフォームを使った場合のフローチャートです
こんな感じかな?

プログラムソース

標準モジュール

Sub Tenmei_Select()

    '店名選択フォームの表示
    SentakuForm.Show

End Sub

'Uriage_Keisan5をWithステートメントを使って
Sub uriage_keisan6(Tenmei As String)

  Dim i As Long   '売上データの行数
  Dim j As Long   '作成する売上表の行数
  Dim k As Long   '売上の項目の位置(列)

  With Worksheets("売上明細")
      j = 5
      For i = 3 To .Cells(Rows.Count, 3).End(xlUp).Row
               
          If .Cells(i, 3) = Tenmei Then       '店名がTenmeiかどうか
              
              '売上表に転記
              For k = 4 To 7
                  Worksheets(Tenmei).Cells(j, k - 2) = .Cells(i, k)    '商品名~金額
              Next k
              j = j + 1 '次に作成する売上表の行数の計算
            
          End If
      Next i
  End With

End Sub

Sub Tenmei_Filter(Tenmei As String)

  Dim Gyo As Long

  With Worksheets("売上明細")

      '売上明細データから2列目の店名をTenmeiをキーにしてフィルターをかける    
      .Range("B2").AutoFilter Field:=2, Criteria1:=Tenmei
    
      'フィルターをかけたデータの行数
      Gyo = .Cells(Rows.Count, 3).End(xlUp).Row
    
      '店名と見出しはいらない d3セルからg列の最終行までコピー
      .Range(.Range("d3"), .Cells(Gyo, 7)).Copy
    
      '店名シートのb5セルに値のみ貼付け
      Worksheets(Tenmei).Range("b5").PasteSpecial Paste:=xlPasteValues
    
      'オートフィルターの解除
      .Range("B2").AutoFilter
    
  End With

  '店名シートをアクティブにして終わりますね!
  Worksheets(Tenmei).Activate

End Sub

ユーザーフォーム

Option Explicit
'ユーザーフォームが表示される前に実行される処理
Private Sub UserForm_Initialize()

    'コンボボックスのListに店名を代入
    TenmeiBox.List = Worksheets("店名検索").Range("f3:f7").Value
    
End Sub

'コマンドボタン(OK)が押された時の処理
Private Sub OKbutton_Click()

    'どちらかを呼び出す
    'Call uriage_keisan6(SentakuForm.TenmeiBox.Text)
    Call Tenmei_Filter(SentakuForm.TenmeiBox.Text)
    
    'フォームを閉じる
    Unload Me
    
End Sub

'コンボボックスだけの処理ならこちらでも…
'Private Sub TenmeiBox_Change()

'  'どちらかを呼び出す
'    Call uriage_keisan6(SentakuForm.TenmeiBox.Text)
'    'Call Tenmei_Filter(SentakuForm.TenmeiBox.Text)

'    'フォームを閉じる
'    Unload Me

'End Sub

いかがでしたか?
そんなにむずかしくなかったでしょう❓

コメント