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
ublishsource="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