VBAフォーム 大量の入力項目をSheetのテーブルに転記する処理のまとめ方

Question

前提・実現したいこと

全部で50項目ほどのフォームデータを、Sheetのテーブルに転記したい

発生している問題・エラーメッセージ

今のところ下記のように書いています。

Dim row As Integer  ' テーブルの行
Dim col As Integer  ' テーブルの列

With Sheets(1).ListObjects(1)
    ' 「氏名」
    .Range(row, col).Value = Me.Text_Name.Value: col = col + 1
    ' 「カナ」
    .Range(row, col).Value = Me.Text_Kana.Value: col = col + 1

これだと可読性と保守性に欠けます。
それと一部転記方法を指定する箇所があります。例えば「生年月日」と「年齢」を入力する項目があり、転記する際は生年月日の列に「生年月日（年齢）」と転記するような感じです。

試したこと

Dim ctrl As Control

For Each ctrl In Me.Controls
    Select Case True
        Case ctrl.Name Like "Text*"
            .Range(row, col).Value = ctrl.Value: col = col + 1
        Case ctrl.Name Like "Option*"
            .Range(row, col).Value = ctrl.Caption: col = col + 1
    End Select
Next

上記のように書くことを考えたのですが、Me.Controlsでとれるデータがバラバラ（おそらくオブジェクト配置順？）なので指定の列に入れることができません。

Answer 1

テーブルにしているなら、ListObjectsの機能を使って楽しましょう。
ListColumns("列名")で列を参照できます。

そして、フォームのコントロールのTagプロパティに列名を設定しておきます。

すると、下記のようなコードになります。

    With Sheets(1).ListObjects(1)
        .ListRows.Add          '行追加
        Dim r As Long
        r = .ListRows.Count    'テーブルのデータ行数
        
        Dim ctrl As Control
        For Each ctrl In Me.Controls
            If ctrl.Tag <> "" Then
                With .ListColumns(ctrl.Tag).DataBodyRange(r)    '列の最終行
                    Select Case TypeName(ctrl)
                    Case "TextBox"
                        .Value = ctrl.Value
                    Case "OptionButton"
                        If ctrl.Value Then .Value = ctrl.Caption
                    End Select
                End With
            End If
        Next
    End With

---

以下、蛇足ですが、参考までに。

それと一部転記方法を指定する箇所があります。例えば「生年月日」と「年齢」を入力する項目があり、転記する際は生年月日の列に「生年月日（年齢）」と転記するような感じです。

これに関しては、年齢は入力する必要はないと思います。
生年月日と現在日から計算することができますので。
シートには生年月日のみ格納して年齢は関数で計算するのがいいでしょう。
年齢を入力してしまうと、1年後にそのシートを開いたとき、年齢は正確なデータではありません。
関数で計算すればいつ開いても正確な年齢が表示できます。

Comments

[D0NKAMA]

回答ありがとうございます。
確かに年齢は関数で計算したほうがいいですね^^。是非この方法を使わせていただきたいと思います！ですが一つ質問があります。
テーブルにデータがない状態でこの処理を行うと
「実行時エラー'438'　オブジェクトは、このプロパティまたはメソッドをサポートしていません」
と出ます。なので、行数が0（.ListRows.Count = 0）のときは直接行と列を指定しながら転記しないといけないですか？

[hatena19]

データがない場合の考慮をしてませんでした。

.ListRows.Add で行追加してから書き込むように回答のコードを修正しました。

[D0NKAMA]

先頭で行追加したら済む話でしたね！
お手数おかけしましたm(_ _;)m