Excel VBA ユーザーフォーム１のコンボボックスに別ブックの値を反映させたいです。

Question

ファイル名：受注票→ユーザーフォーム１にコンボボックス１があります。
ファイル名：請求、シート名：請求先→コンボボックスに反映したいデータ元（C2からその下）があります。
ブック名が別々な為、ファイル名：受注票のユーザーフォーム１にあるコンボボックス１に別ファイルの請求先データをもってくることができません。具体的にどう訂正すればいいのか教えて頂きたいです。よろしくお願いします。

***********************************************************
Private Sub ComboBox1_Change()　　　　←問題はこの部分です。
Dim wb As Workbook
Dim sh As Worksheet
Dim LastRow As Long
With Workbooks.Open("C:\Users\XXXXXX\Desktop¥"請求先") '←ブック名:請求先のフルパスを指定
wb = Workbooks("請求")
sh = Worksheets("請求先")
LastRow = sh("請求先").Cells(Rows.Count, "C").End(xlUp).Row
ComboBox1.RowSource = "請求先!C2:C" & LastRow
End Sub
*************************************************************

Private Sub CommandButton1_Click()
'入力必須項目が未入力なら終了(登録しない)
'If TextBox1.Text = "" Then
'MsgBox "受注管理番号を入力してください。", vbInformation, "確認"
'Exit Sub
'End If
Range("B4").Value = TextBox3.Value
Range("B5").Value = TextBox4.Value
Range("B6").Value = TextBox5.Value
Range("B7").Value = TextBox6.Value
Range("B8").Value = TextBox7.Value
Range("B9").Value = TextBox8.Value
Range("B10").Value = TextBox9.Value
Range("B11").Value = TextBox10.Value
Range("B12").Value = TextBox11.Value
Range("B13").Value = TextBox12.Value
Range("B14").Value = TextBox13.Value
Range("B15").Value = TextBox14.Value
Range("B16").Value = TextBox15.Value
Range("B17").Value = TextBox16.Value
Range("B18").Value = TextBox17.Value
Range("B19").Value = TextBox18.Value
Range("B20").Value = TextBox19.Value
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
'初期化処理
Worksheets("受注IMP").Activate
End Sub

Qchan1962 · Accepted Answer

＞①リストからのみ入力可能のところを、直接入力+リストからのみ選択を組み合わせてしまうとやはりまずいでしょうか？

使い方に依ると思いますので何とも言えないと思いますが
この場合、ComboBox1_Changeイベントでの書き込みはやめた方が良いかと思います（Changeイベントは1文字入力の度、発生するので）

方法は複数ありますが、AfterUpdateイベントなどでどうでしょう
Private Sub ComboBox1_AfterUpdate()
Worksheets("受注IMP").Range("B1").Value = ComboBox1.Text
End Sub
挙動については実験で試してください

＞直接入力+リストから（ComboBox1内では難しいです）
これは分けて行うのが良いと思います

②直接入力　OR　リストからならば可能と思いますが、
直接入力に対して入力制限や入力値の整合性などチェックが必要になると思います

②　.Style = fmStyleDropDownList　は　UserForm_Initializeで設定していますのでデフォルトにすれば良いです（つまり、当該1行削除して実行しない）

デザインモード（VBEのプロパティウィンドウ）で設定した場合は実行時
.Style = fmStyleDropDownCombo　で設定します

---①
ユーザーフォームの作成は、運用者が入力し易く出来れば直感的な操作で完結できるのが良いと思います
それには、入力の順序、入力値の制限や入力値の整合性チェックなども必要になると思います

コントロールオブジェクトが持つイベントも色々ありますので調べて検討するようにしてください
いずれに致しましてもやり方も色々あります
ComboBox1に値を登録するだけでもすでに示しているだけでも3パターン

回答を得ても自身で内容を確認、必要に応じて検索などをして理解していく事が、後に改修や問題が発生した時、困らない為にも重要になると思います

Qchan1962 · Answer

＞やはりデータ保存にはCSVのほうが重くないのでしょうか？
そうですね、他にも理由としてカラムを考えれば他の会計ソフト、クラウドソフト、運送ソフトなどとのやり取りでcsv形式のデータなら何とか対応しているケースが多いからですね　
もちろん、csv入出力では、特にExcelを介して行う場合、制限、仕様など注意は必須となります

＞教えて頂いたComboBox1で選んだデータをシートに転記するにはどのようにしたらよいですか？

ComboBox1で選んだデータを限定する方が良いと思います
選択データのみを扱うようにすれば、ComboBox1_Changeイベントで書き込んでも問題なさそうですね（シートイベントなどを使っていない場合）

Private Sub ComboBox1_Change()
Worksheets("受注IMP").Range("B1").Value = ComboBox1.Text
End Sub

ComboBox1の値を選んだ時に書き込む事については是非があると思いますが
実行ボタンを押下した時の処理を少なくして　体感的な処理時間短縮になると思います（実際には選んだ値でデータを読み込むとかそれなりの処理がある場合ですね）

＃３に書きました　Initialize　ComboBox1の設定を少し書き直して
RowSource =　を
配列としてリストに登録
 .List() = sh.Range("C2:C" & LastRow).Value
リストからのみ入力可能
.Style = fmStyleDropDownList

Private Sub UserForm_Initialize()
Dim wb As Workbook
Dim sh As Worksheet
Dim LastRow As Long
Const TargetBook As String = "請求先.xlsx"
Dim myDesktop As String
Dim wsh As Object
Set wsh = CreateObject("Wscript.Shell")
myDesktop = wsh.SpecialFolders("Desktop")
Set wsh = Nothing
'←ブック名:請求先のフルパスを指定
Set wb = Workbooks.Open(myDesktop & "\" & TargetBook)
Set sh = wb.Worksheets("請求先")
LastRow = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row
With ComboBox1
 .List() = sh.Range("C2:C" & LastRow).Value
.Style = fmStyleDropDownList
End With
wb.Close False
'初期化処理
Worksheets("受注IMP").Activate

End Sub

Qchan1962 · Answer

＃３の続きです（長文で申し訳ない）

＃３の様にしても、まだ問題があると思うのはUserFormをloadする時に
（他の作業などを行い繰り返しUserFormを使う場合や複数のUserFormを使う）Workbooks.Openを実行する事と"請求先.xlsx"が既に開いている時と開いていない時の処理を分ける必要がある事（RowSource設定の為）
などなどから

VBAブックを開く時にあらかじめ必要なデータを　
ブック内のデータ用のシート（複数でも良い）に抽出して活用する
外部データはcsvファイルなどにして入出力が比較的簡単に出来るようにする
と良いのでは無いかと思います

例　ブックを開く時にデータをシートに書き込む（csv）
Private Sub Workbook_Open()
Dim File_FllPath As String
Dim ws As Worksheet
    Dim wsh As Object
    Set wsh = CreateObject("Wscript.Shell")
    File_FllPath = wsh.SpecialFolders("Desktop") & "\請求先.csv"
    Set wsh = Nothing
    Set ws = Worksheets("請求先Data")　'ブック内に予めあるシート
Call in_csvQuery(File_FllPath, ws)
End Sub

'複数のcsvファイルデータを抽出するかもなのでメインを分けました

Private Sub in_csvQuery(File_FllPath As String, ws As Worksheet)
'csvデータを取り込むシート ws
Dim qt As QueryTable
    On Error Resume Next
    ws.Cells.Clear
    Set qt = ws.QueryTables.Add(Connection:="TEXT;" & File_FllPath, Destination:=ws.Range("A1"))
    With qt
        .TextFilePlatform = 932 'Shift-JIS 'UTF-8 65001
        .TextFileParseType = xlDelimited    ' 区切り文字の形式
        .TextFileCommaDelimiter = True    ' カンマ区切り
        .RefreshStyle = xlOverwriteCells    ' セルに上書き
        .Refresh
        .Delete
    End With
    Set qt = Nothing
End Sub

ブック内のデータシートを基に処理を行い　
結果を必要に応じてデータシートに書き込み、合わせて外部csvデータを更新する（追加・上書きなど出力）
ブックを閉じる時にブック内データはClearする（軽くする）

この様な処理手順にした方が組み立てやすいと思います
ただし、外部データにcsvファイルを使い、始めにデータを取り込むため
複数人での作業の場合に問題が生じる可能性があります

実際に請求などの基幹業務をExcelVBAで作るのは、消込や連携を考えるとハードルが高いです　さらに今後コンプライアンスの関係もあり専用ソフトなどを活用された方が無難と思います
余計な事まで書いてしまいましたが　参考程度で

Qchan1962 · Answer

こんばんは
＞Private Sub ComboBox1_Change()　　　　←問題はこの部分です。
コードについては省略記載されているのかも知れませんが、複数の問題があります
ご質問者様自身問題視しているChangeイベントはComboBox1の値が変更された時に発生します（項目選択または文字入力の度に発生）

他のイベントでRowSourceプロパティ を設定しているかAddItemメソッドで値を設定していない場合、初期値が無い為、ComboBox1に何だかの値を入れChangeイベントを発生させる必要があります
この様なプロセスは、通常合理的な処理にならないと思いますので
UserFormをloadする時に（UserForm_Initializeで）処理するのが良いと思います

Private Sub ComboBox1_Change()は使わず

まだ問題がありますが　一例
.xlsxと仮定（適時変更）　
Private Sub UserForm_Initialize()
Dim wb As Workbook
Dim sh As Worksheet
Dim LastRow As Long
Const TargetBook As String = "請求先.xlsx"
Dim myDesktop As String
    Dim wsh As Object
    Set wsh = CreateObject("Wscript.Shell")
    myDesktop = wsh.SpecialFolders("Desktop")
    Set wsh = Nothing
    Dim wid As Workbook
 For Each wid In Workbooks
 If wid.Name = TargetBook Then Set wb = wid
Next
If wb Is Nothing Then
 '←ブック名:請求先のフルパスを指定
Set wb = Workbooks.Open(myDesktop & "\" & TargetBook)
End If
Set sh = wb.Worksheets("請求先")
LastRow = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row
 Me.ComboBox1.RowSource = sh.Range("C2:C" & LastRow).Address(0, 0, External:=True)
'初期化処理
Worksheets("受注IMP").Activate
End Sub

対象ブックを閉じて使う場合は、少し面倒ですがRowSourceよりAddItemメソッドを使います
RowSourceの場合、データ取得対象ブックを閉じるとデータを維持できなかったかと
If wb Is Nothing Then　以下を下記の様にします（一例）

If wb Is Nothing Then
 '←ブック名:請求先のフルパスを指定
Set wb = Workbooks.Open(myDesktop & "\" & TargetBook)
End If
Set sh = wb.Worksheets("請求先")
LastRow = sh.Cells(sh.Rows.Count, "C").End(xlUp).Row
Dim c As Range
For Each c In sh.Range("C2:C" & LastRow)
With Me.ComboBox1
.AddItem c.Text
End With
Next
wb.Close False
'初期化処理
Worksheets("受注IMP").Activate

End Sub

AddItemメソッドを使う時は
既に開いている時　Workbooks.Openでインスタンスしない問題は回避できると思いますがそのままです

初心者ですが何か。 · Answer

＞With Workbooks.Open("C:\Users\XXXXXX\Desktop¥"請求先") '←ブック名:

・拡張子がない
・Closeをしてないのでイベント発生の度開かない？
・End With がない
・Set ステートメントを使用してない

Dim LastRow As Long
With Workbooks.Open("C:\Users\XXXXXX\Desktop¥"請求先.xlsx") '←ブック名:請求先のフルパスを指定
LastRow = .worksheets("請求先").Cells(Rows.Count, "C").End(xlUp).Row
ComboBox1.RowSource = "[" & .name & "]請求先!C2:C" & LastRow
.close
end with

とか？
拡張子は適宜修正で。

tatsumaru77 · Answer

ComboBox1.RowSource = "請求先!C2:C" & LastRow を
ComboBox1.RowSource = "[受注票.xlsx]請求先!C2:C" & LastRow 
にしてはどうでしょうか。
（ファイル：受注票の拡張子がxlsxでない場合は、実際の拡張子に合わせてください）

Excel VBA ユーザーフォーム１のコンボボックスに別ブックの値を反映させたいです。

＞①リストからのみ入力可能のところを、直接入力+リストからのみ選択を組み合わせてしまうとやはりまずいでしょうか？

＞やはりデータ保存にはCSVのほうが重くないのでしょうか？

＃３の続きです（長文で申し訳ない）

こんばんは

＞With Workbooks.Open("C:\Users\XXXXXX\Desktop¥"請求先") '←ブック名:

ComboBox1.RowSource = "請求先!C2:C" & LastRow を

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング