VBA Many buttons point to the same _Click sub
I have a bunch of TextBox-Button pairs on a form. When the button is clicked I want to insert the value of the text box into a database. The name TextBoxes and Buttons follow a naming standard, for example Value1Tb - Value1Cmd and Value2Tb - Value2Cmd.
My problem is that since I want to do the same for every button I would like the possibility to write a Sub like:
Private Sub AnyButton_Click(sender As CommandButton) Dim tb As TextBox Set tb = GetTBByName(s.Name) PutValueToDatabase(s.Name,tb.Text) End Sub
But I cannot find a way to point the Click-event of a Button to a different sub than the standard
Anybody know a way around this, that doesn't involve me writing 50 or so different
If you are OK to use Form Controls rather that ActiveX, as it looks as though you may be at the moment, then Chris' solution seems good.
However if you need ActiveX CommandButtons then you are unable (as the VBA compiler will tell you, "Procedure declaration does not match...") to have parameters in the callback for the click event, and you are unable to raise the event from multiple objects, although you do of course know which button raised the event (since the relationship is 1 CommandButton = 1 Sub).
So... I would go with something like:
Private Sub Value1Cmd_Click() Call TheMethod(Value1Cmd) End Sub Private Sub Value2Cmd_Click() Call TheMethod(Value2Cmd) End Sub Private Sub TheRealMethod(sender As CommandButton) ' Do your thing ' Dim tb As TextBox Set tb = GetTBByName(s.Name) PutValueToDatabase(s.Name,tb.Text) ' Etcetera... ' End Sub
Requires a stub for each button, so some copying and pasting to begin with, but then easy to maintain etcetera as all _Click event callbacks are pointing at the same method...
Sub AutoWriteTheStubs() Dim theStubs As String Dim i As Long For i = 1 To 10 theStubs = theStubs & "Private Sub Value" & CStr(i) & "Cmd_Click()" & vbCrLf _ & " Call TheMethod(Value" & CStr(i) & "Cmd)" & vbCrLf _ & "End Sub" & vbCrLf & vbCrLf Next i Debug.Print theStubs End Sub
VBA Many buttons point to the same _Click sub, VBA Many buttons point to the same _Click sub. Question. I have a bunch of TextBox-Button pairs on a form. When the button is clicked I want to insert the value� I want to have a _Click event handler for every button I created during runtime: 'Add month indicators For intMonth = 1 To intMonthCount 'for example: 10 formTarget.frmCalendar.Controls.Add "F
It seems that what you want is to get the name of the clicked button. If you are creating buttons like this:
(where 'i' increments in a loop)
Set btn = Sheet1.Buttons.Add( , , , ,) With btn .OnAction = "btnSub" .Caption = "Upadate" .Name = "btn" & CStr(i) & "Cmd" End With
and then defining a generic "private sub btnSub()" for all the buttons, you could at least get the name of the button that was clicked using Application.Caller. Something like:
Private Sub btnSub() Dim ButtonName As String ButtonName = Application.Caller MsgBox ("Hello:" & ButtonName) End Sub
Hope it helps!
Handling Multiple User Form Buttons With One Event Handler , Last Updated on Mon, 27 Jan 2020 | Excel 2003 VBA Private Sub CommandButtonl_Click() ' Code goes here End Sub. Private Sub Each Click event handler is hard-wired to its CommandButton. You can, however, have each event� Private Sub pictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles pictureBox1.Click Inside, I plan to use this to change the image in the picture box, as follows: pictureBox1.Image = (My.Resources.picture_name)
I have this same situation, and I just have a click event for every button that is a wrapper to the function I want to call. This also allows you to pass sheet-specific parameters if you need to.
Public Sub StoreButton_Click() ' Store values for transaction sheet 3/27/09 ljr Call StoreTransValues(ActiveSheet) End Sub
Assign a macro to a Form or a Control button, The sub procedure CommandButton1_Click (see the figure below) runs these two You can run multiple macros from a button by entering the macro names on To run the VBA code that is now part of the button, click the ActiveX command The type of pointer that is displayed when the user positions the mouse over a� Private Sub CommandButtonl_Click() Call ButtonClick(l) End Sub. Private Sub CommandButton2_Click() Call ButtonClick(2) End Sub. If your UserForm has many CommandButtons, setting up all these event handlers can get tedious. You might prefer to have a single procedure that can determine which button was clicked and take the appropriate action.
I decided to make this an answer because I am doing something similar and I confirmed that it works.
You can store the
OLEobjects in a
Collection, of arbitrary size, containing
Custom Class Objects that include the OLEobjects and associations and the events that you need. Thus you can completely avoid any code stubs.
- Create a
Custom Classto bind the Button and TextBox pairs.
- Declare the Button object
- Include your call-back in the exposed button event handler in the Class Module.
- Put a
Publicroutine in a
Standard Moduleto initialise a
Custom Classobjects by spanning the Form Controls. You can also use this to
Addthe controls programmatically as a 'reBuild' option. The Collection can be inside another Class Module with all of the management routines, but it needs to be Instantiated and loaded in a
- Put a public routine in a standard module to receive the call-backs with whatever context you need. This can also be in a Worksheet Module if it makes for better encapsulation. You can use late binding to reference the callback or CallByName.
You need to bear in mind that the Module of the Form will recompile every time you add a control, so you have to be careful where you put your code.
My application has the controls directly on the Worksheet Surface, so I can't put the the Collection Class in, or source any initialisation of the Collection from the Worksheet module. This would amount to self modifying code and it grinds excel to a halt.
I dreamed this idea up through bloody-minded idealism (not necessarily a good thing) but, of course, I was not the first one to think of it as you can see here. @Tim Williams explains it in his answer. You can also google VBA Control Array Events to see plenty of similar examples including an excellent article by @SiddharthRout. In line with the VB6 analogy, he uses an
Array instead of a
Collection to achieve the same result.
I'll try to post some code later. My application is a bit different so it will take a lot of work to trim it down, but the principle is the same.
The other thing to bear in mind is that VBE really struggles with this type of thing so don't worry if it is loading up you processors. After you re-start with VBE off, all will be fine.
Excel VBA Sub Procedures: The Complete Tutorial, Step #1: Insert A Button. Go to the Developer tab in the Ribbon. Click on “Insert” and choose the In many cases, this VBA Sub procedure by simply button at the same time as the Ctrl key. mouse until the desired point. Private Sub MyCommandButton_Click() Parent.Tag = MyCommandButton.Caption Parent.Hide End Sub--- schnapp ---Add a Userform, (I assume the name is UserForm1) Add some controls (Frame, Multipage and Commandbuttons as you like) Paste in this code:--- schnipp ---Option Explicit Dim MCBHs As New Collection Private Sub UserForm_Initialize()
I just published (Open Source) the Event Centralizer for MSForms.
Citation: "The Event Centralizer for MSForms is a VBA programming tool that allows all sorts of custom grouping when writing handlers for the events occurring in UserForms.
With the Event Centralizer for MSForms, it is easy for example to have all TextBoxes react the same way when the Enter event occurs, or all except one, or only those with a particular Tag value.
Thanks to its events logs system, the Event Centralizer for MSForms is a powerful learning and debugging help."
I can't explain here how it works. I tried to do it on the site.
Macros for Excel Form Control Buttons, Instructions and sample macros to work with the form control buttons on a Excel VBA Tutorials Click on the new button to select it (or Ctrl+Click, if a macro has been at any point, the copy temporarily shows a visible name that is the same as Sub ListAllButtons() Dim wsList As Worksheet Dim wsA As� r/techsupport deals mostly with hardware and software problems, not coding problems. I would say like others have said, r/vba may be a better place to seek advice. From my measly VBA knowledge, I know my professor gave an example in my freshman coding class regarding a subject like this, so I would say it is possible.
How to Add Worksheet Macro Buttons in Excel, How to add macro buttons on each worksheet in Excel file, to go to There are two Excel VBA macros in the workbook -- one that takes you to the Sub GoSheetNext() Dim wb As Workbook Dim lSheets As Long Dim On the worksheet, drag the pointer, to add a button of a specific size, or click to create a� I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.
Subs and Functions, You can run this Sub by pressing F5 in the VBA Editor, you can run it by If ActiveCell is below 20 then we change the font to bold, Arial, 16 points. Now this code is fine as it stands. But suppose we wanted to use this same code again. We could put a button on the spreadsheet and then attach the macro FirstCode to it. In the latest post, I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also be run from the ActiveX controls on the Control Toolbox. We’ll use this simple macro for this example. Sub HelloWorld() MsgBox "Hello, World! ", vbExclamation End Sub When it […]
The complete Guide to Excel VBA Sub and how to use it, 8.1 The Format of Parameters; 8.2 Multiple Parameters; 8.3 Parameters With a Return Value; 8.4 ByRef and ByVal In Excel VBA a sub and a macro are essentially the same thing. Can be called it from VBA\Button\Event etc. but it won't appear in the list of Macros. Here are some important points about parameters:. I have a form that has multipe command buttons on it. Instead of making 40-50 commandbuttonx_Click subs (the form is a calendar), is there a way to loop through UserForm with multiple command buttons - VBA Visual Basic for Applications (Microsoft) - Tek-Tips