The problem:
You want to have a User Form (a VBA-Dialog) which
For a solution of these problems check the following file and/or read on.
Example Code for Dynamic Dialogs in Excel VBA
Consider the following example: There is a table which contains products of some type. Each product has some properties like description, prize etc. This table is created by some database queries. Hence, you do not know how much entries the table will have.
You want to provide the user of your Excel application with an easy way to select those products and insert them in another table (e.g. an invoice). If a user wants to insert a product into the invoice, he clicks a button and you would like to present him a User Form that has one button for each product. If he clicks on one button, the concerning product is inserted into the invoice table with its description, price etc.
There are two problems to solve here:
The first is to add controls to a User Form at runtime. You will need some kind of code that looks like this:
For each row in product sheet create button with caption = product name
The second problem is to add event handling to those buttons at runtime. You would like to have some code that looks like this:
onOneButtonClicked(Button b) search row in product sheet, where product name = b.caption extract other product information from selected row and copy it to to active row in the invoice sheet
Let’s tackle the first problem first:
Adding controls to a User Form at Runtime using Excel VBA
This is very easy. First, create an empty form. Add a method that is named UserForm_Activate. It will be called each time the form is shown to the user. Add the following code to the method:
Private Sub UserForm_Activate() 'Get the right sheet from our workbook Dim productSheet As Worksheet Set productSheet = Worksheets("Products") Dim c As Range Set c = productSheet.Range("A2") 'Walk through the sheet and add a button each prodcut While (c.Value <> "") 'Create a button named "ProductButton" to the AddProduct Form Dim button As CommandButton Set button = Me.Controls.Add("Forms.CommandButton.1", "ProductButton", True) 'Set button properties With button .Height = 20 .Width = 90 .Top = c.Row * (.Height + 10) .Left = 10 'Set the caption to the name of our product .Caption = c.Value End With 'Go to the next row of product table Set c = c.EntireColumn.Rows(c.Row + 1) Wend End Sub
For this code to work you will of course need an Excel sheet named product, and some facility to show your form. For the sake of simplicity i just added a button to the invoice sheet, and called the show-Method of our form in its Clicked-EventHandler:
Private Sub ButtonAddProduct_Click() AddProduct.Show End Sub
The Screenshot shows the result of our work. So far so good. But there is one minor thing left to do: When the user clicks on one of the buttons, nothing happens. This is, because we did not link our buttons to any events. This is what we will do next.
Event Handling of dynamically added controls using Excel VBA
This is the trickier part of the overall problem. Normally, when you add a button to a form, you write a method ButtonName_Click and perform the event processing within that method. Our buttons, however, are generated at runtime. One way out of this mess is to fill your form with buttons, add a *_Click method for each of them and then use the visible- and caption-Property to create the illusion of buttons that are added dynamically. But since there may be very many buttons, you may end up dragging dozens of buttons onto the form and placing them manually. Let alone writing all the event handlers for those buttons.
As you might have anticipated, we will use a different approach.
Create a Class Module, name it DynamicButton, and add the following code to it:
Option ExplicitPublic WithEvents button As CommandButtonSub button_Click() MsgBox "Hello from button " & button.Caption End Sub
Obviously the goal is, that the button_Click Method gets called, when one of our buttons is clicked. How can that be achieved?
The button variable is going to be filled with one of our dynamically created Buttons. By using the WithEvents keyword, we tell VisualBasic, that the button is a variable which can handle events. When it receives a Click-Event, VisualBasic searches for a method called button_Click and executes it.
But for all this to work, we need to have many of those button Variables. So let’s revisit our form, and make it’s code look like this:
Option ExplicitPrivate dynamicButtons() As New DynamicButton 'Array of dynamic buttons NEW Private Sub UserForm_Activate() 'Get the right sheet from our workbook Dim productSheet As Worksheet Set productSheet = Worksheets("Products") Dim c As Range Set c = productSheet.Range("A2") 'Initialize dynamicButtons Array 'NEW ReDim dynamicButtons(0) 'Walk through the sheet and add a button for all prodcuts While (c.Value <> "") 'Create a button named "ProductButton" to the AddProduct Form Dim button As CommandButton Set button = Me.Controls.Add("Forms.CommandButton.1", "ProductButton", True) 'Set button properties With button .Height = 20 .Width = 90 .Top = c.Row * (.Height + 10) .Left = 10 'Set the caption to the name of our product .Caption = c.Value End With 'Add Event-Handling for this button 'NEW ReDim Preserve dynamicButtons(UBound(dynamicButtons) + 1) 'NEW Set dynamicButtons(UBound(dynamicButtons) - 1).button = button 'NEW 'Go to the next row of product table Set c = c.EntireColumn.Rows(c.Row + 1) Wend End Sub
Four lines (excluding comments) have been added:
There is now an array, that holds instances of the DynamicButton class we created above. This array is initialized at the beginning of the Activate-method. Every button we create is then assigned to a button member-Variable of one DynamicButton-Object.
Now, when someone clicks one of the buttons, VisualBasic calls the button_Click-method of the DynamicButton class. The result is shown in the screenshot.
Enjoy your buttons for a minute. Aah, nice!
This was the tricky part. The rest is straightforward. We just fill the invoice with the data from our product table, according to the caption of the button that was clicked. Modify the button_Click-method according to the following code:
Option ExplicitPublic WithEvents button As CommandButtonSub button_Click() 'MsgBox "Hello from button " & button.Caption 'Get the right sheets from our workbook Dim productSheet As Worksheet Set productSheet = Worksheets("Products") Dim invoiceSheet As Worksheet Set invoiceSheet = Worksheets("Invoice") 'Find the line belonging to the button Dim c As Range Set c = productSheet.Range("A2") While (c.Value <> "") If (button.Caption = c.Value) Then 'Find first free line in invoice sheet Dim iC As Range Set iC = invoiceSheet.Range("A2") While (iC.Value <> "") Set iC = iC.EntireColumn.Rows(iC.Row + 1) Wend iC.EntireRow.Columns("A").Value = c.Value iC.EntireRow.Columns("B").Value = c.EntireRow.Columns("B").Value iC.EntireRow.Columns("C").Value = c.EntireRow.Columns("C").Value 'We are done Exit Sub End If 'Go to the next row of product table Set c = c.EntireColumn.Rows(c.Row + 1) Wend 'We have not found a product that matches the buttons caption. This should not happen Debug.Assert (False) End Sub
That’s it. There are many things, that might be considered from here, to make the dialog more fancy: Adding pictures to our buttons, dynamically hiding and showing buttons, depending on other buttons the user clicks on (and by that realizing subgroups) and so on. Drop me a mail if you are interested in one of those.
Remember, that you can find our example code here:
Example Code for Dynamic Dialogs in Excel VBA
Hi there,
Great piece, thanks for the helpful tip. I am attempting a similar project and would love to get your input on how to approach it: I’m trying to create a form that populates checkboxes into the form area based on the content of a defined worksheet. For example, if the worksheet contains the value “Jan 2008” in A2, “Feb 2008” in B2 and “Mar 2008” in C2, then my form should show three checkboxes called “Jan 2008”, “Feb 2008”, and “Mar 2008”. The user can then select any of these checkboxes to output a custom report (I’ve already written this code, although I’ll need to modify it somewhat to fit within the parameters of the form).
Would love your input before I begin hacking away at this on any good ways to develop it.
Thanks in advance,
Blake
Hi Blake!
I’m glad to hear that the post helped you.
Your project looks similar indeed. I would assume that for placing the checkboxes you can add them in the same way as it is done for the buttons in the UserForm_Activate method.
As there is probaly no need for you to catch the event that is thrown by a individual checkbox when it is clicked, you can probably omit all the event handling stuff.
Instead just query for the checkboxes state in a general Ok_Clicked method or someting like that.
Hth!
Tom
Thanks Tom. I’ll give it a shot and let you know what I come up with.
Hi there!
There seems to be a problem with your style sheets: the problem is that the code segments are randomly mashed up together (tried IE and Firefox) making it a real pain to try out in VBA via copy/paste.
Other than that the code works great. Thanks!
Mick
Hi Mick!
Sorry to hear that there is a copy/paste problem.
How did you copy the code? Perhaps you overlooked the following feature:
On the top of each code section there is a “view plain” and a “copy to clipboard” option. I tried them both in IE and Firefox and both seemed to work. However, I had to paste into Wordpad or Notepad++, since the basic Notepad can not handle the line breaks.
Hth!
Tom
Hi,
I am creating new worksheets using VBA (WorkSheets.Add method). I want to add a ‘SelectionChange’ handler for each such new worksheet within the VBA procedure.
I found the method really great, but my question is: Can the handler array be preserved even after Excel is closed? Or it needs to be re-created every time?
Thanks!
Abhijit.
Hi Abhijit!
The simple answer to your question is:
Yes, the handler array has to be recreated every time the document is opened.
The situation is tricky:
When an Excel Macro runs (fired by an event), all variables die, when the macro is over. Hence preserving any kind of state between two events, requires storing that state somewhere else than in the macro’s variable instances.
The event handlers are attached to the buttons (think of it as a function pointer), and the state of the button’s parent form is preserved as long as it’s parent document is open. However, when the document is closed, the state of the form is reset to the state stored in the document. And since the buttons are created during the first opening of the form, the buttons (and their event handlers) are lost, when the document is closed.
Afaik the only way to save something between two Excel-sessions is to store it in the workbook or in another file. It is of course possible to write an event handler that executes VBA code when the workbook is closed and stores all necessary information to rebuild the previous state when the worbook is opened once more.
I hope this answers your question.
Tom
Can i use this code with textboxes created with variable names, like
for i=1 to 10
set zctrl= me.controls.add(forms.textbox.1, “zname” & i “”, true)
next
Thanks in advance for the help
Hi Mario!
I am not sure, whether I understand your question.
You should be able to create Textboxes in the way you stated above and name them zname1, zname2 etc.
What do you want to do with those boxes? I you want to handle events from them it should work the same way as with the buttons in my example.
I don’t know, if this short reply answers your question. If you need further information, please tell me more about what you want those textboxes to do.
Tom
Hi can any one help me in the code.
When i click the button it should go to next empty cell and it should print the system time. And how to disable the cells after entering the text.
Thanks in advance.
Any one can reply me to this mail address: swtyswthrt@gmail.com (or) Pacificblu007@yahoo.com.
Its urgent plzzzzzzzzz any one help me.
Fantastic code!
If you have time, I would really appreciate insight into the following:
I am trying to do something similar with OptionButtons added to a userform. Is it possible to use the same principle?
I am trying to trigger an event every time a different option is selected, and the optionbuttons have been added dynamically to a useform simply because there are so many of them and they may change in future.
I have decided not to use a listbox in order to keep the height of the userform to a minimum i.e. my optionbuttons can be displayed in two horizontal lines across the userform.
Thanks!
Actually, I just found the solution.
I need to change the line at the top of the classmodule to read
Public WithEvents button As MSForms.OptionButton
(I had previosuly tried
Public WithEvents button As OptionButton without success.)
hi
i have one column data in excel worksheet which i wanna transfer to userform as option button dynamically. can u pl help me out
Hi Ganesh!
According to Matthew’s comments, the Option Buttons can be created dynamically as well, so in principle it should work. Can you tell me exactly where you are stuck?
Tom
Took me awhile, but here is stub code for doing dynamic pull-down controls in a UserForm and their events on-the-fly.
To use the code below:
1) In Excel’s VBA environment, insert a UserForm and call it “AdvInput”. The code for it is given below.
2) Insert a Class module called “dynamicCombobox”. Code below.
3) Insert a Module called “TesT”. Code below.
4) Create a button on a spreadsheet whose commandbutton_Click() code is simply “AdvInput.Show”.
‘++++++++++++++
‘ Code for UserForm “AdvInput”
‘++++++++++++++
Option Explicit
Private Sub UserForm_Activate()
‘MsgBox “Adv Input activated.”
Call TesT.build_pulldown
End Sub
‘++++++++++++++
‘ Code for “dynamicCombobox” Class
‘++++++++++++++
Option Explicit
Public WithEvents cBox As MsForms.comboBox
Public startUp As Boolean
Sub CreateMe(myUF As UserForm, myName As String)
MsgBox “dude, got here in create.”
startUp = True
Set cBox = myUF.Controls.Add(“Forms.ComboBox.1”, myName)
MsgBox “dude, got done with create.”
End Sub
Public Sub cBox_Change()
If startUp Then
MsgBox “Hello from comboBox startup ” & cBox.Name
Exit Sub
End If
MsgBox “Hello from comboBox normal event ” & cBox.Name
End Sub
‘++++++++++++++
‘ Code for TesT Module
‘++++++++++++++
Public dCombobox() As New dynamicCombobox
Sub build_pulldown()
Dim i As Long, numCol As Long, q As Integer
ReDim dCombobox(0)
For i = 0 To 2
q = UBound(dCombobox)
ReDim Preserve dCombobox(q + 1)
Call dCombobox(q).CreateMe(AdvInput, “DudeCtrl” & i)
With dCombobox(q).cBox
‘ Sample population of pulldown
.AddItem “Dude” & i
.AddItem “Dudess” & i
‘ Sample positioning of pulldown
.Top = 25 * i
End With ‘ dCombobox(q).cBox
dCombobox(q).startUp = False ‘ no longer in startup mode
Next i
End Sub
Thanks, it has been very helpful.
I cannot thank you enough for this article. I have been beating my head against code walls looking for the solution to this problem! The number of articles I trawled through looking for this answer has been frustrating, especially since the majority of them did not work on forms, but only worksheets and involved letting VB modify it’s own code (bad security practice IMHO).
Your publishing of this information is very much appreciated, and I now know where to go first to look for solutions to my problems! Awesome job.
Hi thanks for the example – very helpful. I can get _Click and _Change events to work but not _AfterUpdate. (ie use the same object, like a combobox but replace _Change with _AfterUpdate)
Is there a reason for this & is there a solution?
Many thanks
Great, That Solves a big problem of mine. Thanks. Too Good! Just one suggestion, If you add to the code The procedure to add dynamically a UserForm then I think the next person looking for the solution can stop his search here. Anyways great stuff!
I have just one more problem, if you can help.
I have 2 workbooks. A.xls and B.xls. Macro codes are password protected. But not the files. So I can open A.xls can work and run macros but cant view the code as the code is password protected.
Now i am working in a new project in B.xls and I need a code from A.xls. The code is in class Afileclass of A.xls.
1st problem: I need to create a object of Afileclass class in B.xls and access all public function.
2nd problem: Is there a way if I dont want the A.xls to remain open.
3rd problem: I want to pass parameters while calling the proc.
Thanks for your help.
Hello-Thanks for this post. I will definitely be needing it! I wonder if you can help a beginner tho:
I am running a piece of code in a macro. At a certain point in a Loop in the code, I want the userform to pop and for several of its textboxes to be populated with information from the sheet or from the macro (whichever proves easiest.
The data will be different each loop iteration, and the user will respond to the new data.
How do I get this info into the textboxes in the userform?
I don’t think I can pass a value to the userform initialise event, so is there some other way to pass a value to a userform?.
OR is there a counter of some sort I can use (within the userform’s code) which will count how often its been opened/initialised/activated before (during the macro run, obviously reset to 0 when macro finished looping).
Hope this makes sense. Any help will be much appreciated!
Thanks a lot for tht code! worked wonders!
This is absolutely awesome. I am a VBA newbie and I searched the Web for 2 days looking for a way to get dynamic controls to work with events. I was resigned to doing the .show / .hide method or scripting code on the fly, and was not happy about either approach. Then I came across your article. It’s a very clever use of classes and object arrays, and is an elegant and flexible approach. I have implemented my requirement successfully based on your code. Thank you, thank you, thank you!
Previously, I successfully implemented your model with SpinButtons on dynamically created forms, and I am now trying to implement with TextBoxes on a different form. (Excel 20101). I was surprised to see that events for TextBoxes (specifically Enter & Exit) that are available when you manually build a form do not appear to be available when you put that event in the class module.
To wit, in class module named TbHandle:
—
Public WithEvents tBox As MSForms.textBox
‘this one triggers
Private Sub tBox_change()
MsgBox “We just changed Textbox ” & tBox.Name
End Sub
‘this one doesn’t trigger
Private Sub tBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox “We just exited Textbox ” & tBox.Name
End Sub
—
I realized why the second one didn’t work when I saw that in the VBE, Enter and Exit were not in the right dropdown available at the top of the class window when tBox is selected in the left dropdown.
I thought I found a solution when I changed the declaration to
—
Public WithEvents tBox As MSForms.Control
Private Sub tBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox “We just left Textbox ” & tBox.Name
End Sub
—
Then the events Enter and Exit were available in the VBE dropdown for tBox! However, the program threw a
“Run Time error ‘459’
Object or class foes not support the set of events”
I apparently didn’t like this line of code anymore which is in another module:
—
Set tBoxes(y – 2, UBound(tBoxes, 2) – 1).tBox = NewTextBox
—
It apparently didn’t like my use of tBox in this line once I assigned it to a msforms.control, instead of msforms.texbox.
Now I don’t know where to go next. I really want to have an “Exit” event in the textbox so I can automatically do calculations on exit from the field and not depend on a manual button press to recalculate .tBox _Change will work but it is annoying because it triggers every time a character is entered, not when the user is finished entering the field.
Any ideas or insights? Do you know of a list anywhere that tell which VBA events work with which controls in which conditions?
Tom
Its a terrific starting point for those who are interested to know more about class modules and its uses. It would be useful to understand how the above code can be modified for multiple items in the product sheet (for eg, if I copy/paste into the product sheet and change the price, the multiple buttons generated at run time still pick up the price specified against the product the first time it appears on the list). Also, how could one put in a “cancel” button as standard part of the userform? Thanks a lot for your post.
Cheers
Mangesh
Thanks a bunch for this post.
I pretty much followed the instructions, but chose to use a collection, instead of an array of objects. Works out identically, but I think it’s preferable.
Cheers