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 |
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 |
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 |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com