Posted to microsoft.public.excel.programming
|
|
check box Error
Thanks Dave, got this one to work
"Dave Peterson" wrote:
You could try changing:
Shapes(Shapes.Count).ControlFormat.LinkedCell ...
to
Activesheet.Shapes(activesheet.Shapes.Count).Contr olFormat.LinkedCell ...
But here's another version (from a saved post):
Here are two subroutines. The first one adds a bunch of checkboxes from the
Forms toolbar to a range in the activesheet (b3:B10).
The second one adds one to the cell to the right (C3:C10) each time you check
the box.
The first one only needs to be run once--to set up the checkboxes on the
worksheet.
Option Explicit
Sub testme()
Dim myCBX As CheckBox
Dim myCell As Range
With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B10").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
'.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub
Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
If myCBX = xlOn Then
'do something
Else
'do something else
End If
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
You may not need the macro DoTheWork. You can delete it and delete the
..onaction line, too.
John wrote:
Here is the code I found on this site... and get and error on the last
line... starts with Shapes(Shapes.Count......
Any Ideas? Thanks so much!
Sub AddCheckBoxes()
'Add A Forms CheckBox
Dim Cell As Range
Dim Col As String
Dim LinkCol
Dim R As Long
Dim Value
Col = "B"
Value = "Mark"
LinkCol = "D"
For R = 1 To ActiveSheet.Cells(Rows.Count, Col).Row
Set Cell = ActiveSheet.Cells(R, Col)
If Cell.Value = Value Then
With ActiveSheet.Shapes
.AddFormControl xlCheckBox, Cell.Left, Cell.Top, Cell.Width,
Cell.Height
End With
Shapes(Shapes.Count).ControlFormat.LinkedCell = Cell.(R,
LinkCol).Address
End If
Next R
End Sub
--
Dave Peterson
|