Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check box Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check box Error
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 |
#3
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime 9 error Check. | Excel Worksheet Functions | |||
Error Check Dates | Excel Discussion (Misc queries) | |||
If statement error check | Excel Worksheet Functions | |||
Error check on sheet | Excel Programming | |||
Error check and resume | Excel Programming |