![]() The macro does not appear in the list of available macros.Having created a macro with arguments in the previous section, it raises a few new issues when assigning it to a button. There are no hardcoded variables within the code they are passed to the code when it is called. The code above can be used with any ListBox. Sub lstBoxCount(wsName As String, lstBoxName As String) We can pass the worksheet name and Listbox name into the macro as arguments, by doing this, we can use a single piece of VBA code. Now imagine we had 30 Listboxes and 30 buttons… that would require 30 macros! Bad idea. ![]() If you notice, the sheet name and Listbox name are hardcoded into the macro, therefore we would need one macro for each button. If we had created separate code for each button, then the VBA code for clicking the button below the lstBox1 would be as follows: Sub lstBoxCount()ĭim lstBoxName As String Dim lstBox As ListBox I will assume you already know how to create a button and assign a macro to it. Lets consider how we can achieve this in the most efficient way possible. Whenever the button is clicked, the count of items in the Listbox above it is displayed in a message box. There are two Listboxes, each containing a list, with a button below each. The Exampleįor the purposes of proving how this works, here is our example scenario. To pass an argument to a macro just requires the right syntax when assigning the macro to the button. But please don’t do either of those, that would be crazy. These are simple enough to create with the basic Form Controls found on the Developer Ribbon, which is what makes them a popular option.īut what if you had lots of buttons, all of which did a similar thing apart from a few different variables, arguments or parameters? One option is writing separate code for each button, or a second option maybe a big If statement to handle the logic for all the buttons. ![]() One of the most popular methods of running a macro is having a button on the face of the worksheet. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |