エクセルのユーザーフォームを作って、使ってみるまでを目標に、連載記事でご紹介しています。
前回の記事では、テキストボックスに入力した値からワークシートを検索して、関連する値をユーザーフォーム上の別のテキストボックスに表示させる方法をご紹介しました。
![](https://tonari-it.com/wp-content/uploads/textbox_search-160x90.jpg)
今回は、ユーザーフォームのコマンドボタンを押したとき、ワークシートの値をテキストボックスに入力されている値で更新する方法をご紹介します!
前回までのおさらい
これまでに作成したユーザーフォームがこちら。
MyFormというフォーム上に、「品名」「生産地」とラベルを付けたテキストボックス2つと、コマンドボタンを1つ配置しています。
テキストボックスの「品目」「生産地」には、それぞれTextItem、TextRegionというオブジェクト名と、コマンドボタンにはbtnOKというオブジェクト名をつけてあります。
前回はテキストボックスのAfterUpdateイベントで、「品目」に入力された値をワークシートの「品目」列から検索して、ヒットすれば「生産地」の値を取得してユーザーフォームの「生産地」テキストボックスに表示させたのでした。
前回のワークシート上のリストがこちら。
そして、検索用のコードがこちらでした。
Private Sub TextItem_AfterUpdate() Dim myRange As Range Dim rngSearch As Range Set myRange = ActiveSheet.Range("A1:B7") Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart) If Not rngSearch Is Nothing Then 'ヒットした値を生産地テキストボックスにセット TextRegion.Value = ActiveSheet.Cells(rngSearch.Row, rngSearch.Column + 1).Value End If End Sub
今回は、こちらのコードに手を加えて、ユーザーフォーム上のコマンドボタンをクリックしたときに、
- ワークシートのリスト上に存在している値がテキストボックス「品名」に入力されていて、「生産地」の値がリストとテキストボックスで異なる場合、テキストボックスの値でワークシートを更新する
- テキストボックス「品名」に入力された値が、ワークシートのリスト上に存在していなかった場合、テキストボックス「品名」「生産地」の値をワークシートのリスト最下行に追加する
- ワークシートに変更があったら、ブックを保存する
の3つの処理をさせてみましょう。
テキストボックスの入力値でワークシートを更新する
まずは、テキストボックス「品名」の値がワークシートのリスト上に存在していた場合です。
コードの中でやるべきことは、
- 「品名」に入力された値がワークシートのリストの何行目にあるかを検索
- ヒットした「品名」と同じ行の、隣の列にあるセルの値を、テキストボックス「生産地」の入力値で更新
- ブックを保存
の3ステップです。
前回のコードにこの3ステップを加えて、コマンドボタンのClickイベントに差し込むと、こんな感じになります。
Private Sub btnOK_Click() Dim myRange As Range Dim rngSearch As Range With ActiveSheet Set myRange = .Range(.Cells(1, 1), .Cells(.Range("A1").End(xlDown).Row, 2)) 'テキストボックスの入力値から、ワークシート更新対象の行を検索 Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart) If Not rngSearch Is Nothing Then 'テキストボックスに入力した値とワークシートのリストの値が異なっていた場合、ワークシートを更新 If TextRegion.Value <> .Cells(rngSearch.Row, rngSearch.Columns(2).Column).Value Then .Cells(rngSearch.Row, rngSearch.Columns(2).Column).Value = TextRegion.Value End If End If End With ThisWorkbook.Save End Sub
早速こちらのコードを実行してみましょう。
まず、ユーザーフォームのテキストボックス「品名」に「りんご」を入力します。
ワークシートのリスト上から、「りんご」と紐付いた「長野」という「生産地」の値がテキストボックス「生産地」に表示されました。
ではテキストボックス「生産地」の値を変更して、コマンドボタンをクリックすると…
新しいレコードをワークシートに追加する
ここまでで、既存の「品名」と紐づいた「生産地」を更新することができました。
今度は、新しい「品名」がテキストボックスに入力された場合、ワークシートの最下行に追加する方法を考えてみましょう。
- 「品名」に入力された値がワークシートに存在するか検索
- 存在していなかった場合、ワークシートのリスト最下行に、テキストボックス「品名」「生産地」の値を追加
- ブックを保存
の3ステップに整理できそうです。
ステップ1の、「「品名」に入力された値がワークシートに存在するか検索」については、7行目のrngSearchでやってくれていますね。
また、ステップ3の「ブックを保存」についてもやってくれていますので、ステップ2だけ考えれば良さそうです。
テキストボックスに入力した「品名」がワークシートに存在するかどうかチェックする方法については、「品名」がワークシート上に存在しない場合、rngSearchにはNothingが返ってくるはずです。
なので、9行目にあるIF文に、分岐を加えればいいですね。
rngSearchにNothingが返された場合、ワークシートのリストの最下行にテキストボックスの値を追加する処理を加えると、こんな感じのコードになります。
'テキストボックスの入力値から、ワークシート更新対象の行を検索 Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart) If Not rngSearch Is Nothing Then 'テキストボックスに入力した値とワークシートのリストの値が異なっていた場合、ワークシートを更新 If TextRegion.Value <> .Cells(rngSearch.Row, rngSearch.Columns(2).Column).Value Then .Cells(rngSearch.Row, rngSearch.Columns(2).Column).Value = TextRegion.Value End If Else 'ワークシートに新規追加 .Cells(myRange.Rows.Count + 1, myRange.Columns(1).Column).Value = TextItem.Value .Cells(myRange.Rows.Count + 1, myRange.Columns(2).Column).Value = TextRegion.Value End If
ワークシートが更新された場合のみブックを保存する
ワークシートに新しい「品名」と「生産地」を追加する処理のコードができたところで、あとは「テキストボックスの入力値でワークシートを更新する」の項で登場するコードと合体させれば完成ですが、もうひと手間加えましょう。
このまま単純に合体させただけのコードでは、ワークシートが更新されていなくてもブックの保存が実行されてしまいます。
ブックの保存はワークシートが更新された場合のみ実行したいので、ワークシートが更新されたことを検知するフラグを使って、更新されたかどうかをチェックできるようにしておきます。
コードの全体は、こんな感じになります。
Private Sub btnOK_Click() Dim myRange As Range Dim rngSearch As Range Dim blnChange As Boolean 'ワークシート変更フラグ blnChange = False If TextItem.Value <> "" Then With ActiveSheet Set myRange = .Range(.Cells(1, 1), .Cells(.Range("A1").End(xlDown).Row, 2)) 'テキストボックスの入力値から、ワークシート更新対象の行を検索 Set rngSearch = myRange.Find(What:=TextItem.Value, LookAt:=xlPart) If Not rngSearch Is Nothing Then 'テキストボックスに入力した値とワークシートのリストの値が異なっていた場合、ワークシートを更新 If TextRegion.Value <> .Cells(rngSearch.Row, rngSearch.Column + 1).Value Then .Cells(rngSearch.Row, rngSearch.Column + 1).Value = TextRegion.Value End If Else 'ワークシートに新規追加 .Cells(myRange.Rows.Count + 1, myRange.Columns(1).Column).Value = TextItem.Value .Cells(myRange.Rows.Count + 1, myRange.Columns(2).Column).Value = TextRegion.Value End If End With End If If blnChange = True Then 'ワークシートが更新された場合のみブックを保存する ThisWorkbook.Save End If End Sub
テキストボックスに入力した値をワークシートのリストに追加
では早速、上のコードを動かしてみましょう。
ユーザーフォームを開いて、ワークシートのリスト上に存在していない「品名」を入力して、「生産地」にも値を入力します。
そしてコマンドボタンを押すと…
この通り、ワークシート上のリスト最下行に、テキストボックスに入力した値が追加されていますね!
最後に
連載目次:【エクセルVBA】ユーザーフォームを使ってみよう!
ユーザーフォームは、ちょっとした画面なら作れてしまう、便利な機能です。
何となく敷居が高いように感じてしまいますが、順を追っていけば難しいことはありませんよ。
- 【エクセルVBA】ユーザーフォームを作ってみよう!VBEでフォームを挿入する方法
- 【エクセルVBA】Showメソッドでユーザーフォームをコマンドボタンから開いてみよう!
- 【エクセルVBA】コマンドボタンのClickイベントとUnloadステートメントでユーザーフォームを閉じる方法
- 【エクセルVBA】テキストボックスのプロパティで入力を便利にしよう
- 【エクセルVBA】テキストボックスでよく使う3つのイベントを使ってみよう
- 【エクセルVBA】Initializeイベントでワークシートの値をユーザーフォームの初期値にセットする
- 【エクセルVBA】AfterUpdateイベントでテキストボックス同士を連動させてみよう
- 【エクセルVBA】コマンドボタンのClickイベントでワークシートを更新してみよう
- 【エクセルVBA】ユーザーフォームのリストボックスを使ってみよう!
- 【エクセルVBA】リストボックスのRowSourceプロパティの値を変動させてみよう!
- 【エクセルVBA】AddItemメソッドでリストボックスに値を追加してみよう!
- 【エクセルVBA】リストボックスのListプロパティで複数列の値を更新する
- 【エクセルVBA】ユーザーフォームにコンボボックスを追加する方法
- 【エクセルVBA】コンボボックスに値をセットする2つの方法 RowSourceプロパティとAddItemメソッド