ExcelVBAで入力規則リストを動的に作成する(セルにリストを生成する)ことができる。
さっと以下のような感じである。
┌──────────────────────────────────────┐
│With Selection.Validation                                                   │
│    .Delete                                                                 │
│    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _              │
│        Formula1:="1:織田信長,2:豊臣秀吉,3:徳川家康"                        │
│    .InCellDropdown = True                                                  │
│    .IgnoreBlank = True                                                     │
│    .ShowInput = True                                                       │
│    .InputTitle = "コード入力"                                              │
│    .InputMessage = "コードを入力してください"                              │
│    .ShowError = True                                                       │
│    .ErrorTitle = "エラー"                                                  │
│    .ErrorMessage = "無効です"                                              │
│    .IMEMode = xlIMEModeNoControl                                           │
│End With                                                                    │
└──────────────────────────────────────┘
しかしながら、リストのデータを定義する「Formula1:=」の部分について
256文字以上となるとファイルを保存した時点で
内容が空にされてしまうようだ。
従って、こいつらはリスト数が少ない時にしかた使わないとした方がよさそうだ。

X座標、Y座標でループする方法。
┌──────────────────────────────────────┐
│Dim x As Long, y As Long                                                    │
│For y = 1 To Selection.Rows.Count                                           │
│    For x = 1 To Selection.Columns.Count                                    │
│        MsgBox "x=" & x & ",y=" & y & ",値=" & Selection.Cells(y, x).Value  │
│    Next x                                                                  │
│Next y                                                                      │
└──────────────────────────────────────┘
範囲でループする方法。
┌──────────────────────────────────────┐
│Dim r As Range                                                              │
│For Each r In Selection                                                     │
│    MsgBox "x=" & r.Column & ",y=" & r.Row & ",値=" & r.Value               │
│Next r                                                                      │
└──────────────────────────────────────┘

Excel2003の場合、オートシェイプ(ボタンなど)の削除時は、
シートの保護を解除していないとエラーとなる。
→「アプリケーション定義またはオブジェクト定義のエラーです。」

以下のような感じで入力規則によるリストを作成したところ…。
┌──────────────────────────────────────┐
│With ActiveCell.Validation                                                  │
│    .Delete                                                                 │
│    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, _       │
│        Formula1:="1:東京,2:大阪,3:名古屋"                                  │
│    .InCellDropdown = True                                                  │
│    .IgnoreBlank = True                                                     │
│End With                                                                    │
└──────────────────────────────────────┘
Excel2003で以下のようなエラーが出てしまった(Excel2010では出なかった)。
┌──────────────────────────────────────┐
│実行時エラー'-2147417848(80010108)':                                        │
│オートメーションエラーです。                                                │
│起動されたオブジェクトはクライアントから切断されました。                    │
└──────────────────────────────────────┘

「更新」ボタンを押して実行した場合にエラーが発生し、
マクロを関数単位で直接実行する分には問題なかった。

ということで、原因はフォーカス。
ボタンを押した時点でフォーカスがボタンに当ってしまうので
エラーでごねられてしまうということらしい。

解決策は、ボタンが押された後で、シートに故意にフォーカスをあて直す。
┌──────────────────────────────────────┐
│Cells(1, 1).Select                                                          │
└──────────────────────────────────────┘

もしくはボタンにフォースを当てなくする(TakeFocusOnClick=False)。

文字数がものすごく多い文字列を調査したところ、
どうも途中で文字列が欠けているのではないかという疑念が生まれた。

ウオッチ式で表示すると250文字位で切れてしまっているからだ。

それじゃということで、メッセージボックス(MsgBox)で表示させたところ
こちらも最後まで表示されない(明らかに途中で切れている)。

そういえば、セルの書式を文字列にすると256文字くらいまでしか入力できなかったな。

そんなことから、ExcelVBAは250文字前後までしか文字列に格納できない、
という推論(っていうか誤解)をしてしまった。

そんなことはない。2GBまでOKだ。

実は以下の制約にひっかかったのだった。
  ・ウオッチ式では「250文字まで」(1バイト換算)しか表示できない
  ・メッセージボックス(MsgBox)では「1023文字まで」(1バイト換算)しか表示できない
ということで、「Debug.Print」でイミティトウインドウに表示させたら
全て表示することができた。

しかし、ダブルで制約にひっかかると誤解してしまう。

シフトJISファイルを読み込んでUTF-8に変換し上書する関数。
┌──────────────────────────────────────┐
│Public Sub UTF8変換(対象ファイル名 As String)                               │
│    Dim 変換前 As New ADODB.Stream                                          │
│    Dim 変換後 As New ADODB.Stream                                          │
│                                                                            │
│    Set 変換前 = CreateObject("ADODB.Stream")                               │
│    Set 変換後 = CreateObject("ADODB.Stream")                               │
│    変換前.Charset = "Shift_JIS"                                            │
│    変換後.Charset = "UTF-8"                                                │
│    Call 変換前.Open                                                        │
│    Call 変換後.Open                                                        │
│                                                                            │
│    Call 変換前.LoadFromFile(対象ファイル名)                                │
│    Call 変換前.CopyTo(変換後)                                              │
│    Call 変換後.SaveToFile(対象ファイル名, adSaveCreateOverWrite)           │
│                                                                            │
│    Call 変換後.Close                                                       │
│    Call 変換前.Close                                                       │
│    Set 変換後 = Nothing                                                    │
│    Set 変換前 = Nothing                                                    │
│End Sub                                                                     │
└──────────────────────────────────────┘

なお、ExcelVBAエディタ上から「ツール」→「参照設定」を開き、
「☑Microsoft ActiveX Data Objects 6.1 Library」とチェックを入れる必要あり。

以下のような感じ。
┌──────────────────────────────────────┐
│With Selection.Validation                                                   │
│    .Delete                                                     ─初期化    │
│    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _              │
│        Formula1:="1:織田信長,2:豊臣秀吉,3:徳川家康"            ─リスト内容│
│    .InCellDropdown = True                                      ─リスト    │
│    .IgnoreBlank = True                                         ─空欄許容  │
│    .ShowInput = True                                           ┐          │
│    .InputTitle = "コード入力"                                  │ガイド情報│
│    .InputMessage = "コードを入力してください"                  ┘          │
│    .ShowError = True                                           ┐          │
│    .ErrorTitle = "エラー"                                      │エラー情報│
│    .ErrorMessage = "選択肢中の内容のみ入力できます"            ┘          │
│End With                                                                    │
└──────────────────────────────────────┘
リスト内容はカンマ区切りとなる。

以下の指定により、完全に入力を限定的にできるか否かを決められる。
┌──────────────────┬───────────────────┐
│AlertStyle:=xlValidAlertStop        │停止(決められた内容しか入力できない)  │  
├──────────────────┼───────────────────┤
│AlertStyle:=xlValidAlertWarning     │注意(他も入力できる)                  │
├──────────────────┼───────────────────┤
│AlertStyle:=xlValidAlertInformation │情報(他も入力できる)                  │
└──────────────────┴───────────────────┘

セルは以下の階層で指定する。
┌──────────────────────────────────────┐
│ブック.シート.セル                                                          │
└──────────────────────────────────────┘
ここでは「ブック.シート」階層についてまとめた。

以下のようにも指定できるが、それは上記の省略形である(詳細は後述)。
┌──────────────────────────────────────┐
│セル                                                                        │
│シート.セル                                                                 │
└──────────────────────────────────────┘

ブックの指定手段は以下の通り。
┌────────────────┬─────────────────────┐
│ThisWorkbook.シート.セル        │マクロのあるブック                        │
│ActiveWorkbook.シート.セル      │アクティブなブック                        │
└────────────────┴─────────────────────┘

シートの指定手段は以下の通り。
┌────────────────┬─────────────────────┐
│ブック.ActiveSheet.セル         │アクティブなシート                        │
│ブック.Worksheets(シート名).セル│シート名指定によるシート                  │
└────────────────┴─────────────────────┘
「Worksheets(シート名)」は「Sheets(シート名)」でもOKだが、
(グラフ等を除く)純粋なワークシートとしては「Worksheets(シート名)」指定が厳密的。

ブックの指定は、マクロの書いているブックに絞り込んだ方が安全。
┌─────────────────────┬────────────────┐
│○ThisWorkbook.ActiveSheet.セル           │                                │
│△ActiveWorkbook.ActiveSheet.セル         │完全に動的な指定                │
│○ThisWorkbook.Worksheets(シート名).セル  │完全に静的な指定                │
│△ActiveWorkbook.Worksheets(シート名).セル│                                │
└─────────────────────┴────────────────┘
できるだけ静的な指定とすると想定外のミスを減らせる。

そして、お待ちかねの省略形の実態。
┌─────────────┐  ┌──────────────────────┐
│セル                      │＝│ActiveWorkbook.ActiveSheet.セル             │
├─────────────┤  ├──────────────────────┤
│Worksheets(シート名).セル │＝│ActiveWorkbook.Worksheets(シート名).セル    │
└─────────────┘  └──────────────────────┘
つまり動的な指定のオンパレードなのだ。

ということで、省略形は一時的なプログラムではいいとしても
本格的なプログラムでは避けた方がいいだろう。

「Cells(2, 3)」の入力値が、半角かつ大文字と限定されている場合に、
誤入力を防ぐため全角および小文字を変換するイベントは以下のような感じになる。
┌──────────────────────────────────────┐
│Private Sub Worksheet_Change(ByVal Target As Range)                         │
│    If Target.Row = 2 And Target.Column = 3 Then                            │
│        Application.EnableEvents = False                                    │
│        Target.Value = StrConv(Target.Value, vbUpperCase Or vbNarrow)       │
│        Application.EnableEvents = True                                     │
│    End If                                                                  │
│End Sub                                                                     │
└──────────────────────────────────────┘

シート記述するマクロで、以下のように記述すると
「Cells(2, 3)」に値を入力した場合、自動で「様」がついてくれる想定だ。
┌──────────────────────────────────────┐
│Private Sub Worksheet_Change(ByVal Target As Range)                         │
│    If Target.Row = 2 And Target.Column = 3 Then                            │
│        Target.Value = Target.Value & "様"                                  │
│    End If                                                                  │
│End Sub                                                                     │
└──────────────────────────────────────┘
でも実際に実行してみると、入力後「様様様…」という、大量の「様」がついてしまう。

これは、自分で自分の値を変えてその結果新たに自分を呼び出しているから
…つまり無限ループしてるって訳だ。

ということで、これを防ぐ方法。
┌──────────────────────────────────────┐
│Private Sub Worksheet_Change(ByVal Target As Range)                         │
│    If Target.Row = 2 And Target.Column = 3 Then                            │
│        Application.EnableEvents = False                                    │
│        Target.Value = Target.Value & "様"                                  │
│        Application.EnableEvents = True                                     │
│    End If                                                                  │
│End Sub                                                                     │
└──────────────────────────────────────┘
「Application.EnableEvents = False」で一旦自分が呼ばれないようにしてから
値を変えればいいってことだ。
もちろん、次のイベントのために、「True」で戻すことも忘れずに。