Combo Box Change

[Faiek]

Hi All,
I'm trying to make a combo box in which When You select a value you goto a specific cell in another worksheet, but My code keeps giving me Runtime Error 424 "Object Required". The Code I Have so far is:

Sub DropDown4_Change()
Select Case DropDown4.Text
Case "CRM-1197-1 "
Sheets("Sheet2").Range("A12").Select
Case "CRM-1197-10 "
Sheets("Sheet2").Range("A13").Select
Case Else
End Select
End Sub

This Is the 4th method I have tried, I believe it will work but I think I'm missing something. If you could provide help it would be greatly appreciated.

 

[bjurney]

Did you make sure that your combobox name was changed to DropDown4? That is the only way I have been able to simulate the error with your code.

 

[MickG]

Try this:-

[COLOR=navy]Sub[/COLOR] DropDown4_Change()
[COLOR=navy]With[/COLOR] ActiveSheet.Shapes("Drop Down 4").OLEFormat.Object
   [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] .List(.ListIndex)
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "CRM-1197-1"
            Sheets("Sheet2").Select
            ActiveSheet.Range("A12").Select
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "CRM-1197-10"
            Sheets("Sheet2").Select
            ActiveSheet.Range("A13").Select
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Else[/COLOR]
    [COLOR=navy]End[/COLOR] Select
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Faiek]

Try this:-

[COLOR=navy]Sub[/COLOR] DropDown4_Change()
[COLOR=navy]With[/COLOR] ActiveSheet.Shapes("Drop Down 4").OLEFormat.Object
   [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] .List(.ListIndex)
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "CRM-1197-1"
            Sheets("Sheet2").Select
            ActiveSheet.Range("A12").Select
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Is[/COLOR] = "CRM-1197-10"
            Sheets("Sheet2").Select
            ActiveSheet.Range("A13").Select
        [COLOR=navy]Case[/COLOR] [COLOR=navy]Else[/COLOR]
    [COLOR=navy]End[/COLOR] Select
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank You For Your Reply. I Think this May be the solution, one other question though. I have the combo box embedded in the worksheet and not in a userform How Do I give it A Name, Because it gives me the error stating It could not find the object with that name.

Thanks.
Faiek

 

[bjurney]

Click on the combobox and press F4. A properties window will appear. The Name option is the first option.

 

[Faiek]

Click on the combobox and press F4. A properties window will appear. The Name option is the first option.

Fixed the problem! Thanks All!