「エクセル日記」の作り方～その２～
拙作「エクセル日記」のレビューを行なっている。今回はユーザーフォームを見て行きたい、まずは日記入力フォームから。その前にフォームの名称だが、この頃の私はフォーム名を変えずにそのまま使っていた。日記入力フォームはしたがってUserForm1である。あとになって考えると「見にくいこと甚だしい」と言わねばならない。皆さんが作るときは、是非フォームに分かり易い名称を付けて頂きたい。まず日記入力フォームのInitialize()イベントだが、次のようになっている。
Private Sub UserForm_Initialize() ‘コンボボックスの表示範囲指定
Dim 最終行 As Integer
Dim 表示範囲 As String
最終行 = Worksheets(“顔”).Range(“A1”).Value + 2
表示範囲 = “顔!A2:A” & 最終行
UserForm1.ComboBox1.RowSource = 表示範囲
End Sub
私はExcelVBAを使い始めた初期の頃から変数を漢字にしている。この方が分かり易いからなのだが、よく言われるようにこれが原因でバグが起きるなどということは、ついぞ経験したことはない。もちろんAccessVBAでもVB.NETでも何ら問題はなかったと記憶している。ただしこれは完全に「好み」の問題だと思うので別に他人に薦めようとは思わない。
さて日記入力フォームのInitialize()の変数だが、「最終行」は顔シートのA1セルの「=COUNTA(A2:A1002)」に2を足した数字で、入力セルの最終行。「表示範囲」は、顔シートのA2セルから最終行までの範囲をComboBox1のRowSourceプロパティに代入している。
Private Sub CommandButton4_Click() ‘検索
Dim 選択行 As Long
Dim 選択範囲行 As Long
Dim 参照日付 As Variant
Dim 日付 As Date
If TextBox1 = “” Then
Label34.Caption = “日付が入っていません。”
TextBox1.SetFocus
Else
Label34.Caption = “”
Label33.Caption = “検索中です。”
Label34.Caption = “”
参照日付 = TextBox1.Value
For 選択範囲行 = 5 To 65536
日付 = Cells(選択範囲行, 3).Value
If 日付 = 参照日付 Then
選択行 = 選択範囲行
End If
Next 選択範囲行
If 選択行 <> 0 Then
TextBox45.Text = Cells(選択行, 6).Value
TextBox47.Text = Cells(選択行, 4).Text
TextBox48.Text = Cells(選択行, 2).Text
Label33.Caption = “入力できます。”
Cells(選択行, 3).Select ‘検索した行に跳ぶ。
TextBox45.SetFocus
Else
Label34.Caption = “入力した日付は存在しません。”
Label33.Caption = “”
TextBox1.SetFocus
End If
End If
End Sub
検索ボタンのClick()イベント。TextBox1（日付テキストボックス）が空欄の場合Label34のCaptionを”日付が入っていません。”と表示する。それ以外の場合「参照日付」にTextBox1.Valueの日付を代入する。「選択範囲行」は検索する範囲で、5行目から65536行目まで。その中の3列目（日付）の値を変数「日付」に代入する。「日付」と「参照日付」が一致するまで「選択範囲行」を検索する。一致したらループを抜けて、その行を「選択行」に代入する。一致する日付がない場合、例えばTextBox1.Valueに文字列が入力されたような場合、Label34のCaptionに “入力した日付は存在しません。”と表示する。ループを抜けたあとは日記ニュー力フォームの各コントロールに「選択行」の各列を表示する。
Private Sub CommandButton6_Click() ‘本日
Dim 選択行 As Long
Dim 選択範囲行 As Long
Dim 参照日付 As Date
Dim 日付 As Date
UserForm1.TextBox1.Value = Date
Label34.Caption = “本日を検索します。 ”
Label33.Caption = “検索中です。”
参照日付 = TextBox1.Value
For 選択範囲行 = 5 To 65536
日付 = Cells(選択範囲行, 3).Value
If 日付 = 参照日付 Then
選択行 = 選択範囲行
End If
Next 選択範囲行
If 選択行 <> 0 Then
Cells(選択行, 3).Select ‘本日の行に跳ぶ。
TextBox45.Text = Cells(選択行, 6).Text
TextBox48.Text = Cells(選択行, 2).Text
TextBox47.Text = Cells(選択行, 4).Text
Label34.Caption = “”
Label33.Caption = “入力できます。”
TextBox45.SetFocus
Else
MsgBox (” その日付は存在しません。”)
Label34.Caption = “”
Label33.Caption = “”
TextBox1.Text = “”
TextBox45.SetFocus
UserForm1.Hide
End If
ActiveSheet.Protect
Application.CutCopyMode = False
End Sub
本日ボタンのClick()イベント。まずTextBox1（日付テキストボックス）に本日の日付が入力される。参照日付にTextBox1.Valueすなわち本日を代入する。選択範囲行は、検索する範囲で5行目から65536行目まで。その中の3列目の値を変数「日付」に代入する。「日付」と「参照日付」が一致するまで「選択範囲行」を検索する。一致したらループを抜けてその行を選択行に入力する。
Private Sub CommandButton7_Click() ‘昨日へ
Dim 選択行 As Integer
Dim 参照範囲行 As Long
Dim 参照日付 As Date
Dim 参照元 As Date
If TextBox1.Text = “” Then
Label34.Caption = “日付を入力してから昨日へボタンを押してください。”
TextBox1.SetFocus
Else
Label34.Caption = “”
Label33.Caption = “検索中です。”
参照日付 = TextBox1.Text
For 参照範囲行 = 5 To 65536
参照元 = Cells(参照範囲行, 3).Value
If 参照元 = 参照日付 Then
選択行 = 参照範囲行
End If
Next 参照範囲行
If 選択行 <> 0 Then
Cells(選択行 – 1, 3).Select ‘昨日の行に跳ぶ。
TextBox45.Text = Cells(選択行 – 1, 6).Text
TextBox48.Text = Cells(選択行 – 1, 2).Text
TextBox47.Text = Cells(選択行 – 1, 4).Text
TextBox1.Text = Cells(選択行 – 1, 3).Text
Label34.Caption = “”
Label33.Caption = “入力できます。”
TextBox45.SetFocus
End If
End If
ActiveSheet.Protect
Application.CutCopyMode = False
End Sub
昨日へボタンのClick()イベント。TextBox1（日付テキストボックス）が空欄の場合Label34のCaptionを”日付を入力して検索ボタンを押してください。”と表示する。それ以外の場合「参照日付」にTextBox1.Valueの日付を代入する。選択範囲行は、検索する範囲で5行目から65536行目まで。その中の3列目の値を変数「日付」に代入する。「日付」と「参照日付」が一致するまで「選択範囲行」を検索する。一致したらループを抜けてその行を選択行に入力する。ループを抜けたあとは日記ニュー力フォームの各コントロールに「選択行」から1を引いた行の各列を表示する。
Private Sub CommandButton13_Click() ‘クリア
With UserForm1
.TextBox45.Text = “”
.TextBox47.Text = “”
.TextBox48.Text = “”
.ComboBox1.Text = “”
.Label33.Caption = “”
.Label34.Caption = “”
End With
End Sub
クリアボタンのClick()イベント。特に言うことはなし。
Private Sub CommandButton5_Click() ‘更新
Dim 選択行 As Long
Dim 選択範囲行 As Long
Dim 参照日付 As Variant
Dim 日付 As Date
Dim 応答 As Variant
If TextBox1.Text = “” Then
MsgBox ” 日付を入力して検索ボタンを押してください。”
TextBox1.SetFocus
Else
応答 = MsgBox(” データを更新します。よろしいですか？”, _
vbOKCancel, “データの更新”)
If 応答 = vbOK Then
Label33.Caption = “更新中です。”
参照日付 = TextBox1.Value
For 選択範囲行 = 5 To 65536
日付 = Cells(選択範囲行, 3).Value
If 日付 = 参照日付 Then
選択行 = 選択範囲行
End If
Next 選択範囲行
If 選択行 <> 0 Then
ThisWorkbook.Activate ‘当該ソフトを選択。
Worksheets(“日記”).Select
Cells(選択行, 3).Select ‘更新後その行に留まる。
ActiveSheet.Unprotect
Cells(選択行, 6).Value = Replace(TextBox45.Value, vbCr, “”)
Cells(選択行, 2).Value = Replace(TextBox48.Value, vbCr, “”)
Label33.Caption = “更新完了です。”
Label33.Caption = “”
TextBox1.Text = “”
TextBox45.Text = “”
TextBox47.Text = “”
TextBox48.Text = “”
ComboBox1.Text = “”
Label33.Caption = “”
Label34.Caption = “”
TextBox1.SetFocus
UserForm1.Hide
ActiveWorkbook.Save
MsgBox (“ 更新しました。 ”)
Else
Label34.Caption = “入力した日付は存在しません。”
Label33.Caption = “”
TextBox1.SetFocus
End If
If 応答 = vbCancel Then
Exit Sub
End If
End If
End If
ActiveSheet.Protect
Application.CutCopyMode = False
End Sub
更新ボタンのClick()イベント。TextBox1（日付テキストボックス）が空欄の場合Label34のCaptionを”日付を入力して検索ボタンを押してください。”と表示する。それ以外の場合” データを更新します。よろしいですか？”のMessageBoxを表示する。OKボタンが押されたときは「参照日付」にTextBox1.Valueの日付を代入する。選択範囲行は、検索する範囲で5行目から65536行目まで。その中の3列目の値を変数「日付」に代入する。「日付」と「参照日付」が一致するまで「選択範囲行」を検索する。一致したらループを抜けて「選択行」の2列目と6列目を書き換える。同時にコントロールを全てクリアする。最後にWorkSheetを保存する。
Private Sub CommandButton9_Click() ‘顔文字挿入
Me.TextBox45.SetFocus
Me.TextBox45.SelText = ComboBox1.Text
End Sub
SelTextプロパティを使うことによって、カーソル位置に文字列を挿入出来る。