|
Running a macro based on combo box value
I've searched (and searched and searched), but I can't get the darn thing to work. I've got a combo box (which I've inserted using the Form toolbar) and I've given it options and values for said options. I've written 9 macros that I'd like to have run depending on which option is selected from the combo box. But I can't get the main macro (monitoring the combo box and firing any of the 9 macros depending on it's value) to identify the combo box. Does anybody have some insight? I'm just about ready to send it to somebody and pay them to fix it... any takers there? Thanks in advance! -- m3s3lf ------------------------------------------------------------------------ m3s3lf's Profile: http://www.excelforum.com/member.php...o&userid=34874 View this thread: http://www.excelforum.com/showthread...hreadid=552879 |
Running a macro based on combo box value
Hello m3s3lf, If I understand you correctly, this macro should work. This example uses a Forms Drop Down with 3 lines for brevity. You can expand this example to run as many macros as you have entries. First copy this code using CTRL+C then insert a VBA Module into your Workbook's Project. Paste the code into the new Module. Add the names of the macros to be called into the Select Case code below each Case statement. Be sure to save your changes using CTRL+S. Link this Macro to the Drop Down. When the user makes a selection, the macro will determine which line number was selected in the Drop Down. This line number then determines which macro will be executed. Code: -------------------- Sub RunMacrosFromDropDown() 'Get the Name of ComboBox (Drop Down) Cbo = Application.Caller 'Get the line number of the entry and the entry data With ActiveSheet.Shapes(Cbo).ControlFormat CboLine = .ListIndex CboData = .List(CboLine) End With 'Select Macro to run based on the Drop Down line selected Select Case CboLine Case 1 'Call First Macro Case 2 'Call Second Macro Case 3 'Call Third Macro End Select End Sub -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=552879 |
Running a macro based on combo box value
:eek: It's working!! I'm so happy! Thank you!! :cool: Leith Ross Wrote: Hello m3s3lf, If I understand you correctly, this macro should work. This exampl uses a Forms Drop Down with 3 lines for brevity. You can expand thi example to run as many macros as you have entries. First copy this code using CTRL+C then insert a VBA Module into you Workbook's Project. Paste the code into the new Module. Add the name of the macros to be called into the Select Case code below each Cas statement. Be sure to save your changes using CTRL+S. Link this Macro to the Drop Down. When the user makes a selection, th macro will determine which line number was selected in the Drop Down This line number then determines which macro will be executed. Code ------------------- Sub RunMacrosFromDropDown() 'Get the Name of ComboBox (Drop Down) Cbo = Application.Caller 'Get the line number of the entry and the entry data With ActiveSheet.Shapes(Cbo).ControlFormat CboLine = .ListIndex CboData = .List(CboLine) End With 'Select Macro to run based on the Drop Down line selected Select Case CboLine Case 1 'Call First Macro Case 2 'Call Second Macro Case 3 'Call Third Macro End Select End Sub ------------------- Sincerely, Leith Ros -- m3s3l ----------------------------------------------------------------------- m3s3lf's Profile: http://www.excelforum.com/member.php...fo&userid=3487 View this thread: http://www.excelforum.com/showthread.php?threadid=55287 |
| All times are GMT +1. The time now is 04:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com