Define the Range of a drop-down list

[BrucenAZ]

I'm a bit confused about cell assignment of drop-down lists.

My drop-down list refers to an input range and cell link on a second
worksheet in the same workbook. For example, the Input Range is:
Sheet2!$A$2:$A$6 and the Cell link is: Sheet2!$A$8. I'm trying to create a
macro that will reset the drop- down list to a default ""Choose answer" item
in the list. For me, "Choose answer" is located in Sheet2!$A$2. The
drop-down list is located in Sheet1.

Does anyone know how to define the Range of a drop-down list in Excel?

Thanks,
Bruce

 

[Per Jessen]

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

 

[BrucenAZ]

Perhaps I wasn't clear about my skill level: I know enough to get me in
trouble.
Per my post, this is what I've come up with. It does not work.

Range("Sheet2!$A$8").Select
ActiveCell = "Choose answer"

I do not know the Me command. I tried replacing my statements (above) with
your suggestion. I got a complie error; "Invalid use of Me keyword." Maybe
I'm not using it correctly?

Thanks,
Bruce

 

[Per Jessen]

Bruce,

The ME keyword is refering to the active object in this case the active
sheet, so it can only be used if your code is placed on the code sheet for
sheet1.

But if we shall follow your road, this is what you need:

Sheets("Sheet2").Range("A8").Value = "Choose answer")

Regards,
Per

 

[BrucenAZ]

Very close now! Macro works when in Visual Basic mode using:

Sheets("Sheet2").Range("A8").Value = Sheets("Sheet2").Range("A2").Select

Range A2 is obviously my "Choose Answer" selection in the drop-down list.

However, when I click the button in the app to activate the macro, I get
Run-time error 1004; Unable to get the Select property of the Range class.

Any thoughts? You've been a great help - thanks!

Bruce

 

[Per Jessen]

Hi Bruce

Thanks for your reply.

With Combobox1 on sheet1: LinkedCell = Sheet2!A2, ListFillRange =
Sheet2!A2:A6
CommandButton1 on sheet1, and the code below:

Private Sub CommandButton1_Click()
Sheets("Sheet2").Range("A8").Value = "Choose answer"
End Sub

When I press the button, the combobox i reset to "Choose answer"

Hopes it helps.

Regards,
Per