【エクセル】複数のTextBoxに共通するChangeイベントを簡潔に書きたい

UserForm1上にText◯◯1～10、Text△△1～10、Text□□1～10などTextで始まるオブジェクト名の100個以上のTextBoxがあります。
これらのうちのいずれかのTextBoxの値が変更(消去)されたり、新たに入力された場合に

Me.TextKoushin.Value = "未更新"
Me.TextKoushin.BackColor = RGB(255, 0, 0)
Me.TextKoushin.ForeColor = RGB(255, 255, 255)

というChegeイベント全てのTextBoxに書くことなくコントロールすることは出来ますでしょうか？
この際、TextKoushinのオブジェクト名にもTextと入っているのがまずければTxtKoushinと変更します。

また、UserFormは1～4までありますが、
適用したいのはUserForm1のみです。

よろしくお願いいたします。

質問者からの補足コメント

• 文字数の関係でお礼に補足内容を記載させていただきました。
  よろしくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2016/12/10 19:56

• ご回答有難うございます。
  
  前提条件で書かれていることについて追記させていただきます。
  
  ほとんどのTextBoxが、Text◯◯1～Text◯◯10、Text△△1～Text△△10という形ですが、
  TextBirthdayやTextKinmusakiなど1人の顧客において1つしか必要のないデータなどは1～10という設定が無いTextBoxが複数個あります。
  
  この場合、そのTextBirthdayやTextKinmusakiなどにはそれぞれにChangeイベントとして
  Me.TextKoushin.Value = "未更新"
  Me.TextKoushin.BackColor = RGB(255, 0, 0)
  Me.TextKoushin.ForeColor = RGB(255, 255, 255)
  を記載すれば良いという理解でよろしいでしょうか？
  
  よろしくお願いいたします。

  No.3の回答に寄せられた補足コメントです。 補足日時：2016/12/10 21:02

No.3 ベストアンサー 回答者： tatsu99 回答日時： 2016/12/10 20:28

1)クラスモジュール(Class1)を追加して、以下のコードを記入してください。

-------------------------------
Option Explicit

Private WithEvents myText As MSForms.TextBox
Private myTextBox As String
Public Sub S_setText(NewText As MSForms.TextBox, name As String)
Set myText = NewText
myTextBox = name
End Sub

Private Sub myText_Change()
UserForm1.TextKoushin.Value = "未更新"
UserForm1.TextKoushin.BackColor = RGB(255, 0, 0)
UserForm1.TextKoushin.ForeColor = RGB(255, 255, 255)
End Sub
---------------------------------------------

2)UserForm1のコードへ以下のコードを追加してください。
----------------------------------------------------
Option Explicit
Private myTextArray() As New Class1

Private Sub UserForm_Initialize()
Dim i As Long
Dim j As Long
Dim ix As Long
Dim textBoxName As Variant
textBoxName = Array("TextBoxAA", "TextBoxBB")
ReDim myTextArray(10 * (UBound(textBoxName) + 1) - 1)
For i = 0 To UBound(textBoxName)
For j = 1 To 10
ix = 10 * i + j
Call myTextArray(ix - 1).S_setText(Me(textBoxName(i) & j), textBoxName(i) & j)
Next
Next
End Sub
----------------------------------------------------------
上記は、テキストボックスが、
TextBoxAA1～TextBoxAA10
TextBoxBB1～TextBoxBB10
の例です。
textBoxName = Array("TextBoxAA", "TextBoxBB")
の行へ"TextBox○○"を必要なぶん追加してください。
TextBox○○はTextBox○○1～TextBox○○10まであることが前提です。
以上で完了です。

尚、質問でも行いましたが、
TextKoushinを"未更新"から元の状態に戻す機能はありませんので、その旨ご了承ください。

この回答へのお礼

レスポンスの良いご回答を本当にありがとうございます。

実装は明日にしようと思っていますが、
その前に再度確認でお願いします。

Dim textBoxName As Variant
textBoxName = Array("TextBoxAA", "TextBoxBB")
ReDim myTextArray(10 * (UBound(textBoxName) + 1) - 1)
For i = 0 To UBound(textBoxName)
For j = 1 To 10
ix = 10 * i + j
Call myTextArray(ix - 1).S_setText(Me(textBoxName(i) & j), textBoxName(i) & j)

この部分にある、オブジェクト名"TextBoxAA", "TextBoxBB"ですが、実際は"TextAA", "TextBB"です。
なので「Box」は入りません。
その次の行以下の、 小文字ｔで始まるtextBoxと記載されているUBound(textBoxName)、textBoxName(i)については、
UBound(textName)、textName(i)という形で「Box」を省略する訳では無いという解釈でよろしいでしょうか？

お礼日時：2016/12/10 22:18

No.5 回答者： tatsu99 回答日時： 2016/12/10 22:40

>この部分にある、オブジェクト名"TextBoxAA", "TextBoxBB"ですが、実際は"TextAA", "TextBB"です。

なので「Box」は入りません。
>その次の行以下の、 小文字ｔで始まるtextBoxと記載されているUBound(textBoxName)、textBoxName(i)については、UBound(textName)、textName(i)という形で「Box」を省略する訳では無いという解釈でよろしいでしょうか？

失礼しました。Text○○でしたね。見落としました。
はい、「小文字ｔで始まるtextBoxと記載されているUBound(textBoxName)、textBoxName(i)については、UBound(textName)、textName(i)という形で「Box」を省略する訳では無い」という解釈で良いです。

変更するのは、
textBoxName = Array("TextBoxAA", "TextBoxBB")
の行だけです。ほかは、変更不要です。
今回の例では、TextAA1～TextAA10,TextBB1～TextBB10なので、
textBoxName = Array("TextAA", "TextBB")
が正しいことになります。

この回答へのお礼

ありがとうございます。
承知いたしました！
明日実装してみて状況を報告させていただきます。
今日はこれにて失礼致します。
それではおやすみなさいませ。

お礼日時：2016/12/10 23:01

No.4 回答者： tatsu99 回答日時： 2016/12/10 21:56

>ほとんどのTextBoxが、Text◯◯1～Text◯◯10、Text△△1～Text△△10という形ですが、

TextBirthdayやTextKinmusakiなど1人の顧客において1つしか必要のないデータなどは1～10という設定が無いTextBoxが複数個あります。
>この場合、そのTextBirthdayやTextKinmusakiなどにはそれぞれにChangeイベントとして
>Me.TextKoushin.Value = "未更新"
>Me.TextKoushin.BackColor = RGB(255, 0, 0)
>Me.TextKoushin.ForeColor = RGB(255, 255, 255)
>を記載すれば良いという理解でよろしいでしょうか？

はい、それで良いです。
強引にUserForm_Initializeに組み込むことも可能ですが、
そのようなことをするよりは、個別にChangeイベント
を登録したほうが判りやすいかと考えます。


もし、UserForm_Initializeへ強引に組み込むなら
以下のようになります。
TextBirthdayとTextKinmusakiを組み込む場合の例です。
'追加開始と'追加終了ではさまれた部分が、追加した処理です。
-------------------------------------------------
Private Sub UserForm_Initialize()
Dim i As Long
Dim j As Long
Dim ix As Long
Dim textBoxName As Variant
textBoxName = Array("TextBoxAA", "TextBoxBB")
ReDim myTextArray(10 * (UBound(textBoxName) + 1) - 1)
For i = 0 To UBound(textBoxName)
For j = 1 To 10
ix = 10 * i + j
Call myTextArray(ix - 1).S_setText(Me(textBoxName(i) & j), textBoxName(i) & j)
Next
Next
'追加開始
Dim last As Long
textBoxName = Array("TextBirthday", "TextKinmusaki")
last = UBound(myTextArray) + 1
ReDim Preserve myTextArray(last + UBound(textBoxName))
For i = 0 To UBound(textBoxName)
Call myTextArray(last + i).S_setText(Me(textBoxName(i) & ""), textBoxName(i) & "")
Next
'追加終了
End Sub
-----------------------------------------------

どちらか、好きなほうを選んでください。

No.2 回答者： tatsu99 回答日時： 2016/12/10 18:25

No1です。

http://ateitexe.com/excelvba-control-change-event/
の指示に従い、chengeイベントをまとめて処理することは、できたのですが、

>これらのうちのいずれかのTextBoxの値が変更(消去)されたり、新たに入力された場合に
>Me.TextKoushin.Value = "未更新"
>Me.TextKoushin.BackColor = RGB(255, 0, 0)
>Me.TextKoushin.ForeColor = RGB(255, 255, 255)
>というChegeイベント全てのTextBoxに書くことなくコントロールすることは出来ますでしょうか？
>この際、TextKoushinのオブジェクト名にもTextと入っているのがまずければTxtKoushinと変更します。

上記のことで、いくつか質問があります。
１）TextKoushinはテキストボックスですか。
２）UserForm1のテキストボックス（TextKoushin）の表示内容、表示色、及び背景を上記のように変えれば
よいのでしょうか。
３）TextKoushinを上記のように変えた後、もとに戻すのは、ここでは考慮しなくて良いのですね。
（なにかほかの処理で元に戻していると理解して良いですね）

この回答へのお礼

こんばんは。

ご回答有難うございます。
ご質問について返信致します。

１）TextKoushinはTextBoxです。
２）仰るとおりです。
３）KoushinButtonというCommandButtonを配置して下記のような記載をしております。
これと関連させるために今回の質問をさせていただいた次第です。

Private Sub KoushinButton_Click()
Dim SerchKey As String
Dim SerchArea As Range
SearchKey = Me.TextBukkenNumber.Value
If SearchKey = "" Or SearchKey = "False" Then Exit Sub
Set SearchArea = Sheets("データベース").Range("A:A")
Set FoundCell = SearchArea.Find( _
What:=SearchKey, _
SearchOrder:=xlByRows, _
LookAt:=xlWhole, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "該当するデータはありません", vbCritical
Else
With FoundCell
Dim TextKinyubi As Date
Me.TextKinyubi = Format(Now, "ggge年mm月dd日")
.Offset(0, 1).Value = Me.Combokokyakumei.Value
.Offset(0, 2).Value = Me.TextKinmusaki.Value
（以下100行ほど省略）
End With
End If
Set SearchArea = Nothing
Me.TextHozon.Value = "更新済"
Me.TextHozon.BackColor = RGB(0, 0, 255)
Me.TextHozon.ForeColor = RGB(255, 255, 255)
ActiveWorkbook.Save
MsgBox "データ更新と上書き保存が完了しました。", vbInformation, "更新と上書き保存"
End Sub
よろしくお願いいたします。

お礼日時：2016/12/10 19:54

No.1 回答者： tatsu99 回答日時： 2016/12/08 12:10

下記URLにchengeイベントをまとめて処理する方法が記載されています。

http://ateitexe.com/excelvba-control-change-event/