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

Question

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

Rob Anthony · Accepted Answer · 2017-07-27 15:27:06Z

1

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

answered Jul 27, 2017 at 15:27

Rob Anthony

1,8431 gold badge17 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LazyBear Over a year ago

That's a good work-around. Might get a bit annoying at times when there are 20-30 choices (rare, but it happens). But if this is the only way. I've been searching, but it doesn't seem like it's possible to incorporate button-clicks to populate variables during a function.

Rob Anthony Over a year ago

The other way to do it would be to create two global variables to indicate that you are asking the question and record where you are in the loop. Make the two buttons enabled and then have two different routines which are triggered by the button presses, they check (and reset) the global variable and process the change. You would then need to jump back into the loop using one of the global variable to continue.

LazyBear Over a year ago

That's a good idea. I'll play around with that concept. I try to limit my global variables, but this seems like just an occasion that calls for it.

LazyBear Over a year ago

I came up with my some code based on your suggestion, and submitted it as an answer. I was unable to give you the answer credit, since the suggestion was made in the comments. That stuff doesn't matter to me, so if you want the credit just submit an answer with my code I can give you the answer credit. Thanks.

LazyBear · Accepted Answer · 2017-07-27 17:32:45Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related