For a solution of these problems check the following file and\/or read on. 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. The second problem is to add event handling to those buttons at runtime. You would like to have some code that looks like this:<\/p>\n Let’s tackle the first problem first:<\/p>\n Adding controls to a User Form at Runtime using Excel VBA<\/strong><\/p>\n 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:<\/p>\n 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:<\/p>\n 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.<\/p>\n Event Handling of dynamically added controls using Excel VBA<\/strong><\/p>\n 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. Create a Class Module, name it DynamicButton, and add the following code to it:<\/p>\n Obviously the goal is, that the button_Click Method gets called, when one of our buttons is clicked. How can that be achieved? Four lines (excluding comments) have been added: Enjoy your buttons for a minute. Aah, nice! 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.<\/p>\n Remember, that you can find our example code here: 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,9],"tags":[],"class_list":["post-16","post","type-post","status-publish","format-standard","hentry","category-software-development","category-vba"],"_links":{"self":[{"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16"}],"version-history":[{"count":0,"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/16\/revisions"}],"wp:attachment":[{"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tom.paschenda.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nExample Code for Dynamic Dialogs in Excel VBA<\/a><\/p>\n
\nYou 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.<\/p>\n![\"Dynamic](\"http:\/\/tom.paschenda.org\/blog\/wp-content\/uploads\/2007\/08\/wearedone.jpg\")
<\/a><\/p>\n
\nThere are two problems to solve here:
\nThe first is to add controls to a User Form at runtime. You will need some kind of code that looks like this:<\/p>\n\r\nFor each row in product sheet\r\n create button with caption = product name<\/pre>\n
\r\nonOneButtonClicked(Button b)\r\n search row in product sheet, where product name = b.caption\r\n extract other product information from selected row and\r\n copy it to to active row in the invoice sheet<\/pre>\n
\r\nPrivate Sub UserForm_Activate() \r\n\r\n'Get the right sheet from our workbook\r\nDim productSheet As Worksheet\r\nSet productSheet = Worksheets(\"Products\") \r\n\r\nDim c As Range\r\nSet c = productSheet.Range(\"A2\")\r\n\r\n'Walk through the sheet and add a button each prodcut\r\nWhile (c.Value <> \"\")\r\n\r\n'Create a button named \"ProductButton\" to the AddProduct Form\r\nDim button As CommandButton\r\nSet button = Me.Controls.Add(\"Forms.CommandButton.1\", \"ProductButton\", True)\r\n\r\n'Set button properties\r\nWith button\r\n.Height = 20\r\n.Width = 90\r\n.Top = c.Row * (.Height + 10)\r\n.Left = 10\r\n'Set the caption to the name of our product\r\n.Caption = c.Value\r\nEnd With\r\n\r\n'Go to the next row of product table\r\nSet c = c.EntireColumn.Rows(c.Row + 1)\r\n\r\nWend\r\n\r\nEnd Sub<\/pre>\n
\r\nPrivate Sub ButtonAddProduct_Click()\r\nAddProduct.Show\r\nEnd Sub<\/pre>\n
![\"Add](\"http:\/\/tom.paschenda.org\/blog\/wp-content\/uploads\/2007\/08\/addproductbuttonanddialog.jpg\")
<\/a><\/p>\n
\nAs you might have anticipated, we will use a different approach.<\/p>\n\r\nOption ExplicitPublic WithEvents button As CommandButtonSub button_Click()\r\nMsgBox \"Hello from button \" & button.Caption\r\nEnd Sub<\/pre>\n
\nThe 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.
\nBut 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:<\/p>\n\r\nOption ExplicitPrivate dynamicButtons() As New DynamicButton 'Array of dynamic buttons NEW\r\n\r\nPrivate Sub UserForm_Activate()\r\n\r\n'Get the right sheet from our workbook\r\nDim productSheet As Worksheet\r\nSet productSheet = Worksheets(\"Products\")\r\n\r\nDim c As Range\r\nSet c = productSheet.Range(\"A2\")\r\n\r\n'Initialize dynamicButtons Array 'NEW\r\nReDim dynamicButtons(0)\r\n\r\n'Walk through the sheet and add a button for all prodcuts\r\nWhile (c.Value <> \"\")\r\n\r\n'Create a button named \"ProductButton\" to the AddProduct Form\r\nDim button As CommandButton\r\nSet button = Me.Controls.Add(\"Forms.CommandButton.1\", \"ProductButton\", True)\r\n\r\n'Set button properties\r\nWith button\r\n.Height = 20\r\n.Width = 90\r\n.Top = c.Row * (.Height + 10)\r\n.Left = 10\r\n'Set the caption to the name of our product\r\n.Caption = c.Value\r\nEnd With\r\n\r\n'Add Event-Handling for this button 'NEW\r\nReDim Preserve dynamicButtons(UBound(dynamicButtons) + 1) 'NEW\r\nSet dynamicButtons(UBound(dynamicButtons) - 1).button = button 'NEW\r\n\r\n'Go to the next row of product table\r\nSet c = c.EntireColumn.Rows(c.Row + 1)\r\n\r\nWend\r\n\r\nEnd Sub<\/pre>\n
\nThere 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.
\nNow, when someone clicks one of the buttons, VisualBasic calls the button_Click-method of the DynamicButton class. The result is shown in the screenshot.<\/p>\n![\"Message](\"http:\/\/tom.paschenda.org\/blog\/wp-content\/uploads\/2007\/08\/messagehandlingatwork.jpg\")
<\/a><\/p>\n
\nThis 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:<\/p>\n\r\nOption ExplicitPublic WithEvents button As CommandButtonSub button_Click()\r\n\r\n'MsgBox \"Hello from button \" & button.Caption\r\n\r\n'Get the right sheets from our workbook\r\nDim productSheet As Worksheet\r\nSet productSheet = Worksheets(\"Products\")\r\nDim invoiceSheet As Worksheet\r\nSet invoiceSheet = Worksheets(\"Invoice\")\r\n\r\n'Find the line belonging to the button\r\nDim c As Range\r\nSet c = productSheet.Range(\"A2\")\r\n\r\nWhile (c.Value <> \"\")\r\n\r\nIf (button.Caption = c.Value) Then\r\n\r\n'Find first free line in invoice sheet\r\nDim iC As Range\r\nSet iC = invoiceSheet.Range(\"A2\")\r\n\r\nWhile (iC.Value <> \"\")\r\nSet iC = iC.EntireColumn.Rows(iC.Row + 1)\r\nWend\r\n\r\niC.EntireRow.Columns(\"A\").Value = c.Value\r\niC.EntireRow.Columns(\"B\").Value = c.EntireRow.Columns(\"B\").Value\r\niC.EntireRow.Columns(\"C\").Value = c.EntireRow.Columns(\"C\").Value\r\n\r\n'We are done\r\nExit Sub\r\n\r\nEnd If\r\n\r\n'Go to the next row of product table\r\nSet c = c.EntireColumn.Rows(c.Row + 1)\r\n\r\nWend\r\n\r\n'We have not found a product that matches the buttons caption. This should not happen\r\nDebug.Assert (False)\r\n\r\nEnd Sub<\/pre>\n
<\/a><\/p>\n
\nExample Code for Dynamic Dialogs in Excel VBA<\/a>
\n<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"