VBA Change .List Range of Drop Down Box

ArthurReyes

Board Regular
Joined
Dec 9, 2002
Messages
77
I have the following Code
The first Part copies a horizontal range and transposes it in B510

C509 reads a cell that counts the total # of entries in the list.

My difficulty comes at the end, when I am trying to select the list box and change the listfillrange for the content.

The list box does get selected successfully, but the listfillrange is not modified.

Also, I would like to know how to deselect the Drop Down Box after I have modified it.

'Rebuild Skills List
Sheets("AdminSkillsProfessions").Range("W1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("AdminSkillsProfessions").Range("B510").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

x = Sheets("AdminSkillsProfessions").Range("C509").Value

ActiveWorkbook.Sheets("UserForm").Shapes("Drop Down 1").Select
With Selection
ListFillRange = "AdminSkillsProfessions!$B$510:$B$" & x
End With
This message was edited by ArthurReyes on 2002-12-13 14:28
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Ah, hang on- you would need to activate the worksheet first with your code, try this variation and amend to suit your worksheet names and ranges:-

Sheets("Sheet3").Shapes("Drop Down 1").ControlFormat.ListFillRange = "Sheet2!$B$1:$B$3"

Personally, I would stick to using controls from the toolbox menu, much easier to manipulate in code as they expose more properties.
 
Upvote 0
I have to select the stupid sheet first.

Sheets("Attributes").Select

ActiveSheet.Shapes("Drop Down 1").Select
With Selection
.ListFillRange = "AdminSkillsProfessions!$B$510:$B$" & x
End With

however, I don't know how to deselect the object when I am done
 
Upvote 0
You won't need to select the sheet if you use the piece of code above. In fact it's quite rare that you will have to select or activate anything using VBA.
 
Upvote 0
Hi Mudface,

It is true,you don't need to select the sheet for the code to work. However you MUST ACTIVATE the sheet if it not active.

OK we have found a way around the problem trough trial and error which is great but we still don't know the logic behind this
VBA code behaviour.

So the question is -Why can we not set the ListFillRange property with the code below when the sheet on which the control is embeded is not active !!?:

Sheet(1).shapes("DROP DOWN 1").ListFillRange="Sheet1!$A$1:$A$10")

I just can't spot anything wrong with this code.

By the way,this also applies to controls from the Control toolbox.

Does anyone out there have an explaination as to why this happens?

Thank you.

Jaafar.
 
Upvote 0
On 2002-12-14 23:43, rafaaj2000 wrote:
Hi Mudface,

It is true,you don't need to select the sheet for the code to work. However you MUST ACTIVATE the sheet if it not active.

OK we have found a way around the problem trough trial and error which is great but we still don't know the logic behind this
VBA code behaviour.

So the question is -Why can we not set the ListFillRange property with the code below when the sheet on which the control is embeded is not active !!?:

Sheet(1).shapes("DROP DOWN 1").ListFillRange="Sheet1!$A$1:$A$10")

I just can't spot anything wrong with this code.

By the way,this also applies to controls from the Control toolbox.

Does anyone out there have an explaination as to why this happens?

Thank you.

Jaafar.

1st you don't need to Activate the sheet
to change an Objects property.

2nd The code you supplied will not change it
as the syntax is wrong
The code you gave has an extra ")" but even with this removed the object does not support that syntax

Here is what worked for me Xl2000 on any sheet...<pre/>
Sub Tester()
Sheets(2).Shapes("DROP DOWN 1").ControlFormat.ListFillRange = "Sheet1!$A$1:$A$10"
End Sub</pre>

You must use the Shapes ControlFormat property to change the property of a control
from the Forms commandbar.




_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails
Auck.gif

This message was edited by Ivan F Moala on 2002-12-15 00:22
 
Upvote 0
Thanks Ivan.
The code does work with the Controlformat property but the object I was experimenting with on my computer was actually a ComboBox from the Control ToolBox not a drop down from the forms box.

Do you know how to carry out the same task on a combobox . I tried the Controlformat but it does not support this property ?


Thank you.
Jaafar.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top