Dynamic Dialogs in Excel VBA

The problem:
You want to have a User Form (a VBA-Dialog) which

  • controls (buttons, text fields etc.) are created at runtime based on information that is read from e.g. a table
  • event handling of those controls is created at runtime.
  • 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.

    Dynamic Dialogs in Excel at Work


    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

    Add Product Button and To-Be-Dynamic Dialog

    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.

    Message Handling in Dynamic Dialogs

    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

    Dynamic Dialogs in Excel at Work

    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

    25 thoughts on “Dynamic Dialogs in Excel VBA

    1. Blake Stuchin

      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

      Reply
    2. admin Post author

      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

      Reply
    3. Mick

      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

      Reply
    4. admin Post author

      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

      Reply
    5. Abhijit

      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.

      Reply
    6. admin Post author

      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

      Reply
    7. Mario

      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

      Reply
    8. admin Post author

      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

      Reply
    9. Matthew

      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!

      Reply
    10. Matthew

      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.)

      Reply
    11. ganesh

      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

      Reply
    12. admin Post author

      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

      Reply
    13. Glenn Maxey

      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

      Reply
    14. Toby

      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.

      Reply
    15. Edward

      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

      Reply
    16. Pinu

      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.

      Reply
    17. Alison

      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!

      Reply
    18. Robert

      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!

      Reply
    19. Robert

      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?

      Reply
    20. Mangesh

      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

      Reply
    21. Nuno

      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

      Reply

    Leave a Reply to Toby Cancel reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.