【エクセルVBA】コマンドボタンのClickイベントでワークシートを更新してみよう

 

ユーザーフォーム,ワークシート更新皆様こんにちは、ノグチです。

エクセルのユーザーフォームを作って、使ってみるまでを目標に、連載記事でご紹介しています。

前回の記事では、テキストボックスに入力した値からワークシートを検索して、関連する値をユーザーフォーム上の別のテキストボックスに表示させる方法をご紹介しました。

【エクセルVBA】AfterUpdateイベントでテキストボックス同士を連動させてみよう
エクセルユーザーフォーム上の複数のテキストボックスを、AfterUpdateイベント内で連動させる方法をご紹介しています。テキストボックスに入力された値から、ワークシート上のリストを検索して、検索結果を別のテキストボックスに表示させるという、マスタ管理画面や、検索画面などで使える方法です。
tonari-it.com
2019.12.06

今回は、ユーザーフォームのコマンドボタンを押したとき、ワークシートの値をテキストボックスに入力されている値で更新する方法をご紹介します！

スポンサーリンク

前回までのおさらい

これまでに作成したユーザーフォームがこちら。

ユーザーフォーム,オブジェクト名

MyFormというフォーム上に、「品名」「生産地」とラベルを付けたテキストボックス２つと、コマンドボタンを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

今回は、こちらのコードに手を加えて、ユーザーフォーム上のコマンドボタンをクリックしたときに、

  1. ワークシートのリスト上に存在している値がテキストボックス「品名」に入力されていて、「生産地」の値がリストとテキストボックスで異なる場合、テキストボックスの値でワークシートを更新する
  2. テキストボックス「品名」に入力された値が、ワークシートのリスト上に存在していなかった場合、テキストボックス「品名」「生産地」の値をワークシートのリスト最下行に追加する
  3. ワークシートに変更があったら、ブックを保存する

の3つの処理をさせてみましょう。

テキストボックスの入力値でワークシートを更新する

まずは、テキストボックス「品名」の値がワークシートのリスト上に存在していた場合です。

コードの中でやるべきことは、

  1. 「品名」に入力された値がワークシートのリストの何行目にあるかを検索
  2. ヒットした「品名」と同じ行の、隣の列にあるセルの値を、テキストボックス「生産地」の入力値で更新
  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

早速こちらのコードを実行してみましょう。

まず、ユーザーフォームのテキストボックス「品名」に「りんご」を入力します。

ユーザーフォーム,品名,りんご

ワークシートのリスト上から、「りんご」と紐付いた「長野」という「生産地」の値がテキストボックス「生産地」に表示されました。

テキストボックス、品名、変更、りんご

ではテキストボックス「生産地」の値を変更して、コマンドボタンをクリックすると…

テキストボックス,ワークシート更新,りんご
この通り、テキストボックスに入力した値でワークシートが更新されましたね！

新しいレコードをワークシートに追加する

ここまでで、既存の「品名」と紐づいた「生産地」を更新することができました。

今度は、新しい「品名」がテキストボックスに入力された場合、ワークシートの最下行に追加する方法を考えてみましょう。

コードの中で必要な処理は、
  1. 「品名」に入力された値がワークシートに存在するか検索
  2. 存在していなかった場合、ワークシートのリスト最下行に、テキストボックス「品名」「生産地」の値を追加
  3. ブックを保存

の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】ユーザーフォームを使ってみよう！

ユーザーフォームは、ちょっとした画面なら作れてしまう、便利な機能です。

何となく敷居が高いように感じてしまいますが、順を追っていけば難しいことはありませんよ。

  1. 【エクセルVBA】ユーザーフォームを作ってみよう！VBEでフォームを挿入する方法
  2. 【エクセルVBA】Showメソッドでユーザーフォームをコマンドボタンから開いてみよう！
  3. 【エクセルVBA】コマンドボタンのClickイベントとUnloadステートメントでユーザーフォームを閉じる方法
  4. 【エクセルVBA】テキストボックスのプロパティで入力を便利にしよう
  5. 【エクセルVBA】テキストボックスでよく使う3つのイベントを使ってみよう
  6. 【エクセルVBA】Initializeイベントでワークシートの値をユーザーフォームの初期値にセットする
  7. 【エクセルVBA】AfterUpdateイベントでテキストボックス同士を連動させてみよう
  8. 【エクセルVBA】コマンドボタンのClickイベントでワークシートを更新してみよう
  9. 【エクセルVBA】ユーザーフォームのリストボックスを使ってみよう！
  10. 【エクセルVBA】リストボックスのRowSourceプロパティの値を変動させてみよう！
  11. 【エクセルVBA】AddItemメソッドでリストボックスに値を追加してみよう！
  12. 【エクセルVBA】リストボックスのListプロパティで複数列の値を更新する
  13. 【エクセルVBA】ユーザーフォームにコンボボックスを追加する方法
  14. 【エクセルVBA】コンボボックスに値をセットする2つの方法 RowSourceプロパティとAddItemメソッド

 

タイトルとURLをコピーしました