Need to get Value from ComboBox Form Control

**pipoliveira** · 06-25-2013, 08:40 AM

Dear all,

I hope you are all well.

I have a ComboBox Form Control in one sheet. The combobox drop down list contains the name of all sheets available. Under the combobox, I have a commandbutton so when a user selects the sheet name from the ComboBox form control and clicks the CommandButton, a range from sheet1 will be copied to the selected sheet.

I have changed the form control name to "MonthDropDown".

Here's the code I am using:

Private Sub CommandButton1_Click()
Dim sname As String

    If Sheets("Sheet1").Shapes("MonthDropDown").ControlFormat = "" Then
    MsgBox "Please enter the Sheet number!", vbCritical, "No week selected"
    Exit Sub
    End If
        With Worksheets("Sheet1")
            sname = .Shapes("MonthDropDown").ControlFormat.Value
            Sheets(sname).Range("AU19:AU45").Value = Sheets("Sheet1").Range("AU19:AU45").Value

        End With

End Sub

The problem I am facing is an 'Run-Time error 9' - Subscript out of range on the range outlined in red.

Any ideas on how to Debug this error?

You assistance on this matter will be highly appreciated.

Best regards,
Filipe Oliveira.

**AndyLitch** · 06-25-2013, 08:52 AM

I think you'll find the problem is with the assignation of sname ..
To prove it you could add
Msgbox sname
directly after
sname = .Shapes("MonthDropDown").ControlFormat.Value

**pipoliveira** · 06-25-2013, 09:02 AM

Hi Andy,

Thanks for the update.

I searched some other examples and I found the solution.

Here's what was missing:

Private Sub CommandButton1_Click()
Dim sname As String

    If Sheets("Sheet1").Shapes("MonthDropDown").ControlFormat = "" Then
    MsgBox "Please enter the Sheet number!", vbCritical, "No week selected"
    Exit Sub
    End If
        With Sheets("Sheet1").Shapes("MonthDropDown").ControlFormat
            sname = .List(.Value)        End With
        
    Sheets(sname).Range("A19:A45").Value = Sheets("Sheet1").Range("AU19:AU45").Value
      
End Sub

Now it works perfectly!

Thanks a million. Really appreciated.
Filipe

**pipoliveira** · 06-25-2013, 09:02 AM

Hi Andy,

Thanks for the update.

I searched some other examples and I found the solution.

Here's what was missing:

Private Sub CommandButton1_Click()
Dim sname As String

    If Sheets("Sheet1").Shapes("MonthDropDown").ControlFormat = "" Then
    MsgBox "Please enter the Sheet number!", vbCritical, "No week selected"
    Exit Sub
    End If
        With Sheets("Sheet1").Shapes("MonthDropDown").ControlFormat
            sname = .List(.Value)        End With
        
    Sheets(sname).Range("A19:A45").Value = Sheets("Sheet1").Range("AU19:AU45").Value
      
End Sub

Now it works perfectly!

Thanks a million. Really appreciated.
Filipe