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