Using a Var to Loop through and change listbox name

[OSUBuckeye20]

I have an issue with my UserForm that I have researched extensively and can't quite find the solution. I have a multipage UserForm with a page for each of my 8 events we'll be attending for my work. A few of the ComboBoxes and Listboxes should be populated with identical information and I'm trying to use a loop to do so. For example, the naming convention of one of the listboxes is "lstMarketingMenuX" where X equals numbers 1 through 8 corresponding to the correct event. Here's what I've tried to do so far but I get runtime errors:

Sub AssignMarketingMenu()

[LIST]
[*]Dim x as integer 
[*]    For x = 1 To 8                                  'for events 1 - 8
[*]        With Me.Controls("lstMarketingMenu" & x)    'assign looped variable to listbox name
[*]            .RowSource = "Marketing_Menu"           'loop through listbox and assign row source
[*]        End With
[*]    Next x 
[*]End Sub 
[/LIST]

The row source of "Marketing_Menu" is a named range on the current sheet. I want to use the same row source for each of the listboxes. Also, I want the listbox source to be assigned at runtime so I currently have a call to this sub in my initialize event. If you could shed some light on this I would greatly appreciate it!!!

Thanks,
Mike

 

[Norie]

Mike

What errors are you getting?

Even if the comboboxes are on separate pages of the multipage you should still be able to address them directly via the Controls collection of the form.

 

[jmacleary]

Hi there. Try this: Mod shown in red)
With Me.Controls("lstMarketingMenu" & Cstr(x)) 'assign looped variable to listbox name

 

[OSUBuckeye20]

Norie - Thank you for your reply. I get the following error message: "Run Time Error '380'. Could not set the RowSource property. Invalid property value."

jmacleary - I tried modifying my code based on your suggestion and I get the same error message.

I fear it may be an issue with the .MeControls argument. If I don't use the loop and assign directly in the initialize event as shown below, it works correctly (meaning there's no issue with the named range that I'm aware of).

lstMarketingMenu1.RowSource = "Marketing_Menu"

I suppose the larger question is the following:

I've created 8 multipage pages, 1 for each event. The layout of each multipage is exactly the same, and all of the controls have the same exact names, but with event number suffixes to differentiate. For each event, I'd like to populate the same exact listboxes and most of the same comboboxes, but write their selections/values to different locations on the worksheet based on the event number. While more challenging from a coding perspective, I like the idea of a separate page for each event to make it easier for the user to know which event information is being edited/modified. If someone has a better idea of how to handle I'm open for suggestions!

Thanks in advance for your time and appreciate your comments! Please let me know if I can be more concise with my explanations.

 

[OSUBuckeye20]

All - quick update, and my apologies for any confusion. Originally I was referencing the wrong worksheet for my "Marketing_Menu" range and have now fixed that.

However, I am still getting an error message, just a different one than originally posted:

"Run-time error' -2147024809 (8070057)': Could not find the specified object."

Thanks again for the help and appreciate your time.

 

[Norie]

Sounds like there could be a problem with the names of the controls, have you checked them?

Perhaps adjust the code so you can check the control names in the code.

Something like this which will output the names to the Immediate Window.

Dim strControlName As String
Dim x As Integer

    For x = 1 To 8                                  'for events 1 - 8

        strControlName ="lstMarketingMenu" & x

        Debug.Print strControlName
 
        With Me.Controls(strControlName)    'assign looped variable to listbox name
            .RowSource = "Marketing_Menu"           'loop through listbox and assign row source
        End With

    Next x

End Sub

When next you get an error you can check to see which control name caused the problem.

 

[OSUBuckeye20]

Norie - Thank you for the idea and it worked great! I had a naming issue with my listbox on event 8 that must have triggered the error. I corrected the name for that particular listbox, verified through the immediate window that the looped string was creating the correct names and the remainder of the code worked exactly as intended.

I appreciate the advice and the time. I'm new to the forum overall and can't seem to find where to "give credit" or mark a post as the correct solution but I'd like to give proper credit and show good forum etiquette.

Thanks again!
Mike