I have an Excel VBA user form with a multi-page control (of 6 pages) each containing a single command button.
I want to be able to detect which of the (6) buttons has been clicked without resorting to coding separate procedures for each of the buttons by name
eg. Sub cbCalculate1_Click, Sub cbCalculate2_Click, Sub cbCalculate3_Click etc.......
I had a similar requirement with text boxes on the user form and an EE expert provided me with the code to resolve this issue. He set up a Class module and checked the text box name to establish which of the text boxes had been changed. The functionality works fine.
So, I've tried to adapt his code and apply it to my button problem. But when I click on a button nothing happens. I've tried it in debug and the Class module procedure isn't even invoked. I can only assume I don't have the correct syntax to 'catch' this click event.
Here's the code I have so far.
In a Class module I have called clsCalculate...
Public WithEvents aButton As MSForms.CommandButton
Private Sub aButton_Click()
Dim Idx As Integer
If aButton.Name Like "cbCalculate*" Then
Idx = CInt(Mid(aButton.Name, 12, 1)) ' Which group?
The above Class procedure should pass an argument (Idx) to the form module procedure Calculate_Prices but when I click on a button it doesn't do anything.
Your help will be much appreciated.