webdevqa.jp.net

ExcelVBAユーザーフォーム-何かが変更されたときにSubを実行する

たくさんのテキストボックスを含むユーザーフォームがあります。これらのテキストボックスの値が変更されるたびに、サブルーチンAutoCalc()を呼び出して、テキストボックスの値に基づいて最終結果の値を再計算する必要があります。

約25個のボックスがあり、上記のサブルーチンを呼び出す各テキストボックスにChange()イベントを個別に追加したくありません。値が変更されるたびにAutoCalc()を呼び出す最も迅速で効率的な方法は何ですか?

7
Ashok

これは、クラスモジュールを使用して実現できます。次の例では、いくつかのテキストボックスを含むユーザーフォームがすでにあると想定します。

まず、VBAプロジェクトにクラスモジュールを作成します(clsTextBoxと呼びましょう。クラスモジュールの「Name」プロパティを必ず変更してください)。

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Private Sub MyTextBox_Change()
    AutoCalc() //call your AutoCalc sub / function whenever textbox changes
End Sub

次に、ユーザーフォームに次のコードを追加します。

Dim tbCollection As Collection

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control
    Dim obj As clsTextBox

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBox Then
                Set obj = New clsTextBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing

End Sub
15
Alex P

上記の回答が示唆するように、クラスの使用は、簡潔でエレガントな方法で多くのコントロールを処理するための優れた戦略です。

1)コントロールの数が動的でない限り、共通のユーザーフォームプライベートルーチンを呼び出して、1行で25のイベントを作成しても問題はありません。それは[〜#〜] kiss [〜#〜]哲学です。

2)一般的に、Changeイベントは、入力された各桁のすべての再計算を行うため、非常に厄介だと思います。 ExitイベントまたはBefore Updateイベントを使用してこれを行う方が賢明で中程度です。これは、値を決定するときにのみ再計算が行われるためです。たとえば、Google Instantは、ユーザーが質問を定義せずに、応答を返そうとしてリソースを消費するのを煩わしくします。

3)検証の問題がありました。 Changeイベントで間違ったキーを回避できることに同意しますが、データを検証する必要がある場合、ユーザーが入力を続行するかどうか、またはデータを検証する準備ができているかどうかを知ることはできません。

4)ChangeまたはExitイベントはユーザーにテキストフィールドの入力を強制しないため、フォームを終了しようとするときにシステムを再検証および再計算する必要があることに注意してください。キャンセルせずに。

次のコードは単純ですが、静的フォームには効果的です。

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub
.....
Private Sub TextBox25_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Function Valid
.....
End Function 

Private Sub AutoCalc(Canc As Variant)
If Not Valid() Then Canc=True
'  Calculation
End Sub

時間を節約することに夢中になっている場合は、コントロールに関連するイベントのコードをマスクに適合する形式で生成するために、汎用のVBAルーチンを作成できます。このコードはドラフトシートに含めることができ(コードを直接生成する方が安全です。一部のExcelバージョンではバグがあります)、フォームモジュールにコピーして貼り付けるよりも安全です。

 Sub GenerateEvent(Form As String, Mask As String, _
   Evento As String, Code As String)
 '  Form - Form name in active workbook
 '  Mark - String piece inside control name
 '  Evento - Event name to form procedure name
 '  Code   - Code line inside event
 Dim F As Object
 Dim I As Integer
 Dim L As Long
 Dim R As Range
 Dim Off As Long
 Set F = ThisWorkbook.VBProject.VBComponents(Form)
 Set R = ActiveCell   ' Destination code
 Off = 0
 For I = 0 To F.Designer.Controls.Count - 1
    If F.Designer.Controls(I).Name Like "*" & Mask & "*" Then
        R.Offset(Off, 0) = "Private Sub " & _
          F.Designer.Controls(I).Name & "_" & Evento & "()"
        R.Offset(Off + 1, 0) = "     " & Code
        R.Offset(Off + 2, 0) = "End Sub"
        Off = Off + 4
    End If
 Next I
 End Sub

 Sub Test()
 Call GenerateEvent("FServCons", "tDt", "Exit", _
    "Call AtuaCalc(Cancel)")
 End Sub
7
Paulo Buchsbaum

テキストボックスの変更に応答するクラスを作成する方法については、 this を参照してください。例はボタン用ですが、変更できます。ただし、TextboxコントロールにはExitイベントがないことに注意してください(このイベントは実際にはユーザーフォームの一部です)。そのため、実際にはChangeイベントを使用する必要があります。

6
Doug Glancy

共通のルーチンを使用して約48の異なるテキストボックスを検証したいという同様の問題があり、クラスモジュールのアプローチは面白そうに見えました(コードの重複行がはるかに少ない)。ただし、入力したすべての文字を検証する必要はなく、更新後にのみ確認したかったのです。また、入力したデータが無効な場合は、テキストボックスをクリアして同じテキストボックスにとどまり、ExitルーチンでCancel = Trueを使用する必要があります。これを数時間試しても、AfterUpdateイベントハンドラーとExitイベントハンドラーがトリガーされなかった後、その理由を発見しました。

次のようなクラスを作成する場合:

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set** Control(tb As MSForms.TextBox)

    Set MyTextBox = tb

End Property

次に、VBEオブジェクトブラウザに移動してMyTextBoxを選択すると、サポートされている列挙されたイベントにAfterUpdateまたはExitが含まれていないことがわかります。これらのイベントは、ユーザーフォームに移動してVBEオブジェクトブラウザーを使用し、TextBoxのインスタンスを確認すると利用できますが、TextBoxが含まれているコントロールから継承されているように見えます。 MSForms.TextBoxを使用して新しいクラスを定義する場合、これらのイベントは含まれません。これらのイベントハンドラーを手動で定義しようとすると、コンパイルされ、機能するように見えます(ただし、機能しません)。クラスオブジェクトのイベントハンドラになる代わりに、VBEオブジェクトブラウザの下の(一般)に表示され、実行されることのないプライベートサブルーチンになります。有効なイベントハンドラーを作成する唯一の方法は、VBEオブジェクトブラウザーでクラスオブジェクトを選択し、列挙されたイベントリストから目的のイベントを選択することです。

何時間も検索した後、プライベートクラス内で同様の継承モデルを構築する方法を示す参照を見つけることができなかったため、AfterUpdateとExitは作成されたクラスで使用可能なイベントとして表示されます。したがって、ユーザーフォームのテキストボックスごとに個別のイベントハンドラーを使用することをお勧めします(上記)が、AfterUpdateやExitを使用する場合に機能する唯一のアプローチである可能性があります。

1
user3164282

ただし、TextboxコントロールにはExitイベントがないことに注意してください(このイベントは実際にはユーザーフォームの一部です)。そのため、実際にはChangeイベントを使用する必要があります。

よくわかりません。おそらくこれは2007年に追加されたか、あるいは微妙な違いがわかりません。 TextBoxコントロールでExitイベントを使用します。コントロールからTabキーで移動するか、別のコントロールをマウスでクリックすると、Exitイベントがトリガーされます。

0
BiggerDon

ですから、フォーラムで私に与えられた最初の9行は、どこにあるのか思い出せません。しかし、私はそれを基に構築したので、コマンドボタンを使用して、このサブにリストされている変数を使用によって変更されたかどうかを再計算したいと思います。

<pre><code>'Private Sub txtWorked_Exit(ByVal Cancel As     MSForms.ReturnBoolean)
11 Dim OTRate       As Double
   OTRate = Me.txtHourlyRate * 1.5
If Me.txtWorked > 40 Then
   Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * 40, "$#,##0.00")
   Me.txtOvertime = Format((Me.txtWorked - 40) * OTRate, "$#,##0.00")
Else
   Me.txtOvertime.Value = "0"
   Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * Me.txtWorked.Value, "$#,##0.00")
End If
Dim Gross, W2, MASSTax, FICA, Medi, Total, Depends, Feds As Double
   Gross = CDbl(txtBonus.Value) + CDbl(txtBasePay.Value) +    CDbl(txtOvertime.Value)
   W2 = txtClaim * 19
   Me.txtGrossPay.Value = Format(Gross, "$#,##0.00")
   FICA = Gross * 0.062
   Me.txtFICA.Value = Format(FICA, "$#,##0.00")
   Medi = Gross * 0.0145
   Me.txtMedicare.Value = Format(Medi, "$#,##0.00")
   MASSTax = (Gross - (FICA + Medi) - (W2 + 66)) * 0.0545
If chkMassTax = True Then
   Me.txtMATax.Value = Format(MASSTax, "$#,##0.00")
Else: Me.txtMATax.Value = "0.00"
End If
If Me.txtClaim.Value = 1 Then
   Depends = 76.8

ElseIf Me.txtClaim.Value = 2 Then
   Depends = 153.8

ElseIf Me.txtClaim.Value = 3 Then
   Depends = 230.7
Else
   Depends = 0
End If
   If (Gross - Depends) < 765 Then
   Feds = ((((Gross - Depends) - 222) * 0.15) + 17.8)
   Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
ElseIf (Gross - Depends) > 764 Then
   Feds = ((((Gross - Depends) - 764) * 0.25) + 99.1)
   Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
Else:
   Feds = 0
End If
   Total = (txtMATax) + (FICA) + (Medi) + (txtAdditional) + (Feds)
   Me.txtTotal.Value = Format(Total, "$#,##0.00")
   Me.txtNetPay.Value = Format(Gross - Total, "$#,##0.00")

End Sub
Private Sub cmdReCalculate_Click()

End Sub'</pre></code>
0
GregNH

2クラスモジュール方式

イベントリスナーをユーザーフォームに追加する非常に簡単な方法を作成しました。さらに、MouseOverやMouseOutなどのイベントを追加します。 (ホバー効果を行うためのクール)

動作するためにインポートする必要がある2つのクラスモジュールは、私のGithubページにあります VBA Userform Event Listeners


ユーザーフォームでコードを使用する方法

始めるのは簡単です。クラスモジュールを追加したら、以下のサンプルコードをユーザーフォームに追加するだけです。

Private WithEvents Emitter As EventListnerEmitter

Private Sub UserForm_Activate()
   Set Emitter = New EventListnerEmitter
   Emitter.AddEventListnerAll Me
End Sub

それだけです!これで、さまざまなイベントのリッスンを開始できます。


メインイベントリスナー

メインイベントEmittedEventがあります。これにより、イベントが発生するコントロールとイベント名が渡されます。したがって、すべてのイベントはこのイベントハンドラーを通過します。

Private Sub Emitter_EmittedEvent(Control As Object, ByVal EventName As String, EventValue As Variant)

    If TypeName(Control) = "Textbox" And EventName = "Change" Then
        'DO WHATEVER
    End If

End Sub

個々のイベントリスナー

特定のイベントを聞くこともできます。 oこの場合、変更イベント。

Private Sub Emitter_Change(Control As Object)

    If TypeName(Control) = "Textbox" Then
          'DO WHATEVER
    End If

End Sub

まだすべてのイベントがキャプチャされているわけではないので、私の Github ページをチェックして、プルリクエストを行ってください。

0
Robert Todar