Generic Button Click event?

[mark hansen]

This is probably a silly question... But is there an event so when you click on a button, it takes information from the button (any button) and runs code. I have a user form with 80 buttons, and I have the same code on each Private sub CommandButtonX_Click() event … Except for the button number.

Private Sub CommandButton78_Click()
    Dim Str As String
    Str = Me.CommandButton78.Caption
    Worksheets(Str).Activate
    If cbkAutoClose Then Call Label1_Click
End Sub

I was wondering if I could have something like … When a button is clicked... Take the caption from that button and activate that sheet and check to see if I need to close the user form. I see there is a mouse down and up events, but haven't used them...

When this user form is run, it takes the worksheet names and puts them on buttons, makes the user form Modeless so it just sits on top of the sheet while you work. (in a small display), When you click in the user form, it opens and displays buttons for each worksheet, allowing you to jump to a different worksheet. Its designed to be floating Table of Contents when you have many worksheets. Except for the .show command line, its completely self contained.

As I have (mostly) identical code for each buttons (up to 80), I have the above code replicated for each command button click event.

I'm just trying to make sure there isn't an easier way to set this up.

Thanks for any insight,
Mark

 

[6StringJazzer]

Here is a demo file that shows how to do this with buttons on a userform. The strategy is to create a Class that handles events from all the buttons. On form initialization, you create an array using the class as the type, then loop through the buttons to add them to the array. The Class has the Click event handler. In this demo, you can see that it retrieves the caption from the clicked button.

handle multiple buttons by creating a class.xlsm

Shared with Dropbox

www.dropbox.com

 

[mark hansen]

Thanks [U]6StringJazzer[/U] I'll need to download this file in the evening. I can't get to it from work.

 

[mark hansen]

Thanks 6StringJazzer!
I was able to get your file... It works as I expected it to work... I've never used a class module, something I will need to explorer. This will certainty make my code a lot smaller.

Thanks for the examples.
Mark

 

[6StringJazzer]

I can give you more explanation as needed. The Class is an object-oriented concept. VBA kinds of hints at being object-oriented (though it is not a full-blown OOL). A class is a kind of way of defining your own data type which also has custom operations defined for it and custom internal state data.

 

[mark hansen]

I've put your code into my user form and it working well. One problem I running into, which I don't think has anything to do with classes, is in the BTN Class module, in the part of the code that activates the sheet you want, it has a line to basically resets the user form to pick up any changes in worksheet names. The line is:

If cbkAutoClose Then Call Label1_Click

The cbkAutoClose check box will allow the user to set if they want to keep the table of contents open (like if they want to view tab after tab), or close the table of contents to stay on the worksheet but get the TOC out of the way.

The code in the Class module is looking for the Lable1_Click code, which is inside the user form.
When I Rem the line out, it works, but, of course the TOC doesn't reduce in size.

Thoughts?

Thanks for your help and ideas.

 

[mark hansen]

This is the code in the class module

Option Explicit
Public WithEvents ButtonGroup As CommandButton
'
Private Sub ButtonGroup_Click()
    Dim cbkAutoClose As Boolean
    Dim Str As String
'    Dim Msg As String
'
'    Msg = "You clicked " & ButtonGroup.Name & vbCrLf & vbCrLf
'    Msg = Msg & "Caption: " & ButtonGroup.Caption & vbCrLf
'    Msg = Msg & "Left Position: " & ButtonGroup.Left & vbCrLf
'    Msg = Msg & "Top Position: " & ButtonGroup.Top
'    MsgBox Msg, vbInformation, ButtonGroup.Name
    Str = ButtonGroup.Caption
    Worksheets(Str).Activate
    If cbkAutoClose Then Call Label1_Click
End Sub

 

[6StringJazzer]

I am not quite sure what your question is--but that line of code needs to qualify the userform.

    If cbkAutoClose Then Call UserForm1.Label1_Click

Replace UserForm1 with the name of your actual userform.

 

[mark hansen]

Oh man, now I'm embarrassed.... Thanks,

 

[6StringJazzer]

We all make those kinds of errors, and all you need is a fresh pair of eyes