エクセルVBAでユーザーフォームのテキストボックス

質問

ユーザーフォーム上にテキストボックスを8個設置しました。
そこに数値が入力された場合は自動的にカンマ区切りが表示されるように以下のコードを書きました。
これで希望の動きはしてくれるのですが、このやりかたは正しいでしょうか？
また、テキストボックスは8個あるので、それぞれに下記のように8通りコードをかかなければいけないのでしょうか？ご教示ください。

Private Sub TextBox1_Change()
　　If IsNumeric(TextBox1.Value) Then
Application.EnableEvents=False
　　　　TextBox1.Text = Format(TextBox1.Value, "#,##0")
Application.EnableEvents=True
　　End If
End Sub

途中（2～7）略

Private Sub TextBox8_Change()
　　If IsNumeric(TextBox8.Value) Then
Application.EnableEvents=False
　　　　TextBox8.Text = Format(TextBox8.Value, "#,##0")
Application.EnableEvents=True
　　End If
End Sub

回答No.2

kkkkkm · ベストアンサー

No1の補足です。

一応参考までにNo1のリンク先のコードを利用して以下のようにすると
Private Sub TextBox1_Change()
などのTextBoxそれぞれのコードはすべて不要になります。

クラスを作成するとClass1ができるので

そこに

Private WithEvents Target As MSForms.TextBox

Public Sub SetCtrl(new_ctrl As MSForms.TextBox)
Set Target = new_ctrl
End Sub

Private Sub Target_Change()
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Text = Format(Target.Value, "#,##0")
Application.EnableEvents = True
End If
End Sub

ユーザーフォームのコードに

Private ctrl(1 To 8) As New Class1

Private Sub UserForm_Initialize()
Dim i As Integer
For i = LBound(ctrl) To UBound(ctrl)
ctrl(i).SetCtrl Me("TextBox" & i)
Next
End Sub

お礼

すごいです！！
クラスって初めて使ってみましたが丁寧にご教示いただいたのでうまくいきました！感謝感激です。
もう一つだけ教えていただけますか？
実はユーザーフォームはもう1つあり、そちらのテキストボックスは3個なんです。その場合、Userform2のほうにも
ユーザーフォームのコードに
Private ctrl(1 To 3) As New Class1　と宣言して
Private Sub UserForm_Initialize()
に
For i = LBound(ctrl) To UBound(ctrl)
ctrl(i).SetCtrl Me("TextBox" & i)
Next
とすればいいのでしょうか？

回答No.5

kkkkkm

おまけです。

既に実装しているかもしれませんが、数値だけしか入力できないようにするには
以下の方法が色々なところで紹介されてました。

クラスでの例です

Private Sub Target_Change()
Application.EnableEvents = False
If IsNumeric(Target.Value) Then
Target.Text = Format(Target.Value, "#,##0")
ElseIf Target.Value <> "" Then
Target.Text = Left(Target.Value, Len(Target.Value) - 1) 'ここです
End If
Application.EnableEvents = True
End Sub

もしくは　上記の
ElseIf Target.Value <> "" Then
Target.Text = Left(Target.Value, Len(Target.Value) - 1)
を除いて

クラスに以下を追加

Private Sub Target_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" Then
KeyAscii = 0
End If
End Sub

お礼

ご親切にありがとうございました。
これからもよろしくお願いいたします。

回答No.4

imogasi

本当は＃１のご回答がよいと思う。
ーー
だだし、クラスの考えの習得がすぐとはいかないと思うので下記はどうですか。
ユーザーフォームに８個のテキストボックスとコマンドボタン１つ配置。
CommandButton1_Clickイベントに
ーー
Private Sub CommandButton1_Click()
For Each c In Controls
If Left(c.Name, 7) = "TextBox" And IsNumeric(c.Value) Then

'MsgBox c.Name
c.Text = Format(c.Value, "#,##0")
c.TextAlign = fmTextAlignRight

End If
Next c
End Sub
ーー
テキストボックスにデータ（数値）を入れて、コマンドボタンをクリック。
（１）３桁ごと位取りのカンマをいれる
（２）右詰め表示
で、各々のテキストボックスの値を表示。
質問者のニーズと違う・ズレる、かな

お礼

ありがとうございます。
別ボタン方式ですね。これも役に立ちそうです。

回答No.3

kkkkkm

> ユーザーフォームのコードに
> Private ctrl(1 To 3) As New Class1　と宣言して
> Private Sub UserForm_Initialize()
> に
> For i = LBound(ctrl) To UBound(ctrl)
> ctrl(i).SetCtrl Me("TextBox" & i)
> Next
> とすればいいのでしょうか？

はい、不安でしたらctrlをそれぞれのフォームに合わせてctrl1,ctrl2と別にしておいてもいいです。
クラスはかなり単純にいうとひな形みたいなもので、ひな形を好きな名前でコピーして使う感じです。
(No1の場合は同じコードを共有してる感じ)

お礼

ありがとうございます。とてもよくわかりました。
助かりました！！

回答No.1

kkkkkm

> それぞれに下記のように8通りコードをかかなければいけないのでしょうか？

いちおう簡単な方法ですが

Private Sub TextBox1_Change()
Call Conv("TextBox1")
End Sub

Private Sub TextBox2_Change()
Call Conv("TextBox2")
End Sub

以下TextBoxの数だけ記載して

中身を一個だけ記載します。
Private Function Conv(ByVal TextBoxName As String)
If IsNumeric(Me.Controls(TextBoxName).Value) Then
Application.EnableEvents = False
Me.Controls(TextBoxName).Text = Format(Me.Controls(TextBoxName).Value, "#,##0")
Application.EnableEvents = True
End If
End Function

クラスを使って新しいイベントを作成する方法もありますが
ExcelVBAでControlオブジェクトのChangeイベントを一括制御する方法
https://ateitexe.com/excelvba-control-change-event/

たしか、以前にクラスは苦手のようなことを書いていたのでリンクだけ貼っておきます。

お礼

早速ありがとうございます。
たしかにこれでだいぶ簡略化できますね。
勉強になりました。