Excelのセル選択ボックスをVBAで実現する

Excelを操作していると、セルを選択するダイアログボックスがあることに気付きます。

このようなボックスは、Excelのセルをドラッグするとその範囲のセル番地名を入力してくれます。
これが自作のVBAのツールの中でできればなんてすばらしいんでしょう。
ところが。テキストボックスでも、ドロップダウンでもないので、どこから入れるものなのか。
Excelの操作部品である以上、どこかには入れる手段があるんじゃないかと。
実はこれ、あったんです。

RefEdit

この部品、名称はRefEditと言います。
リボンの中の開発タブを表示させて、挿入の中のコントロールの選択をクリックします。

コントロールの一覧から「RefEdit.Ctrl」を選択。

あとはシート上ど適当にドラッグすれば、この部品が入ります。

なんかそれっぽいのが入りましたよね！
でこれを右クリックしてプロパティを設定していきます。

LinkedCellにこの部品で入れた値をリンクするセルを指定。
で、デザインモードを解除して実行！！

どんだけやってもボタンがクリックできないし、ドラッグしても何も反応しない。

手詰まり！
というわけにもいかないので、いろいろプロパティを探ってみましたが、やはりできないのでこの方法は一旦諦めて、ユーザーフォームから出す方法を考えました。

ユーザーフォームからのRefEdit

よく考えたら、Excelのセル選択ボタンはすべてダイアログボックス上にあるものしか見たことないのでユーザーフォームでもできるんじゃないかと思って、VBEを起動して、ユーザーフォームを追加しました。

で、ユーザーフォーム上にRefEditって配置するボタンってどっから挿入すんのかなって挿入メニューとか探ってみたけどもない。（そういえばVBEってまだリボンインターフェースじゃないのね）
こういう時はだいたいあるべきところを右クリックすれば何か起きるので、コントロールダイアログボックスを右クリックしたら、やっぱり出てきました。

その他のコントロールをクリックしたら、RefEdit.Ctrlが選べましたのでOKします。

コントロールにRefEditが追加された！！

ではこれをクリックしてフォーム上をドラッグします。

フォーム上にできた！！
あと、OKボタンも追加して、ボタンをダブルクリックします。

OKボタンをクリックしたときに動作するコードを記述できるので、
Private SubからEnd Subの間に次のコードを記述します

Dim re
re = RefEdit1.Value
Range(re).Value = "34"

RefEdit1はRefEditコントロールに初めから設定されている名前。わかりやすい名前に書き換え推奨。

こんどは、このフォームを呼び出す方法ですが、今のままではフォームはExcel側からは開けないので、開くためのマクロを記述します。
標準モジュールを追加します。

次のようなコードを標準モジュールに追加します。
UserForm1はユーザーフォームを追加した時に初めから設定されてる名前、変更推奨。

Sub セル選択()
  UserForm1.Show
End Sub

Excelに戻って、表示のマクロからマクロの表示。

この中にセル選択があるので実行。

UserForm1が表示され、セル選択できます！

OKボタンをクリックすると選択範囲に34の文字が入ります！！

応用

こんな感じでオリジナルのセル選択を使ったダイアログボックスも作れるかなと。
今回で言う、マクロ名「セル選択」のマクロはリボンに登録することもできるし、ショートカットキーも設定できます。
だからExcel機能の一つをこれで作れると思います。
また、範囲の指定をするのであれば、For Eachループとはものすごく相性がよくて。
OKボタンにこんなコードを仕込めば、範囲の分、１からの数値を埋めてくれます。

Private Sub CommandButton1_Click()
  Dim i As Range
  Dim y As Integer
  For Each i In Range(RefEdit1)
      y = y + 1
      i.Value = y
  Next
End Sub