プルダウンしたセルの内容と同じ名前のシートを表示する命令の記述は？

質問

エクセルの入力規則で、Sheet1のセルＢ５に「Ａ～Ｊ」の文字をプルダウンできるようにしました。この文字（例「Ａ」）はワークシートの名に対応しています。
教えていただきたいことは以下のとおりです。
　【プルダウンした文字(Ａ）に対応するワークシート(Ａ)を表示し、そのシートのセルＡ１を選択する命令を作成して、Sheet１のボタンに登録したいのです。この命令（ＢＶＡでしょうか）はどのように記述すればよろしいでしょうか？】

回答No.3

Wendy02 · ベストアンサー

こんにちは。

#1 の回答者です。

>今回の想定とは異なりますが、
>別の場面で利用させていただきたいと思います。

別の場面？「不可」という意味ですね。そういう婉曲的な表現って、意味がありません。

あくまでも、入力規則＋フォームのコマンドボタンにつけたいなら、そう、はっきりとおっしゃっていただいたほうが早いと思います。それで、相手の人が、それだけの技術があって回答してくれるか、ゲタを預けたほうが、掲示板を利用する上でスムーズに行くと思います。掲示板の世界というのは、とても狭いですから、あまり、別の人が回答してくれると期待しないほうがよいです。マクロの常連さんなんて、#2で書いていただいた人とか、ほんの少数の人だけです。

私には、こうしたシートを替えるようなアクションといものは、ワークシートとは区分けされた部分での選択が必要と考えたからです。そういうこちらの理由は、分からないでしょうけれども。

以下のコードを、「標準モジュール」に貼り付けてください。

単に、フォームボタンを右クリックして、Button1_Click を登録してくださればよいですが、
「標準モジュール」上で、Sub SettingButton() にカーソルを置いて、[F5] を押してくだされば、「設定されました。」と出たら、設定されます。

'----------------------------------------------------------
'標準モジュール

'Option Explicit
Sub Button1_Click()
　'入力規則のある場所
　Const ValidRng As String = "E1"
　On Error Resume Next
　With ActiveSheet
　If .Range(ValidRng).Value <> "" Then
　　 Application.Goto Worksheets(.Range(ValidRng).Value).Range("A1")
　　 If Err.Number > 0 Then
　　　　MsgBox .Range(ValidRng).Value & ": 目的のシートが見当たりません。", 32
　　 End If
　End If
　End With
　On Error GoTo 0
End Sub
'--------------------------------------------------------
'フォームボタンの設定
'フォームボタンのあるシートをアクティブにしておいてください。
Sub SettingButton()
　　Dim num As Integer
　　''マクロを取り付けるフォームボタンのインデックス
　　num = 1
　　On Error GoTo ErrHandler
　　With ActiveSheet
　　　　.Buttons(num).OnAction = ""
　　　　.Buttons(num).OnAction = "Button1_Click"
　　　　If InStr(.Buttons(num).OnAction, "Button1_Click") = 0 Then
　　　　　　Err.Raise 18
　　　　End If
　　End With
ErrHandler:
　　If Err.Number > 0 Then
　　　　MsgBox "設定ができていません。", 48
　　Else
　　　　MsgBox "設定されました。"
　　End If
End Sub

'------------------------------------------------------------
なお、このマクロは、シートの行き先を間違えた場合は、Ctrl + Z で、戻ることが可能です。

もちろん、私なら、入力規則だけで、目的の場所にジャンプすることも可能です。

お礼

ありがとうございました。
マクロの記述内容は全く分かりませんが、きちんと動きました。
ここの掲示板の使い方も教えていただきありがとうございました。

回答No.2

imogasi

まだ質問者にとって敷居が高いのではないですか。
まずマクロの記録です。
Sheet1をアクチブにして、Sheet2のA２を選択すると
Sub Macro1()
Sheets("Sheet2").Select
Range("A2").Select
End Sub
となります。
ーー
この「Sheet2」のところをプルダウンで決めるわけです。
ユーザーフォームのコンボなどで選択する場合はコンボのイベントで捉えるのですが、入力規則の場合は、プルダウンメニューを選択した結果、セルの値の変化を捉えます。
プルダウンのオブジェクト名が捉えられないからです。
ーー
Sheet1の
A１セルに入力規則が設定されてある（内容はシート名の文字列）として
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets(Target.Value).Activate
Sheets(Target.Value).Range("A1").Select
End If
End Sub
（１）イベントという考え方
（２）他シートのActivate、Selectの関係
こんな短いコードですが、色々な経験が詰まっています。
存在しない場合の手当てやChangeイベントの難しさ（Delした場合がどうだとか）がありますが正常限定で省略。
当面丸呑みで。

補足

遅くなりましたが、ご回答ありがとうございます。
いただいた内容を標準モジュールに記述しましたが、フォームのボタンにマクロ登録できません。
「Microsoft Excel Objects」のシート１に記述しても同様です。
やはり、この操作の自動化は私にとって、かなり敷居が高いようです。

どのようにすればボタンに登録できるのでしょうか？。

回答No.1

Wendy02

こんばんは。

入力規則でもできますが、手続きとしては面倒です。

できれば、コントロールツールのコンボボックスに取り付けたほうが簡単ですね。
たとえば、コンボボックスで選んで、ダブル・クリックしてジャンプするというのはいかがですか？

コントロールツールのコンボボックスを選び、それをワークシートにおきます。
右クリックして、[コードの表示]で、以下のコードを別に貼り付けます。
以下のコードでできます。

コードの表示でできた、Private Sub ComboBox1_Click　は、削除してしまって結構です。

--------------------------------------------------------
'シートモジュール、ダブルクリックイベント
Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
　　Dim ShName As String
　　ShName = ComboBox1.Value
　　Application.Goto Worksheets(ShName).Range("A1")
End Sub
Private Sub ComboBox1_GotFocus()
　'シート名を、コンマ区切りで入れる
　 ComboBox1.List = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J")
End Sub

-----------------------------------------------------------

貼り付けた後で、コントロールツールバーの青い三角定規がオンになっていますから、これをオフにすれば、すぐに使えるようになります。

お礼

おそくなりましたが、ありがとうございました。
試したらきちんと動きました。
今回の想定とは異なりますが、
別の場面で利用させていただきたいと思います。