【Excel VBA】データ貼り付け先のシート名選択

質問

Excel2003を使用しています。

あるシートの選択部分を別のシートに値のみコピーするという作業をマクロで処理したく、コードを書きました。
コピー元のシートは毎回同じなのですが、貼り付け先のシートは、都度選択するようにしたいので、InputBox にシート名を入力して選択する方法にしていますが、これをシート名リストから選択するようにすることは可能でしょうか？
可能であれば、その方法を教えていただきたいのですが。。。

よろしくお願いします。

回答No.1

KenKen_SP · ベストアンサー

こんにちは。

Userform 不要でシート選択ダイアログを表示する方法です。

Excel 組み込みダイアログではあるのですが、通常の方法
Dialogs().Show ではシート選択ダイアログは表示できませんし、
そもそも Dialogs は戻り値がありません。

そこで、ちょっとトリッキーな手法を使ってシートオブジェクト
を返す関数にしてます。

Sub Sample()
　　Dim Sh As Worksheet
　　Set Sh = ShowSelectSheetDialog()
　　If Not Sh Is Nothing Then
　　　　MsgBox Sh.Name & "が選択されました。アクティブにします(・∀・）", _
　　　　　　　 vbInformation
　　　　Sh.Activate
　　Else
　　　　MsgBox "キャンセルされましたよ(・∀・）", vbExclamation
　　End If
　　Set Sh = Nothing
End Sub

' // シート選択ダイアログを表示
' // 戻り値： 選択されたシートオブジェクト キャンセル時：Nothing
Public Function ShowSelectSheetDialog() As Worksheet

　　Dim ShBackup As Worksheet
　　Application.ScreenUpdating = False
　　Set ShBackup = ActiveSheet
　　With CommandBars.Add(Temporary:=True)
　　　　.Controls.Add(ID:=957).Execute
　　　　.Delete
　　End With
　　' Return
　　If Not ActiveSheet Is ShBackup Then
　　　　Set ShowSelectSheetDialog = ActiveSheet
　　End If
　　ShBackup.Select
　　Application.ScreenUpdating = True

End Function

お礼

KenKen_SP さん、こんにちは。

早速、教えていただいた方法で試してみたところ、希望通りのことができました(^o^)丿
今までも何度か回答をいただいていますが、いつも「すごいなぁ～」としみじみ(？)思っています。

ありがとうございました！

回答No.2

zap35

ユーザーフォームを作成し、ListBoxとCommandButtonをそれぞれ１つずつ配置します。(UserForm1、ListBox1、CommandButton1ができるはずです）
なおCommandButtonはキャンセルボタンとして使用します。

この行直下のマクロは標準モジュールシートに貼り付けます
Sub ListBoxShow()
Dim idx As Integer
　UserForm1.ListBox1.Clear
　For idx = 1 To ThisWorkbook.Worksheets.Count
　　UserForm1.ListBox1.AddItem Worksheets(idx).Name
　Next idx
　UserForm1.Show
End Sub

以下のマクロはユーザフォームモジュールに貼り付けます。
ユーザフォームモジュールシートはVBE画面でユーザーフォームをダブルクリックすると表示されます

Private Sub CommandButton1_Click()
　Me.Hide
End Sub

Private Sub ListBox1_Click()
　MsgBox (ListBox1.Text & "が選択されました")
'Copy & Paste例
'　Worksheets("Sheet1").Range("A1:A10").Copy _
'　　Destination:=Worksheets(ListBox1.Text).Range("A1")
　Me.Hide
　Worksheets(ListBox1.Text).Activate
End Sub

お礼

zap35 さん、こんにちは。

今回、ユーザーフォームを使用したのは初めてだったのですが、丁寧に説明していただいたおかげで、希望通りのことができ、勉強になりました。

ありがとうございました<(_ _)>