エクセルVBAでテキストボックスの金額表示について

質問

テキストボックスに金額を入力する際、1,000のように表示したいと思っています。
ただ、テキストボックスが２０個あるので、一括設定する方法はありませんか。

回答No.3

real beatin（@realbeatin） · ベストアンサー

No.1 補足、拝見しました。
No.1の時点でUserForm版も考えてはいたので、
少しだけ書き足したものを提示します。

ちょっとイクスキューズ。
ClassモジュールでTextBoxのイベントを
トラップする方法について。
UserFormモジュールでは可能な
_Exitイベント
が、Classモジュールでは扱えません。
方法はあるのですが、
すべてのバージョンに汎用的な、環境に左右されない
ものを考えることが、私には、出来ませんでしたので、
　TextBoxに対するキーストローク
　TextBoxに対するマウスボタン操作
を基本のトリガーにして、フォーマット処理します。
TextBox1~20に対してTabやEnterでの連続入力を
中心に考えているようでしたら、ほぼ、
ストレスなく機能させられると考えています。
それでも、フォーマットされない場合があれば、
TextBox1~20の何れかをクリックするか、
UserForm（の余白）をクリックすれば、
直前に入力中だったTextBoxをフォーマットします。
CommandButton1（があれば仮の話）を押した場合に
機能させる場合の例をUserFormモジュールに
オプションとして添えておきましたので、
必要な場合は工夫してみて下さい。
数値のフォーマットに関しては、
IsNumeric関数とFormat関数の機能をそのまま
活かす方が入力作業への親和性が高いと判断しましたので、
余計なことはしていません。
結構な数の意地悪テストをして動作確認していますが、
「いいな」「使える関数だな」と思ったのは、
　123
とタイプした時に
「あ、いけね、マイナスやん」と思ってもそのまま
　123456-
と最後まで数値打ってから、右端に符号を打つと、
　-123,456
に整形してくれるとか、
誤って、
　0
から打ち始めても、そのまま、
　012345
と打ってしまえば、
　12,345
とか、正しい数値文字列として解釈し直してくれることです。

　標準モジュール
　UserForm1モジュール
　Class1モジュール
の３ヶ所、下記のコードを適用してください。
コードネームとしての、
　UserForm1
　Class1
これらに齟齬が生じないよう必要なら、
下記コード側で置換処理してください。

今回は、
UserFormを読み込むタイミングで機能を起動し、
UserFormを破棄したら（閉じたら）機能も終了する
ように書いています。（再起動は不要です）

' ' 〓 Module1 (標準モジュール) 〓
Option Explicit
Public colClass As New Collection
Public oControl As MSForms.Control
Public ptrCls As Long
Private Declare PtrSafe Sub Sleep Lib "KERNEL32.dll" (ByVal dwMilliseconds As Long)
' ' //
Sub ShowUFrm()
　　UserForm1.Show vbModeless　'opt)　UserForm1.Show
End Sub
Sub SetEvTxtBxes(frm As UserForm)
　　If colClass.Count Then Exit Sub
　　For Each oControl In frm.Controls
　　　　If TypeName(oControl) = "TextBox" Then　'opt)　If oControl.Name Like "TextBox*" Then
　　　　　　ptrCls = ptrCls + 1
　　　　　　colClass.Add New Class1, CStr(ptrCls)
　　　　　　Sleep 10
'　　　　ElseIf TypeName(oControl) <> "Label" Then　'opt) TextBox以外のTabStopを解除する場合
'　　　　　　If oControl.TabStop Then oControl.TabStop = False　'opt)
　　　　End If
　　Next
　　ptrCls = 0
　　Set oControl = Nothing
End Sub
Private Sub Inquire()
　　If ptrCls Then colClass(CStr(ptrCls)).FormatNum
End Sub
' ' //
' ' 〓　〓
' ' 〓〓 UserForm1 (ユーザーフォームモジュール) 〓〓
Option Explicit
' ' //
Private Sub UserForm_Initialize()
　　SetEvTxtBxes Me
End Sub
Private Sub UserForm_Click()
　　Application.Run "Inquire"　'　未フォーマット問合せ
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
　　Set colClass = Nothing　'　必須　！！
　　ptrCls = 0
End Sub
' ' // TextBox 以外の コントロール（Label除く）にフォーカスが移るタイミングの共通処理
'Private Sub CommandButton1_Click()　'ex)コマンドボタンを押した直前に編集中だったテキストボックスを処理
'　　Application.Run "Inquire"　'　未フォーマット問合せ
'End Sub
' ' //
' ' 〓〓　〓〓
' ' 〓〓〓 Class1 (クラスモジュール) 〓〓〓
Option Explicit
Private WithEvents evTextBox As MSForms.TextBox
Private nIndex As Long
Private escEv As Boolean
' ' //
Private Sub Class_Initialize()
　　Set evTextBox = oControl
　　Let Index = ptrCls
End Sub
Private Sub evTextBox_Change()
　　If escEv Then Exit Sub
' '　　金額を入力したいボックスが１~２０あります。
' '　　２０以降は単純な入力だけ用に使用しています。
　　If Index > 20 Then Exit Sub　'　TextBox21 以降はフォーマット対象外にする。
　　ptrCls = Index
End Sub
Private Sub evTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
　　Select Case KeyCode
　　Case vbKeyTab, vbKeyReturn, vbKeyUp, vbKeyDown
　　　　If ptrCls Then FormatNum
　　End Select
End Sub
Private Sub evTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
　　Application.Run "Inquire"　'　未フォーマット問合せ　'　右ボタンは２回イベント通るが問題ない。中ボタンには無反応。
End Sub
' ' //
Private Function EvalNum() As Boolean
Dim v
　　v = evTextBox.Value
　　If IsNumeric(v) = False Then Exit Function
　　EvalNum = True
End Function
Public Function FormatNum()
　　ptrCls = 0&
　　If EvalNum = False Then Exit Function
　　escEv = True
　　evTextBox.Text = Format(evTextBox.Value, "#,##0")
　　escEv = False
End Function
' ' //
Public Property Let Index(n As Long)
　　nIndex = n
End Property
Public Property Get Index() As Long
　　Index = nIndex
End Property
' ' //
' ' 〓〓〓　〓〓〓

お礼

早くて、びっくりしました。
これから、試してみます。ありがとうございました。

回答No.2

imogasi

下記ＷＥＢ時事を参考に。
https://okwave.jp/qa/q737392.html
ーー
ＵｓｅｒＦｏｒｍ1を挿入する。そこへＴｅｘｔｂｏｘを１つ貼り付ける。（ＴｅｘｔＢｏｘ１です）
そのTextBox1_KeyDownイベントに
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
If KeyCode <> 13 Then Exit Sub
MsgBox "Ente>を押しました。"
tx = UserForm1.TextBox1.Text
TextBox1.Text = Format(Val(tx), "\\#,###")
End Sub
Sub/ユーザーフォームの実行で、ユーザーフォームやテキストボックス
が表示され、テキストボックスに数字を入れてＥＮＴＥＲキーを押す。
数字の３桁ごとにカンマが入る。また先頭に円記号がつく。
注意
上記のＦｏｒｍａｔの中の、//は￥円マーク文字（半角）を２つ重ねてください。
これで数字の先頭に円記号（１つ）がつきます。
納得した後は、ＭｓｇＢｏｘ行は削除。

回答No.1

real beatin（@realbeatin）

こんにちは。

シートの設計として、
本来は入力用のインターフェイスであるテキストボックスに対して、
表示上のフォーマットを操作するのは、やや珍しい感じはしますね。
特にシート上のテキストボックスには
入力を確定させたタイミング_Enterイベントが無効なので、
_Changeイベントに頼らざるを得ず、１桁打鍵する度に書き直す仕様
が避けられなくて、なんか忙しいです。
これが気持ちいいと感じる人も結構いるのは解るんですけれどもね。
一応、打鍵１に対して、桁送りが２、という挙動に違和感を覚えるユーザーも
少なくないでしょうから、ニーズを的確に捉えた上で
必要の判断を強化してから導入してみて下さい。

必要最低限の簡単なサンプルとして、
標準モジュールとクラスモジュールの組合わせ例です。
この仕組みは、
ブックを開いた時にAuto_Openが実行されて、
SetEvTxtBxesの処理によって有効になります。
ただ、ブック編集中にVBProjectが終了
　｜・デザインモードでコントロールの追加／編集をした
　｜・VBA実行時エラーに対してプロジェクトの終了を選んだ
　｜・デバッグ中にプロジェクトのリセットをした、、、等々
された場合には、機能しなくなりますから、
想定できる範囲で復旧（SetEvTxtBxes実行）を自動化する処理を
（、、、シートのイベントとかで、、、）
適宜用意されることをお奨めしておきます。

サンプルでは、
　Sheets("Sheet1")上のテキストボックスすべて
を対象にしています。

　標準モジュールを挿入、編集（シート名、要指定）
　クラスモジュール（サンプルではClass1）を挿入、編集
　1> 保存して閉じて開き直す
　または
　2> Private Sub Auto_Open() を実行
といった手順で動作確認してください。
当然ですが、事前に指定のシートに
　ActiveXコントロールのテキストボックス
を配置しておいてください。
テキストボックスの数について理論上の制約はありません。

' ' // Module1 (標準モジュール)
Option Explicit

Private colTextBoxes As New Collection

Private Sub SetEvTxtBxes()
Dim o As OLEObject
Dim cn As Long
　　If colTextBoxes.Count Then Exit Sub
　　For Each o In Sheets("Sheet1").OLEObjects
　　　　If o.progID = "Forms.TextBox.1" Then
　　　　　　cn = cn + 1
　　　　　　colTextBoxes.Add New Class1, CStr(cn)
　　　　　　Set colTextBoxes(CStr(cn)).TextBox = o.Object
　　　　End If
　　Next
'Debug.Print colTextBoxes.Count
End Sub

Private Sub Auto_Open()
　　SetEvTxtBxes
End Sub
' ' //

' ' // Class1 (クラスモジュール)
Option Explicit

Private WithEvents evTextBox As MSForms.TextBox
Private escEv As Boolean

Private Sub evTextBox_Change()
　　If escEv Then Exit Sub
　　If evTextBox.Text <> "" Then
　　　　If IsNumeric(evTextBox.Text) Then
　　　　　　escEv = True
　　　　　　evTextBox.Text = Format(-CVar(-evTextBox), "#,###,###,###,###")
　　　　　　escEv = False
　　　　End If
　　End If
End Sub

Public Property Set TextBox(o As MSForms.TextBox)
　　Set evTextBox = o
End Property
' ' //

今回は「入力する際」ということでしたが、
他のやり方として、
「入力時にEnterキーやTABキーを押せば」
のようなことでしたら、出来なくもないです。
個々のテキストボックスで入力が確定したら、とか、
テキストボックスからフォーカスが外れたら、みたいなのは、
ユーザーフォームを使えば可能です。

以上、ご参考まで。

補足

早々と回答を頂き、ありがとうございました。
私の質問の記述が間違っていました。
テキストボックスは、ユーザーフォームのテキストボックスです。
金額を入力したいボックスが１~２０あります。
20以降は単純な入力だけ用に使用しています。
金額部分について、再考していただけると幸いです。