Access VBAのコンボボックスのリストを動的に設定する

主旨

Accessのコンボボックスで、AddItemメソッドを使用しようとして若干つまづいたので、メモとして残しておきます。
わかれば簡単な内容です。

記事の末尾に、参考として「Excelファイルを開いてコンボボックスからシートを選択するソースコード」も貼っておきます。

テスト用フォームの作成

とりあえず、フォームにコンボボックスを配置します。

フォームの名前はフォーム1のままで、コンボボックスの名前は、CmbBoxとしておきました。

VBAの記述

そして、Excel VBA の感覚で次のように記載します（フォーム1のクラスモジュールに記載します）。

Private Sub Form_Load()
    CmbBox.AddItem "東京"
    CmbBox.AddItem "神奈川"
    CmbBox.AddItem "千葉"
    CmbBox.AddItem "埼玉"
End Sub

フォームの立ち上げ時に処理する内容は、Excel VBAではPrivate Sub UserForm_Initialize()に書きますが、Access VBAではPrivate Sub Form_Loadに書いておきます。

しかし、このコードでは、フォームを立ち上げると次のエラーが出てきます。

実行時エラー '6014':
このメソッドを使用するには、"RowSourceType/値集合タイプ" プロパティに値リストを設定する必要があります。

ということで、エラーで指示があるように、値集合タイプ（RowSourceType）を値リストに指定します。

Private Sub Form_Load()
    CmbBox.RowSourceType = "Value List"
    CmbBox.AddItem "東京"
    CmbBox.AddItem "神奈川"
    CmbBox.AddItem "千葉"
    CmbBox.AddItem "埼玉"
End Sub

1行目にCmbBox.RowSourceType = "Value List"を加えただけです。
フォームを立ち上げ直すと、AddItemメソッドが正常に実行されて、次のようにリストが登録されています（めでたし）。

＜参考サイト＞
・コンボボックスのドロップダウンリストのデータを設定する（値リスト）
・"RowSourceType/値集合タイプ" プロパティ (Access)

プロパティシートで設定する方法

なお、値集合タイプ（RowSourceType）の設定はプロパティシートからもできます。
次のように、プロパティシートの「データ」タブに「値集合タイプ」の項目があります。

このところを、ドロップダウンリストから「値リスト」に変更するだけでも同じ結果となります。

（参考）Access VBAからExcelファイルを開いてシートを選択する方法

本来やりたかったのは、Accessから「任意のExcelファイル」を開いて、「任意のシート」を選択してもらいデータを取り込むということです。
Excel VBAとは、異なるところがいくつか出てきます。

以下、Access VBAでExcelを開き、コンボボックスにシートを表示するまでのサンプルコードを貼っておきます。
個々の説明は、コメント部分を参照してください。

＜フォーム画面＞

＜各コントロールの名称＞

場所	コントロール名	コントロールの種類
ファイル選択ボタン	FileSelectBtn	コマンドボタン
クリアボタン	ClearBtn	コマンドボタン
ファイルパス（右の四角部分）	FileNameText	テキストボックス
シート選択（右の四角部分）	SheetCmb	コンボボックス

＜ソースコード＞

Option Compare Database
Option Explicit

'フォームオープン時の処理
Private Sub Form_Load()
    SheetCmb.RowSourceType = "Value List" '値集合タイプを「値リスト」に設定
    FileNameText.Locked = True 'ファイルパス表示のテキストボックスを編集不可にする
End Sub

'ファイル選択ボタンのクリック時の処理
Private Sub FileSelectBtn_Click()
    'FileDialogオブジェクトでファイルを開く
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear 'Filtersコレクションはファイルの種類を指定（Clearで一旦全部解除）
        .Filters.Add "Excelファイル", "*.xls*" 'Excelファイルを指定（前半は表示名, 後半は拡張子指定）
        .AllowMultiSelect = False '複数選択を許可するか否か
        .InitialFileName = "C:\VBA" '最初に開くディレクトリを指定
        If .Show = True Then 'Showメソッドでダイアログボックスを表示（戻り値：開くボタン=-1, キャンセルボタン=0）
            FileNameText.Value = .SelectedItems(1) 'SelectedItemsプロパティはユーザーが選択したファイルパス一覧を返す
        Else
            Call FormReset 'フォーム内容を初期化（サブプロシージャ呼び出し）
            Exit Sub
        End If
    End With

    'Excelオブジェクトを取得
    Dim xlApp As Object: Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True 'Excelを表示

    '対象ファイルのオブジェクトを取得
    Dim wb As Object
    If IsFileOpened(FileNameText.Value) Then 'ファイルが開いているか否かを確認（関数を呼び出し）
        Set wb = GetObject(FileNameText.Value) 'ファイルのオブジェクトを取得（ファイルが開いている場合）
    Else
        Set wb = xlApp.Workbooks.Open(FileNameText.Value) 'ファイルを開いてオブジェクトを取得（ファイルが閉じている場合）
    End If

    Call ClearSheetCmb '一旦コンボボックスのリストを全部削除する（サブプロシージャ呼び出し）
    Dim ws As Object
    For Each ws In wb.WorkSheets 'wbオブジェクトの各シートを取得
        SheetCmb.AddItem ws.Name 'シート名をコンボボックスに追加
    Next
    SheetCmb.Value = SheetCmb.ItemData(0) '１つ目のシートを初期値に設定

    Set xlApp = Nothing 'Excelオブジェクトの開放
End Sub

'コンボボックスのリストを全部削除
Private Sub ClearSheetCmb()
    Dim i As Long
    For i = SheetCmb.ListCount - 1 To 0 Step -1
        SheetCmb.RemoveItem Index:=i 'RemoveItemメソッドで１つずつ削除（AccessではClearメソッドは使用できない）
    Next i
    SheetCmb.Value = "" '表示している値も削除
End Sub

'クリアボタンクリック時の処理
Private Sub ClearBtn_Click()
    Call FormReset 'フォームの内容を初期化（サブプロシージャ呼び出し）
End Sub

'フォーム内容を初期化する処理
Private Sub FormReset()
    FileNameText.Value = "" 'テキストボックスのファイルパスを削除
    Call ClearSheetCmb 'コンボボックスのリストを全部削除（サブプロシージャ呼び出し）
End Sub

'ブックが開かれているかチェックする(https://vbabeginner.net/check-if-the-book-is-open/)から拝借
Private Function IsFileOpened(fullPath As String) As Boolean
    On Error Resume Next 'エラーが生じても停止せず次の行から続行
    Open fullPath For Append As #1 'ファイルを開いてみてエラーが発生するか確認
    Close #1
    If Err.Number > 0 Then 'Err.Number→70:ファイルが開いている, 75:ファイルが存在しない（要注意）
        IsFileOpened = True
    Else
        IsFileOpened = False
    End If
End Function

結果として、ファイル名はFileNameText.Valueで取得され、シート名はSheetCmb.Valueで取得されるので、それを元に処理を続ければよいことになります。

＜参考サイト＞
・エクセルの神髄 : ファイルダイアログ（FileDialog）
・ComboBox の removeitem メソッド (Access)
・フォームを開くときに、コンボボックスの値を選択（アクセスVBA)
・Excel作業をVBAで効率化 : ブックが開かれているかチェックする
・アクセスVBAで既に開いているエクセルを閉じたい

以上