フォームの全オブジェクトの値をシートへ書き出す

Contents
• 今回の目標
• サンプルコード
• 説明
• フォームのデータを書き出す
• フォームのデータを復元する
• 実務上の注意
• 関連リンク

コードを紹介します。次項以降で解説を行います。
フォームのボタン『書き出しBT』オブジェクトをクリックすることでフォームのデータを"Sheet1"シートへ書き出します。

サンプルファイルを用意しました↓
フォームオブジェクトを書き出しと読み込み.xlsm

Const ROW_START As Long = 4
Const COL_OBJ_NAME As Long = 2
Const COL_OBJ_VAL As Long = 3
Const SHEET_NAME As String = "Sheet1"

'====================================
' シートからフォームデータの読み込み
'====================================
Private Sub UserForm_Initialize()
    Dim buf As Variant
    Dim tn As String
    Dim rowEnd As Long
    Dim inVal As String
    Dim bl As boolean
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
    rowEnd = ws.Cells(Rows.Count, COL_OBJ_NAME).End(xlUp).Row
    
    'フォーム全てのオブジェクト
    For Each buf In Me.Controls
        tn = TypeName(buf)
        bl = (tn = "ComboBox") Or (tn = "TextBox") Or _
            (tn = "CheckBox") Or (tn = "OptionButton")
        'tn = "読み込むオブジェクトの種類"とする
        If bl Then
            'Vlookup関数でエラー→フォームにオブジェクトが存在しない
            On Error GoTo ErrTrapVlook
            inVal = WorksheetFunction.VLookup(buf.Name, ws.Range( _
                    ws.Cells(ROW_START, COL_OBJ_NAME), _
                    ws.Cells(rowEnd, COL_OBJ_VAL)), 2, False)
            buf.Value = inVal
ErrTrapVlook:
            Err.Number = 0
        End If
    Next buf
    Set ws = Nothing
End Sub

'====================================
' フォームのデータを書き出す
'====================================
Private Sub 書き出しBT_Click()
    Dim buf As Variant
    Dim tn As String
    Dim i As Long
    Dim bl As boolean
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
    i = ROW_START
    
    For Each buf In Me.Controls
        tn = TypeName(buf)
        bl = (tn = "ComboBox") Or (tn = "TextBox") Or _
            (tn = "CheckBox") Or (tn = "OptionButton")
        'tn = "書き出すオブジェクトの種類"とする
        If bl Then
            ws.Cells(i, COL_OBJ_NAME) = buf.Name
            ws.Cells(i, COL_OBJ_VAL) = buf.Value
            i = i + 1
        End If
    Next buf
    
    MsgBox "書き出しが完了しました", vbInformation
End Sub

フォームのデータを書き出する

フォームのデータを書き出すにはシートからフォームに書き込むのが一番楽です。他が思い浮かびません（笑）。フォームに設置されたボタン『書き出しBT』オブジェクトをクリックすることで、本プロシージャを呼び出してデータを書き出します。
ForEachでフォームのオブジェクトを回します。その中で特定の型（今回は、ComboBox, TextBox, CheckBox, OptionButton の４種類の型）なら値を表に書き出します。

フォームのデータを復元する

フォームの初期化イベント時（フォームが呼び出されると自動的に呼び出される関数）にデータ読み込みを行います。フォームにあるオブジェクトを総当たり形式でForEachを回し、特定の型のオブジェクトだった場合に、表からVlookUp関数で読み込んでいます。この時にエラー処理をしているのは、表をユーザーに書き換えられてしまった等の不測の事態のためです。

実務上の注意

今回のフォームを使ってユーザーに入力させる際には注意がいくつかあります。それは、シート上の表データを直接操作されないようにすることで、書き出したデータの改ざんやオブジェクト名の書き換えられることによるプログラムエラーが発生します。ただし、これは対策を打てば全く問題ありませんので、その方法について触れておきます。

1. データ書出シートは非表示にしてシートの保護をかける
   フォームの呼び出しボタンとデータを書き出すシート（以下、書出シート）を別にして、書出シートのVisible属性を"xlVeryHidden"に設定してます。次にVBAのプロジェクトのロックをかけます。これで管理者以外からデータを書出シートの閲覧をすることができなくなります。パスワードを忘れないようにしましょう。
2. シートの保護をかける
   シートに保護をかけ、データ書き出し前にシートの保護を解除します。

トップページへ