Define the Range of a drop-down list
 

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

Define the Range of a drop-down list
 

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

"BrucenAZ" skrev i meddelelsen
...
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

Define the Range of a drop-down list
 

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" wrote:

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

"BrucenAZ" skrev i meddelelsen
...
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

Define the Range of a drop-down list
 

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" skrev i meddelelsen
...
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" wrote:

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

"BrucenAZ" skrev i meddelelsen
...
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

Define the Range of a drop-down list
 

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" wrote:

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" skrev i meddelelsen
...
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" wrote:

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

"BrucenAZ" skrev i meddelelsen
...
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

Define the Range of a drop-down list
 

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

"BrucenAZ" skrev i meddelelsen
...
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" wrote:

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" skrev i meddelelsen
...
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" wrote:

Hi Bruce

try this:

Me.ComboBox1.ListIndex = 0

Regards,
Per

"BrucenAZ" skrev i meddelelsen
...
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