[SOLVED] Programmatically set Excel 97 Control Toobar Checkbox object & container nam

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:%23cg%[email protected]...
> Looks beautiful - thank you very much Bob!
> Cheers mate
> Matt
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:%[email protected]...
> Matt,
>
> Did you ever get my tooltips code?
>
> Bob
>
>
> "Matt Jensen" <[email protected]> wrote in message
> news:%23cg%[email protected]...
> > Looks beautiful - thank you very much Bob!
> > Cheers mate
> > Matt
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > Matt,
> >
> > Did you ever get my tooltips code?
> >
> > Bob
> >
> >
> > "Matt Jensen" <[email protected]> wrote in message
> > news:%23cg%[email protected]...
> > > Looks beautiful - thank you very much Bob!
> > > Cheers mate
> > > Matt
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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).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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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).Address(external:=True)
With cb1
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With

--
Regards,
Tom Ogilvy


"Matt Jensen" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:%23%[email protected]...
> ' 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Matt Jensen" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23%[email protected]...
> > ' 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Matt Jensen" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23%[email protected]...
> > ' 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Matt Jensen" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
news:%23m5gK%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23m5gK%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%23m5gK%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:%23m5gK%[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>