Excel Macro for drop down

[Slippenos]

I fire a macro when the drop down value below changes. Problem is, I don't know how to properly define the dropdown.

Sub DropDown61_Change()
    Dim Dropdown_1
    [red]Dropdown_1 = Worksheets(1).ListBox(1).Value[/red]
    If D61 = "25198047" Then
        Worksheets(1).Range("F18").Value = "X"
    End If
End Sub

Any thoughts?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[SkipVought]

Hi,

If it's on your sheet...

  With Activesheet.shapes("DropDown61").oleformat.object.object
msgbox .text& " is the selected value"
  end with

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[Slippenos]

SkipVought,

Thanks for responding. I manipulated your suggestion to this:

Sub DropDown61_Change()
    Dim Dd_1
    [purple]Dd_1 = ActiveSheet.Shapes("DropDown61").OLEFormat.Object[/purple]
    If Dd_1 = "25198047" Then
        Worksheets(1).Range("F18").Value = "X"
    End If
End Sub

I get this error:
"The item with the specified name was not found."

Any other suggestions?

Thanks.


[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[SkipVought]

When you SELECT that object on the sheet, what name is in the Name Box?

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[Slippenos]

There is no name. I got the DropDown_61 from the Subprocedure Excel created for me.

If I right-click and go to Format Control, my only options are tabs: Size, Protection, Properties, Web, and Control.

Any thoughts?


[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[SkipVought]

Then you're using a Control Toolbox control

Sub DropDown61_Change()
    Dim Dd_1
    Dd_1 = DropDown61.text
    If Dd_1 = "25198047" Then
        Worksheets(1).Range("F18").Value = "X"
    End If
End Sub

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[Slippenos]

I got a run-time error '424'. Object required.
[red]Dd_1 = DropDown61.Text[/red]

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[SkipVought]

Exactly how did your insert this object?

Where is this object located?

Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[Slippenos]

I add a combo box from the fromat control toolbox. The object is located on top of the cells; not really part of the sheet.

I right-click on the control and I go to Format Control to select its values from some cells on another sheet.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[GlennUK]

I go to Format Control to select its values from some cells on another sheet

that sounds more like a Forms Toolbar object than a Control Toolbox object!?!?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.