CODEWITHSUNDEEP
excelvbauser-interfacems-access
LazyBear
LazyBear

Reputation: 353

Use a button click event handler within a different button click event in VBA

Is it possible to handle a button click within a button click event function in VBA? I want to iterate through records in an access database table, find similar records, present the user with two of those similar records, then have them click a button to choose one of those records.

Consider this pseudocode: 

 Sub Choose_Click()
   For (loop through DB recordset)
       Label1 = Record1
       Label2 = Record2
       Listen for button click
       If (Click Button1)
          Choose Record1
          do something
       ElseIf (Click Button2)
          Choose Record2  
          do something

Upvotes: 1

Views: 2733

Answers (2)

LazyBear
LazyBear

Reputation: 353

Below is an example of how to implement Button Click Events during a function.
I solved this using a global variable, as suggested in the comments by @Rob Anthony. When the macro runs, it waits for the Global variable value ButtonClicked to change which indicates that a button was clicked (in my case, a choice was made). If all you want to indicate is that a button (any button) was clicked, then a Boolean value would be better, but this solution also indicates which button was clicked. The value of ButtonClicked is populated by the individual click event macros assigned to the selection buttons.

Option Explicit

'Global Variable
Public ButtonClicked As String

Private Sub Choose_Button_Click()
    ButtonClicked = "None"

    Do Until ButtonClicked <> "None"
        'Do Nothing but allow other Events - Wait until a selection button is clicked
        DoEvents
    Loop

    MsgBox ButtonClicked & " Button Clicked!"

End Sub

Private Sub Select1_Button_Click()
    ButtonClicked = "Select1"
End Sub

Private Sub Select2_Button_Click()
    ButtonClicked = "Select2"
End Sub

Upvotes: 0

Rob Anthony
Rob Anthony

Reputation: 1813

I would use a MsgBox. After displaying the two records, do this.

Response = MsgBox("Process Record 1? No=Process Record 2",VbYesNo+vBDefaultButton1,vBQuestion,"Process records")
If Response = vbYes Then
    Do stuff here
Else
    Do stuff here
End If

OR create my own dialog box to do the same thing but with buttons for Record 1 and Record 2

Upvotes: 1

Related Questions