Programmatically set Excel 97 Control Toobar Checkbox object & container name

M

Matt Jensen

Howdy
Need in Excel 97 to programmatically create some control toolbar checkboxes
and set both (I think the terminology is) the object and container names to
the same name "cb_r" & i & "c" & j
What code do I need to add to my snippet of my code below to set both names?
Thanks
Matt

Set cb =
ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=intLeftLocation, _
Top:=cellUnder.Top + 2, _
Width:="13.5", _
Height:="15", _
DisplayAsIcon:=False)
With cb 'add other info
.Name = "cb_r" & i & "c" & j 'name the checkbox with
it's row and column number
.LinkedCell =
Worksheets("Data-PMProducts-LinkedCells"). _
Range("anchorpoint_LinkedCells").Cells(i,
j).Address(external:=True)

.Placement = xlMove ' This lets each check box stay
with its row during sorts. NEEDED???
With .Object
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With
 
B

Bob Phillips

Matt,

Is this what you want?

With cb 'add other info
.Name = "cb_r" & i & "c" & j 'name the checkbox with
it 's row and column number
ws.OLEObjects(.Name).Name = .Name
....

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
M

Matt Jensen

Ahh yes I did thanks Bob
You actually sent it to a different email address to the first time you sent
it and hence I missed it until I just checked now, but got it now, will have
to go thru it yet, looks good though - thanks very much Bob
Cheers
Matt
 
B

Bob Phillips

I thought that might be the case as I lost your email request and found the
address I posted to somewhere else. Post directly if you have any problems.

Regards

Bob
 
M

Matt Jensen

Hey Bob
Adjusted code as advised and it doesn't seem to be taking effect in xl97...?

Code looks like this:

Set cb =
ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=intLeftLocation, _
Top:=cellUnder.Top + 2, _
Width:="13.5", _
Height:="15", _
DisplayAsIcon:=False)
With cb 'add other info
.Name = "cb_r" & i & "c" & j
ws.OLEObjects(.Name).Name = .Name
.LinkedCell =
Worksheets("Data-PMProducts-LinkedCells"). _

Range("anchorpoint_LinkedCells").Cells(i,j).Address(external:=True)
With .Object
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

It's definitely not taking effect, and code is definitely all being executed
(I changed the .Name = "cb_r" & i & "c" & j to .Name = "NewName_cb_r" & i
& "c" & j ) to make sure and this name change did occur but in the
properties dialogue it is only still saying checkbox1 etc. for the name
attribute of my checkboxes...
Any ideas?
Thanks
matt
 
M

Matt Jensen

Try this in a blank workbook in xl97 - doesn't work either...
Sub test()
Dim cb As OLEObject
Dim ws As Worksheet

Set ws = Worksheets("Sheet1") 'Sheet1
ws.Activate
ws.Range("A1").Activate

Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=20, _
Top:=20, _
Width:=13.5, _
Height:=15, _
DisplayAsIcon:=False)
With cb
.Name = "CheckboxName"
ws.OLEObjects(.Name).Name = .Name
End With
End Sub

Matt
 
T

Tom Ogilvy

' declarations are important
Dim cb as OleObject
Dim cb1 as MsForms.Checkbox

Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=intLeftLocation, _
Top:=cellUnder.Top + 2, _
Width:="13.5", _
Height:="15", _
DisplayAsIcon:=False)
set cb1 = cb.Object
cb1 .Name = "cb_r" & i & "c" & j
cb.Name = cb1.Name
cb.LinkedCell = _
Worksheets("Data-PMProducts-LinkedCells"). _
Range("anchorpoint_LinkedCells").Cells(i,j).Address(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With
 
M

Matt Jensen

Thanks Tom
Sorry I don't know the equivalent of :

Dim cb1 as MsForms.Checkbox

when on a worksheet instead of a userform (as in my case)?
Thanks
Matt
 
T

Tom Ogilvy

Here is a tested example that does what you want. I modified some of your
original code since I didn't want to try and recreate your layout.

If you get an error on the line
Dim cb1 as MsForms.Checkbox

then go to Tools=>References and create a reference to the MSforms 2.0
library. Or insert a userform and the reference will be created
automatically. This is really more applicable in later versions of excel -
xl97 should already have the reference.

Sub Tester2()
Dim cb As OLEObject
Dim cb1 As MSForms.CheckBox
Dim i As Long, j As Long
Dim ws As Worksheet, obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
obj.Delete
Next
Set ws = ActiveSheet
For i = 3 To 4
For j = 4 To 8 Step 3
Set cell = Cells(i, j)
Set cb = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=cell.Left, _
Top:=cell.Top, _
Width:=cell.Width, _
Height:=cell.Height, _
DisplayAsIcon:=False)
Set cb1 = cb.Object
cb1.Name = "cb_r" & i & "c" & j
cb.Name = cb1.Name
cb.LinkedCell = _
ws.Cells(i, j).Address(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With

Next j
Next i

End Sub
 
M

Matt Jensen

Great thanks Tom - your were right.
Guess I'll have to programmatically add and delete a form for the later
versions of excel to eliminate this problem when they are used...
Cheers
Matt
 
T

Tom Ogilvy

In later versions of Excel you shouldn't have to do it. Excel automatically
synchronizes the Name property of the Control and the OleObject container.

--
Regards,
Tom Ogilvy

Matt Jensen said:
Great thanks Tom - your were right.
Guess I'll have to programmatically add and delete a form for the later
versions of excel to eliminate this problem when they are used...
Cheers
Matt
Click to expand...
 
M

Matt Jensen

I am aware of the synchronisation however since this app will be deployed on
both 97 and up at the same time then using the code you kindly provided I'll
need to either wrap it with a xl-version-if-statement or programmatically
add a form won't I - I guess the former is preferable though.

quick google search showed some code to use of:
If Val(Application.Version) <= 8 Then
' user is in 97 or earlier
End If


Matt

Tom Ogilvy said:
In later versions of Excel you shouldn't have to do it. Excel automatically
synchronizes the Name property of the Control and the OleObject container.
Click to expand...
 
T

Tom Ogilvy

References are maintained at the workbook level. So if you have the
reference set in xl97, when you send the file to another version, it should
cause the reference to be set to the latest version of that reference.

So I don't think you need to do anything. The code will do the
synchronization, but it should only be slightly redundant in later versions,
so probably not worth doing anything special. You will still want to rename
the checkboxes in all versions - running the code unaltered/unsupplemented
for version of excel should achieve that.
 
M

Matt Jensen

I'm confused Tom, but if my understanding of terminology is correct then the
reference may be set in either 97 or 2002, and that code "broke" in 2002
with default settings which I won't be able to alter on other
workstations...
 
T

Tom Ogilvy

To simplify:
do your development in xl97. as part of that, set the reference.

Now you should be able to use the workbook/code in any version, xl97 or
later.

I don't know what ["broke" in 2002] means.
 
M

Matt Jensen

Thanks very much for your patience Tom! :)

Sorry mate, after finally getting the app back to the all-XP machine and
testing it the code all works great thanks!!!
Was too worried about it not working that I didn't actually test the end
product on the machine in question!!

My brain and eyes are seriously fried from so much time spent on this, sorry
for this.

THanks very much

Matt


Tom Ogilvy said:
To simplify:
do your development in xl97. as part of that, set the reference.

Now you should be able to use the workbook/code in any version, xl97 or
later.

I don't know what ["broke" in 2002] means.

--
Regards,
Tom Ogilvy


Matt Jensen said:
I'm confused Tom, but if my understanding of terminology is correct then the
reference may be set in either 97 or 2002, and that code "broke" in 2002
with default settings which I won't be able to alter on other
workstations...

versions
of
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Programmatically add control to worksheet. 3
Multiple Checkboxes 3

Top