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

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)


"Matt Jensen" wrote in message
...
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

Looks beautiful - thank you very much Bob!
Cheers mate
Matt

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

Matt,

Did you ever get my tooltips code?

Bob


"Matt Jensen" wrote in message
...
Looks beautiful - thank you very much Bob!
Cheers mate
Matt

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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


"Bob Phillips" wrote in message
...
Matt,

Did you ever get my tooltips code?

Bob


"Matt Jensen" wrote in message
...
Looks beautiful - thank you very much Bob!
Cheers mate
Matt

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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

"Matt Jensen" wrote in message
...
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


"Bob Phillips" wrote in message
...
Matt,

Did you ever get my tooltips code?

Bob


"Matt Jensen" wrote in message
...
Looks beautiful - thank you very much Bob!
Cheers mate
Matt

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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).Addres s(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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

No errors either btw
matt

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

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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

' 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).Addres s(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

--
Regards,
Tom Ogilvy


"Matt Jensen" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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

"Tom Ogilvy" wrote in message
...
' 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).Addres s(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

--
Regards,
Tom Ogilvy


"Matt Jensen" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

This is written for a worksheet. There is no need to change it.

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
' 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).Addres s(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

--
Regards,
Tom Ogilvy


"Matt Jensen" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
' 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).Addres s(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

--
Regards,
Tom Ogilvy


"Matt Jensen" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)


"Matt Jensen" wrote in message
...
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

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
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" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

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" wrote in message
...
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" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
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.

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

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...

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
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.

--
Regards,
Tom Ogilvy


"Matt Jensen" wrote in message
...
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...

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

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" wrote in message
...
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" wrote in message
...
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...

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Matt Jensen" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy