Hello All,

i am hoping somebody can help. I want to have the
LinkedCell property of a checkbox populated when i open a
worksheet. for example, i want the following to happen
when i open the worksheet: "checkbox1.linkedcell = a2"

any help would be appreciatted. Thanks

Did you set it before you saved the workbook? It should remain set.
--
Regards,
Bill


"dan" wrote in message
...
Hello All,

i am hoping somebody can help. I want to have the
LinkedCell property of a checkbox populated when i open a
worksheet. for example, i want the following to happen
when i open the worksheet: "checkbox1.linkedcell = a2"

any help would be appreciatted. Thanks

i have around 500 checkboxes i need to do this to and i
was hoping i could it programatically and not have to
touch each one. then after i did it the 1st time, i
could then save and remove the code.

thanks.

-----Original Message-----
Did you set it before you saved the workbook? It should
remain set.
--
Regards,
Bill


"dan" wrote in
message
...
Hello All,

i am hoping somebody can help. I want to have the
LinkedCell property of a checkbox populated when i
open a
worksheet. for example, i want the following to happen
when i open the worksheet: "checkbox1.linkedcell = a2"

any help would be appreciatted. Thanks



.

How did you get the 500 checkboxes installed? You can modify the following
demo program to suit your application. It creates a new workbook then adds
10 checkboxes each from both the Forms and Control toolbars (Excel 95 style
checkboxes and the newer ActiveX checkboxes). If you run the code in the
same workbook that you will be installing the ActiveX checkboxes, then you
will not be able to single-step through the code, because the addition of an
ActiveX control changes the code module of the workbook (due to the event
handlers that come with an ActiveX control), and this prevents Excel from
continuing in single-step mode (the macro will run to completion). You will
have to modify this code to place the number of checkboxes where you want
them. This demo does not place any captions next to the checkbox, since it
is assumed that the description would be in an adjacent worksheet cell
somewhere.


Option Explicit

Const NumCheckboxes = 10

'----------------------------------------------------------------------
Public Sub Main()
Dim wbNew As Workbook
Dim wsCheckboxDemo As Worksheet

Application.ScreenUpdating = False

Set wbNew = Workbooks.Add
Set wsCheckboxDemo = wbNew.Worksheets(1)
wsCheckboxDemo.Name = "Checkboxes"

AddFormsCheckboxes wsCheckboxDemo, 1
AddActiveXCheckboxes wsCheckboxDemo, NumCheckboxes + 2

Application.ScreenUpdating = True
End Sub

'----------------------------------------------------------------------
Public Sub AddFormsCheckboxes(ws As Worksheet, StartRow As Long)
Const CBLeftMargin = 2 'Space to leave betweeen the left
'edge of the checkbox and the cell.
Dim rngCheckboxLocation As Range 'Top left cell underneath the checkbox.
Dim rngLinkedCell As Range 'Cell where the checkbox will be linked to.

Dim lngCBNum As Long 'Checkbox counter to iterate with.
Dim chk As CheckBox

For lngCBNum = 1 To NumCheckboxes
'Define where the linked cell for the checkbox will be
'and initialize its value.
Set rngLinkedCell = ws.Cells(StartRow + lngCBNum - 1, 1)
rngLinkedCell.Formula = False

'Define where we want the checkbox to be.
Set rngCheckboxLocation = ws.Cells(StartRow + lngCBNum - 1, 2)

'Add the checkbox to the worksheet.
'Height of the underlying cell may be too small.
With rngCheckboxLocation
Set chk = ws.CheckBoxes.Add(.Left, .Top, .Width, .Height)
End With

'Set checkbox properties.
With chk
.Caption = ""
.LinkedCell = rngLinkedCell.AddressLocal
.Width = .Height

'Re-position the checkbox.
.Left = rngCheckboxLocation.Left + CBLeftMargin
If .Height < rngCheckboxLocation.RowHeight _
Then
'Re-center the vertical position of the checkbox in the cell.
.Top = rngCheckboxLocation.Top + _
(rngCheckboxLocation.RowHeight - .Height) / 2
Else
'Make the row height bigger to fit the checkbox.
rngCheckboxLocation.RowHeight = .Height
End If
End With
Next lngCBNum
End Sub

'----------------------------------------------------------------------
'WARNING: Do NOT single-step through this routine if the checkboxes
'are being added to the same workbook that this code is in!
'When the first checkbox is added with the OLEObjects.Add method,
'the addition of the event handlers changes the code module.
'Excel will display "Can't enter break mode at this time",
'with Continue and Stop buttons as the only options!
'Further debugging in single-step mode is not possible.

Public Sub AddActiveXCheckboxes(ws As Worksheet, StartRow As Long)
Const CBLeftMargin = 5 'Space to leave betweeen the left
'edge of the checkbox and the cell.
Dim rngCheckboxLocation As Range 'Top left cell underneath the checkbox.
Dim rngLinkedCell As Range 'Cell where the checkbox will be linked to.

Dim lngCBNum As Long 'Checkbox counter to iterate with.
Dim objOLEControl As OLEObject

For lngCBNum = 1 To NumCheckboxes
'Define where the linked cell for the checkbox will be
'and initialize its value.
Set rngLinkedCell = ws.Cells(StartRow + lngCBNum - 1, 1)
rngLinkedCell.Formula = False

'Define where we want the checkbox to be and activate,
'since this is where VBA places the checkbox, by default.
Set rngCheckboxLocation = ws.Cells(StartRow + lngCBNum - 1, 2)

'Add the checkbox to the worksheet.
Set objOLEControl = ws.OLEObjects.Add(ClassType:="Forms.CheckBox.1")

With objOLEControl
'Set properties common to all OLE Objects.
.LinkedCell = rngLinkedCell.AddressLocal

'Re-position the checkbox.
.Left = rngCheckboxLocation.Left + CBLeftMargin
If .Height < rngCheckboxLocation.RowHeight _
Then
'Re-center the vertical position of the checkbox in the cell.
.Top = rngCheckboxLocation.Top + _
(rngCheckboxLocation.RowHeight - .Height) / 2
Else
'Make the row height bigger to fit the checkbox,
'then reposition the checkbox.
rngCheckboxLocation.RowHeight = .Height
.Top = rngCheckboxLocation.Top
End If

'Now set properties specific to the checkbox.
With .Object
.AutoSize = True
.Caption = ""
.TripleState = False
End With
End With
Next lngCBNum
End Sub
--
Regards,
Bill

wrote in message
...
i have around 500 checkboxes i need to do this to and i
was hoping i could it programatically and not have to
touch each one. then after i did it the 1st time, i
could then save and remove the code.

thanks.