【VBA】セルの値とテキストボックスをリンクさせる

こんにちは、Ryoです。
何かと使い勝手が良いユーザーフォームを活用する機会は多いと思いますが、フォーム上に配置したテキストボックスの元データとして指定セルと値をリンクさせることができるControlSourceプロパティというものがありますので、今回はその内容について書いていきたいと思います。参考までにセルからSheet上に配置したテキストボックスへリンクする一例についても書いていきます。

1.サンプル概要
2.サンプルコード
3.まとめ

1.サンプル概要

事前にセルA1に「Test」と文字が入力されています。起動させたユーザーフォームのボタンにある「セルA1とリンク」を押すとテキストボックスにもセルA1と同じ文字が表示され、リンクされた状態になります。

そのリンクされた状態を確認する為に「セルA1の文字変更」ボタンを押すとインプットボックスが表示されるので任意の文字（例ではChange）を入力してOKを押すとセルA1に書き込まれますが、リンクされているテキストボックスも同じ内容に変更されます。

次はテキストボックス側の値を変化させて確認していきます。「TextBoxクリア」を押すと文字通りテキストボックス上の値が削除されますが、その際にリンクされているセルA1もクリアされます。

「TextBox1へ入力」を押すとテキストボックスへ ”サンプル入力” という文字が入力されますが、同時にリンクされているセルA1にも書き込まれますので、このようにリンクされた状態とすることができます。

ユーザーフォーム右上の「Reset」ボタンは最初にシートに入力されていた値（文字）を保管しておく為のもので、セルへの再入力の手間を省いて動作を繰り返して確認する目的のものですから、無くても問題ないです。

以上がサンプル概要になります。

2.サンプルコード

◆ユーザーフォーム構成

構成は以下の図の通りです。キャプションなどは各動作がわかるように適宜設定すれば良いと思います。

◆各コマンドボタンのコード

≪CommandButton1≫

Private Sub CommandButton1_Click()

  'テキストボックス1のソースとしてセルA1を指定
  TextBox1.ControlSource = "A1"
  'ラベル1にセルA1入力されていた文字列をセット
  '（サンプル動作を繰り返し確認する為に使用）
  Label1.Caption = TextBox1
  
End Sub

≪CommandButton2≫

Private Sub CommandButton2_Click()

  'セルA1の値が変更された場合にテキストボックス1の内容も
  '変更されることを確認する目的

  'インプットボックスに入力された内容をセルA1に書込む
  Cells(1, 1).Value = InputBox("セルA1の値を変更します" _
  & vbLf & vbLf & "入力してください")

End Sub

≪CommandButton3≫

Private Sub CommandButton3_Click()

  'ラベル1キャプションに表示している値（文字列）を
  'セルA1に書込み、ラベル1キャプション表示を”ー”に変更
  Cells(1, 1).Value = Label1.Caption
  Label1.Caption = "－"
 
End Sub

≪CommandButton4≫

Private Sub CommandButton4_Click()

  'コマンドボタン1を実行してセルA1とテキストボックス1が
  'リンクされている場合、テキストボックス1クリア＝セルA1もクリア

  'テキストボックス1のクリア
  TextBox1.Value = vbNullString

End Sub

≪CommandButton5≫

Private Sub CommandButton5_Click()

  'コマンドボタン1を実行してセルA1とテキストボックス1が
  'リンクされている場合、テキストボックス1入力＝セルA1にも反映
  TextBox1.Value = "サンプル入力"

End Sub

セルA1とTextBox1を実際にリンクさせている処理はCommandButton1で行っています。その処理を実行するにはControlSourceプロパティを使用します。

構文：オブジェクト.ControlSource = expression

ControlSourceプロパティを利用すると指定したコントロール（サンプルではテキストボックス）の元データとして指定したセルとリンクさせることができます。指定セルの値を変更するとテキストボックスの値も自動的に変更されます。

「CommandButton1」でテキストボックス1とセルA1をリンクさせ、「CommandButton2」でインプットボックスに文字入力を促し、入力された値をセルA1に書込むことでテキストボックスも同様に変化することを確認しています。

「CommandButton4」と「CommandButton5」はテキストボックス上を変化させてセルA1の状態を確認する為のものです。

「CommandButton3」は最初に入力されている文字を再入力するのが面倒なのでラベルキャプションに格納しておき、押すことで初期状態の表示に戻しています。

≪参考1≫別シートのセルをリンクしたい場合

上述のサンプルコードではアクティブシートのセルA1が対象になりますが、Sheetを指定したい場合もあるかと思います。その場合は「TextBox1.ControlSource = “Sheet3!A1″」のようにシート名を含めて指定すればOKです。

≪参考2≫セルの値をワークシート上に配置したテキストボックスとリンクしたい場合

ユーザーフォーム上ではなく、セルの値をSheetに配置したテキストボックスとリンクしたい場合の一例を参考までに書いてみます。これはセル⇒テキストボックスには反映できますが、その逆（テキストボックス⇒セルは不可）はリンクできないものになります。

動作としては以下の動画の通りですが、セルA1～A3をそれぞれのテキストボックスにリンクしています。これはWorksheet_changeイベントを利用しているので、各個の動作にはリンクしますが範囲削除の様な動作には対応できないので、使いどころを考える必要があります。

Private Sub Worksheet_Change(ByVal Target As Range)
'＊＊＊セルの値とシート上のテキストボックス連動一例＊＊＊

'Changeイベントを利用することで、セルの値が変更された際に
'テキストボックスへ反映させる形にする
'（本例ではテキストボックス⇒セルへ連動は不可）

  Dim Dt As Variant
  
  'セルA1/A2/A3に対して処理を行う
  Select Case Target.Address
    'セルA1の値が変更⇒TextBox 1へ反映
    Case Is = "$A$1"
        Dt = Target.Value
        ActiveSheet.Shapes("TextBox 1") _
        .TextFrame2.TextRange.Text = Dt
    'セルA2の値が変更⇒TextBox 2へ反映
    Case Is = "$A$2"
        Dt = Target.Value
        ActiveSheet.Shapes("TextBox 2") _
        .TextFrame2.TextRange.Text = Dt
    'セルA3の値が変更⇒TextBox 3へ反映
    Case Is = "$A$3"
        Dt = Target.Value
        ActiveSheet.Shapes("TextBox 3") _
        .TextFrame2.TextRange.Text = Dt
  End Select

End Sub

これはシートモジュールに記述して、そのワークシートのセルに変更があった際に動作するworksheet_changeイベントを利用した例になりますので、ご参考までに。