Variables In For Loops

  • Is it possible to use a variable to change a controls name in a For Loop?
    I have several worksheet combo boxes that have the same root name, e.g., cbobox1, cbobox2. cbobox3, etc. I want to be able to keep the main name and change the numeric part in a for loop and also perform some function with the control.

    For example:

    Code
    1. For x = 1 to 10
    2. Worksheets("Name").cbobox(x).Clear
    3. next x



    This gives the basic idea. How do I make cbobox(x) evaluate correctly?

  • Re: Variables In For Loops


    I've used something like the example below to do this. For example, if you have 10 textboxes and you want to do something to each of them, you can loop through them like this:


    Code
    1. For x = 1 To 10
    2. ControlName = "cbobox" & x
    3. For Each ctl In YourUserForm.Controls
    4. If ctl.Name = ControlName Then
    5. Whatever you want here
    6. End If
    7. Next
    8. Next x
  • Re: Variables In For Loops


    Thanks for the help. I tried this:


    Code
    1. Dim ControlName as String
    2. For x = 1 To 10
    3. ControlName = "cboPrtStatBott" & x
    4. Worksheets("Bottles").ControlName.Clear
    5. Next x


    but it didn't work either.


    I have numerous worksheet combo boxes. Some of them have the "root" name in common and are distinguished by a number. e.g. cboPrtStatBott1, cboPrtStatBott2, cboPrtStatBott3, etc.


    One of the functions I want to perform is to clear the combo box before I add new data to it, hence the line:


    Worksheets("Bottles").cboPrtStatBott1.Clear


    This line will do what I need it to do. The question is how can I do this in a For Loop so that I can cycle through all of the changes I need to make on all of the combo boxes.


    I hope this helps clarify what I'm looking for.

  • Re: Variables In For Loops


    Do you mean you're trying to clear the contents? The names can't be cleared. Each control must have an identifying name.


    Code
    1. Dim ControlName As String
    2. ControlName = "cboPrtStatBott"
    3. For x = 1 To 10
    4. ActiveSheet.Shapes("Controlname" & x).Value = ""
    5. Next x
  • Re: Variables In For Loops


    Yes, I'm trying to clear the contents of the combo boxes. That code didn't work either - I don't think the combo box is a shape?


    I tried:


    Code
    1. Worksheets("Bottles").Activate
    2. Dim ControlName As String
    3. ControlName = "cboPrtStatBott"
    4. For x = 1 To 10
    5. ActiveSheet.Shapes("Controlname" & x).Value = ""
    6. Next x


    It wasn't able to find the control.