【Excel VBA】コンボボックスで選択した値の取得

質問

Excel2003を使用しています。

SheetAから、あるシート(都度選択)にデータを転記するマクロを作成しています。
当初は、下記のように InputBox で、転記先のシート名を入力して、そこからシート名を取得していましたが、確実にシート名を取得するために、ユーザーフォームのコンボボックスから選択するように、変更したいと思います。

　MySht = InputBox("転記するシート名を入力してください")
　　With Sheets("SheetA")
　　　.Range("H6:J9").Copy
　　　　Sheets(MySht).Range("M7")　～

これを、コンボボックスで選択した値を上記のコードでいう、MySht に設定(？)したいのですが、どのようにしたらいいでしょうか？
コンボボックスで選択した値をセルに転記する方法はわかるので、とりあえず、今は一旦セルに転記していますが、コード内で直接(…というと変かもしれませんが)設定できるのであれば、教えていただきたいのです。
自分だけが使用するものならば、InputBox で十分なのですが、選択するシートのシート名に数字が含まれているので、全角や半角の入力ミスを避けるために、コンボボックスで選択するようにしたらどうかと思ったのですが、何か他にも良い方法があれば、あわせて教えていただけると嬉しいです。

よろしくお願いします。

回答No.4

zap35 · ベストアンサー

なぜコンボボックスなのでしょう？　リストボックスにシート名を表示させれば「誤入力」は起きないはずなのですが？　
でもコンボボックスを用いるなら「誤入力の可能性」がありますから、エラーチェックが必要になりますよ。

とりあえずUserformにComboboxを１つ配置して、ユーザーフォームのモジュールシートに以下のマクロを貼り付けて試してみてください。

Const excpt As String = "SheetA"　'コピー元のシート名
Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
　　If sht.Name <> excpt Then
　　　　Me.ComboBox1.AddItem sht.Name
　　End If
Next sht
End Sub

Private Sub ComboBox1_Change()
　　If Len(ComboBox1.Text) = 0 Then
　　　　MsgBox "シートを選択するか入力してください"
　　　　ComboBox1.SetFocus
　　Else
　　　　On Error GoTo err1
　　　　Worksheets(excpt).Range("H6:J9").Copy _
　　　　　　Destination:=Worksheets(ComboBox1.Text).Range("M1")
　　　　Unload Me
　　　　Exit Sub
　　End If
err1:
　　MsgBox "該当するシートがないので再入力してください"
　　ComboBox1.SetFocus
End Sub

お礼

お礼が遅くなり、申し訳ありません。

>コンボボックスを用いるなら「誤入力の可能性」がありますから

確かにそうですね。
以前、別件で作成したマクロで、コンボボックス使用し、リストからの選択と手入力と両方できて便利だったという記憶があって、今回もコンボボックスで…と思ってしまいました。
誤入力がおきないよう、リストボックスで検討しようと思います。

今回、教えていただいたマクロも大変参考になりました。
ありがとうございました。

回答No.3

mshr1962

写す方法はいくつかあります。
方法１．コンボボックスチェンジイベントで値を設定する。
※コンボボックスの値が変わると動作するマクロ
Sub combobox_change()
Mysht = combobox.Value
以下継続

方法２．コンボボックスのプロパティのLinkedCellで、特定のセルに値をリンクさせておく
Sub macro()
MySht = WorkSheets("シート名").RANGE("セル座標").Value
以下継続

お礼

お礼が遅くなり、申し訳ありません。
具体的な方法を示していただき、助かりました。
いろんな方法があるのですね。勉強になります。
ありがとうございました。

回答No.2

imogasi

コンボボックスを
（１）ユーザーフォームに貼り付ける場合
（２）ワークシートへ貼り付ける場合
どちらをしたのか、質問には明記のこと。
ーー
Sheet1に（２）として
Private Sub ComboBox1_Click()
MsgBox Worksheets("Sheet1").ComboBox1.List(Worksheets("Sheet1").ComboBox1.ListIndex)
End Sub
をSheet１のイベントのコンボボックスのクリックイベントに入れれば
実験になるでしょう
実際はｘ＝Worksheets("Sheet1").ComboBox1.List(Worksheets("Sheet1").ComboBox1.ListIndex)
として
最終は
Private Sub ComboBox1_Click()
'MsgBox Worksheets("Sheet1").ComboBox1.List(Worksheets("Sheet1").ComboBox1.ListIndex)
x = Worksheets("Sheet1").ComboBox1.List(Worksheets("Sheet1").ComboBox1.ListIndex)
Sheets(x).Select
End Sub
となると思うが。
簡単に
Private Sub ComboBox1_Click()
x = Worksheets("Sheet1").ComboBox1.Value
MsgBox x
Sheets(x).Select
End Sub
でもOKのようだ。
ーー
それにしても、WEBや本ならコントロール（の中のコンボボックス）の説明のところに、選択したアイテムのとり方は必ず載っているよ。
WEB照会でも、Googleなどで「VBA コンボボックス」で照会すれば、沢山実例が出て、すぐ判る。

お礼

お礼が遅くなり、申し訳ありません。
丁寧に説明していただき、助かりました。
WEBや参考書でも調べましたが、いまひとつ求めるものと違うようでしたので、質問させていただいた次第です。

回答No.1

marbin

>コンボボックスで選択した値をセルに転記する方法はわかるので、

こんな感じかな？
Worksheets(1).Range("A1").Value = Me.ComboBox1.List(Me.ComboBox1.ListIndex)

お分かりと思いますが、シート選択は↓の
ような感じです。
Worksheets("シート名").Select

もうお分かりですね。
Worksheets(Me.ComboBox1.List(Me.ComboBox1.ListIndex)).Select
とすれば、コンボボックスで選択した行の名前のシートを選択できます。

変数を使えばもっと簡潔になります。

お礼

お礼が遅くなり、申し訳ありません。
わかりやすく説明していただき、助かりました。
ありがとうございました。