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
 
On 2002-12-15 00:31, rafaaj2000 wrote:
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.

<pre/>
Sub TTT()
Sheet1.ComboBox1.ListFillRange = "Sheet1!$A$1:$A$10"
End Sub
</pre>
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Thanks Ivan,

Basically,it seems that controls embeded in worksheets from the Control Tool Box have the same properties and methods as those you create in UserForms.

Are there any Differences between the two ?


Also while we are on the subject I would like to know what the name of the collection of sheet controls is (From the control tool box).

I have a ClassModule code that needs looping through all the embeeded controls in a worksheet and assign new custom properties to them.
At the moment i am using the worksheet.shapes collection to achieve this but without any luck.

Note that the Shapes collection is the one that the Macro Recorder shows but for some reason it doesn't work when editing a procedure.

I wuld very much appreciate help on this one as this is is very important stuff.


Thanks a lot.

Jaafar.
 
Upvote 0
On 2002-12-15 01:30, rafaaj2000 wrote:
Thanks Ivan,

Basically,it seems that controls embeded in worksheets from the Control Tool Box have the same properties and methods as those you create in UserForms.

Are there any Differences between the two ?

They are basically one and the same ie.
ActiveX controls sourced from the same Dll
The Forms2.0 dll. These controls are genrally the more prefered to work with on
a worksheet as they offer more control
programtically and also because they offer
more properties as you would expect with any
ActiveX control.

Also while we are on the subject I would like to know what the name of the collection of sheet controls is (From the control tool box).

I have a ClassModule code that needs looping through all the embeeded controls in a worksheet and assign new custom properties to them.
At the moment i am using the worksheet.shapes collection to achieve this but without any luck.

Note that the Shapes collection is the one that the Macro Recorder shows but for some reason it doesn't work when editing a procedure.

I wuld very much appreciate help on this one as this is is very important stuff.


Thanks a lot.

Jaafar.

These controls belong to the ClassType:="Forms"XXX where XXX is the control type eg commandbutton
They are OLEObjects
Place some controls on your sheet and
try running this;


<pre/>
Sub Tester()
Dim Shp As Shape
Dim x As Integer
x = 2
On Error Resume Next
For Each Shp In ActiveSheet.Shapes
Cells(x, 1) = Shp.Name
Cells(x, 2) = Shp.FormControlType ' .OLEType
Cells(x, 3) = Shp.Type ' .ProgId
Cells(x, 4) = Shp.AutoShapeType
Cells(x, 5) = Shp.OLEFormat.ProgId
Cells(x, 6) = Shp.OLEFormat.Creator
x = x + 1
Next
End Sub
</pre>



You should get something like;

<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./ole_files/filelist.xml" rel=File-List><STYLE>v:* {behavior:url(#default#VML);}o:* {behavior:url(#default#VML);}x:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</STYLE><STYLE id=Book1_9371_Styles></STYLE>  <DIV id=Book1_9371 align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 489pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=652 border=0 x:str><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl229371 style="WIDTH: 84pt; HEIGHT: 13.5pt" width=112 height=18>Name</TD><TD class=xl239371 style="WIDTH: 57pt" width=76>Formstype</TD><TD class=xl239371 style="WIDTH: 55pt" width=73>shapetype</TD><TD class=xl239371 style="WIDTH: 78pt" width=104>Autoshapetype</TD><TD class=xl239371 style="WIDTH: 119pt" width=159>OLEFormat.ProgId</TD><TD class=xl249371 style="WIDTH: 96pt" width=128>OLEFormat.Creator</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl259371 style="HEIGHT: 12.75pt" height=17>ComboBox1</TD><TD class=xl269371> </TD><TD class=xl269371 x:num>12</TD><TD class=xl269371 x:num>-2</TD><TD class=xl269371>Forms.ComboBox.1</TD><TD class=xl279371 x:num="1480803660">1480803660</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl289371 style="HEIGHT: 12.75pt" height=17>ComboBox2</TD><TD class=xl299371> </TD><TD class=xl299371 x:num>12</TD><TD class=xl299371 x:num>-2</TD><TD class=xl299371>Forms.ComboBox.1</TD><TD class=xl309371 x:num="1480803660">1480803660</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl259371 style="HEIGHT: 12.75pt" height=17>ComboBox3</TD><TD class=xl269371> </TD><TD class=xl269371 x:num>12</TD><TD class=xl269371 x:num>-2</TD><TD class=xl269371>Forms.ComboBox.1</TD><TD class=xl279371 x:num="1480803660">1480803660</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl289371 style="HEIGHT: 12.75pt" height=17>Drop Down 4</TD><TD class=xl299371 x:num>2</TD><TD class=xl299371 x:num>8</TD><TD class=xl299371 x:num>-2</TD><TD class=xl299371>Forms.ComboBox.1</TD><TD class=xl309371> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl259371 style="HEIGHT: 12.75pt" height=17>Button 5</TD><TD class=xl269371 x:num>0</TD><TD class=xl269371 x:num>8</TD><TD class=xl269371 x:num>-2</TD><TD class=xl269371>Forms.CommandButton.1</TD><TD class=xl279371> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl289371 style="HEIGHT: 12.75pt" height=17>ScrollBar1</TD><TD class=xl299371> </TD><TD class=xl299371 x:num>12</TD><TD class=xl299371 x:num>-2</TD><TD class=xl299371>Forms.ScrollBar.1</TD><TD class=xl309371 x:num="1480803660">1480803660</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl259371 style="HEIGHT: 12.75pt" height=17>Rectangle 8</TD><TD class=xl269371 x:num>2</TD><TD class=xl269371 x:num>1</TD><TD class=xl269371 x:num>1</TD><TD class=xl269371> </TD><TD class=xl279371 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl289371 style="HEIGHT: 12.75pt" height=17>AutoShape 9</TD><TD class=xl299371 x:num>0</TD><TD class=xl299371 x:num>1</TD><TD class=xl299371 x:num>17</TD><TD class=xl299371> </TD><TD class=xl309371> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl259371 style="HEIGHT: 12.75pt" height=17>CheckBox1</TD><TD class=xl269371> </TD><TD class=xl269371 x:num>12</TD><TD class=xl269371 x:num>-2</TD><TD class=xl269371>Forms.CheckBox.1</TD><TD class=xl279371 x:num="1480803660">1480803660</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl289371 style="HEIGHT: 12.75pt" height=17>Spinner 11</TD><TD class=xl299371 x:num>9</TD><TD class=xl299371 x:num>8</TD><TD class=xl299371 x:num>-2</TD><TD class=xl299371> </TD><TD class=xl309371> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl319371 style="HEIGHT: 13.5pt" height=18>CommandButton1</TD><TD class=xl329371 x:num>9</TD><TD class=xl329371 x:num>12</TD><TD class=xl329371 x:num>-2</TD><TD class=xl329371>Forms.CommandButton.1</TD><TD class=xl339371 x:num="1480803660">1480803660</TD></TR></TBODY></TABLE></DIV>


From this you can see what the controls are
 
Upvote 0
Re: VBA loop through a dropdownlist of tables

i am having a similar problem with one of my projects....
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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