How to properly reference a user control and dynamically add it to page?

Macro triggers

• QUESTION: Is there a way to get a dropdown menue to trigger a macro when an item in the drop down menue has been choosen? ANSWER: Michael, Depends on what you mean by dropdown menu. If you mean a dropdown on a worksheet (or for activeX controls they can also be on a userform). there are three different types of dropdown. The list option from data validation. If a selection is made from this dropdown, it will trigger the worksheet change event and the Target argument will hold a reference to the cell where the selection was made. The dropdown from the forms toolbar (in Excel 2007, this would be the dropdown control that is not an activeX control). You can assign a macro to this type of control and it will fire when a selection is made from the dropdown menu. The activeX Combobox selected from the control toolbox toolbar (or in Excel 2007 the activeX combobox). Selecting an item from this dropdown triggers the change event. If you mean the dropdown control for the the commandbar in97 to2003 - these are part of the menu system, then you can assign a macro to the onaction property of the dropdown. if that is what you are talking about, then here is some sample code that shows how to add a dropdown to a commandbar and assign the onaction property of the control to a macro. All this code would be in a general module: Option Explicit Private Const szBAR_NAME As String = "ComboDemo" Sub AddCmdBarCombo() Dim cbrBar As CommandBar Note, the class name of both types of control is CommandBarComboBox Dim ctlComboBox As CommandBarComboBox Dim lCount As Long On Error Resume Next CommandBars(szBAR_NAME).Delete On Error GoTo 0 Set cbrBar = CommandBars.Add(szBAR_NAME, msoBarFloating) cbrBar.Visible = True Set ctlComboBox = cbrBar.Controls.Add(msoControlComboBox) ctlComboBox.OnAction = ThisWorkbook.Name & "!HandleCombo" For lCount = 1 To 5 ctlComboBox.AddItem "Item " & lCount Next lCount ctlComboBox.ListIndex = 0 End Sub Sub HandleCombo() Dim ctlComboBox As CommandBarComboBox Dim szItem As String Set ctlComboBox = CommandBars.ActionControl szItem = ctlComboBox.Text This is required to overcome a screen refresh bug in97. Application.ScreenUpdating = False Application.ScreenUpdating = True MsgBox "You chose " & szItem End Sub Sub DeleteCommandBar() CommandBars(szBAR_NAME).Delete End Sub When you make a selection from the dropdown, the macro assigned to the onaction property is run/triggered. ---------- FOLLOW-UP ---------- QUESTION: Maybe an example would better serve this question. Obviously the example will be less complex than the actual situation, but hopefully you will get the jest of it. EG: On sheet 1 I have a data valadation drop down menu for the years I want to see data from. 1997-2010 is in my data validation drop down menu with each year able for selection. When 1997 is selected from this box I need a macro to copy and paste from (Hidden) sheet 2, and (Hidden) sheet 3 certain data for that particular year and put that data into the final sheet 4 which has the form on it. I have the data validation dropdown and I have the macros for each year created I just need to know how I can get the macros to automatically run when the end user selects a year from the data validation dropdown menus by the year they select. Where can I attach the macros for each individual year? ANSWER: Michael, The macros for the years should be in a general module. You would then go to the sheet4 where the form is (i assume that is where the year selection dropdown is - assume it is in cell B7). Right click on the sheet tab and select view code you will get the sheet class module. At the top of the module will be two dropdown. You should select Worksheet from the left dropdown and Change from the right dropdown. this will put in a declaration for the change event. It will look like this: Private Sub Worksheet_Change(ByVal Target As Range) End Sub when you first select worksheet, it may put in this event: Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub that is the selectionchange event. You don't want to use that one (you can delete it if you want). Now in the Worksheet_Change event it will hold the argument Target. When a cell is edited (such as selecing from the data validation dropdown), the Change event will be triggered. So the first thing you want to do is check if the cell triggering the event is the one with your dropdown (recall that for illustration, I chose "B7" as that cell). Private Sub Worksheet_Change(ByVal Target As Range) Dim lTarg as Long If target.count > 1 then exit sub ' if more than one cell caused the event to fire, ' that isn't something we are interested in. So we exit the event If Target.Address(0,0) = "B7" then ' if we get here we know that activity in our dropdown cell triggered the event ' check if it is blank or if it has a value. If blank, exit the event if len(trim(target)) = 0 then exit sub ' now you can use whatever value is in the cell to determine which of your ' macros to call if isnumeric(target) then lTarg = clng(target) 'convert the value in Target to a integer if lTarg >= 1997 and lTarg End If ' end if Target.Address(0,0) = "B7" End Sub I use mymacro1997 to represent the name of the macro to execute when 1997 is chosen in the dropdown box. Hope that clears it up for you. Change the "B7" to the cell that has your dropdown. Make sure macros are enabled for your project. ---------- FOLLOW-UP ---------- QUESTION: I am not sure what I am doing wrong here. The only thing that was misunderstood was the starting Data verification drop down is on page one C2, macro starts on page two, copies to page four. Which I changed in the code (Thank You by the way). Maybe it is in my lack of knowledge about modules. I am sort of new to2007 so please bare with me. The general module you speak about is in the Personal workbook so that it is global? Or is this something different? I saved all of the Macros in "this workbook" for the simple reason that they will not be needed in any other workbook. Do they need to be redone? Well here is the sequence I went through; 1.went to sheet one where the Data Vald drop down is. Right mouse clicked on the sheet tab.Changed worksheet from the left and change from the right. Yes it pulled up the additional declaration. so after change in the right dropdown I deleted the workbook declaration. Cut and pasted your code inbetween the Declaration and the "End Sub" CHanged the names of the macros. CHanged the cell triggering the change in both locations to C2. Hit Alt Q. returned to DV dropdown and selected year. Error comes up stating: Compile Error: Sub or function not defined. I clicked OK and "Private Sub Worksheet_Change(ByVal Target As Range)" comes up in code with yellow highlight and yellow arrow. Any help would be greatly appreciated.

• Answer:

  Michael, macros in class modules, such as Thisworkbook, should just be code related to events (in my opinion). the easiest thing is to go to the vbe and in the project explorer, select you personal.xls. Then in the menu in the VBE, do Insert=>Module. That should create a module1 (or the next available number). That is a general module. Copy all your year macros from thisworkbook to your module1 (or whatever it is) in your personal.xls. Now in the change event code you have to Use Application.Run since you are calling a routine from another workbook - your workbook with the change event can not see the code in personal.xls Instead of Select Case lTarg ' make a decision based on the value in B7 Case 1997 mymacro1997 it would be Select Case lTarg ' make a decision based on the value in B7 Case 1997 Application.Run "Personal.xls!mymacro1997" make those changes for all macros and I would expect it to work. (one way to say what the argument to Application.Run should be is to make you sheet one (the sheet with the code) the active sheet in Excel. Then go to Tools=>Macro=>Macros and you should see your year macros there (unless you declared them private). They should be prefaced by Personal.xls as in the above - but whatever they look like, that is what you should put as the argument to application.run