PDA

View Full Version : [SOLVED] Create checkbox



slamet Harto
09-23-2008, 01:20 AM
Hi there,

I'm trying to create a checkbox by looping in a column. However, I can see the following error message "Object Variable with block variable not set".

Can you help to solve it.
Thanks for everything. Rgds, harto

Here's what I have done, so far:

Sub LoopToCreateChkbox()
Dim i As Integer
Dim c As Range
Range("C2").Select
For i = 1 To Selection.CurrentRegion.Rows.Count - 1
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = "Check" & c.Address
End With
Next i
ActiveCell.Offset(1, 0).Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6
.FormatConditions(1).Interior.ColorIndex = 6
.Font.ColorIndex = 2
End With
End Sub

Bob Phillips
09-23-2008, 01:44 AM
You are trying to use some variable c, which you don't load, as you are iterating through the rows.

It is not clear to me what you are trying to do. I can see that you are trying to create a checkbox per row, and add CF to test that checkbox, but it is not clear which cells, columns, youwish to apply it to.

slamet Harto
09-23-2008, 03:03 AM
Hi Bob,

If the c variable is useless, you can change it. As i don't have it.
I just copied the code from my library, then tried to implemented it.

You are right, I'm trying to create a checkbox per row in col C.
Please find the attached for your reference.

Can you help to give me an alternate solution.
Thanks & Rgds, Harto

Bob Phillips
09-23-2008, 03:56 AM
Sub LoopToCreateChkbox()
Dim i As Long
Dim cb As CheckBox
With ActiveSheet.Range("C2")
For i = 1 To .CurrentRegion.Rows.Count - 1
Set cb = ActiveSheet.CheckBoxes.Add(.Cells(i, 1).Left, .Cells(i, 1).Top, .Cells(i, 1).Width, .Cells(i, 1).Height)
cb.LinkedCell = .Cells(i, 2).Address
cb.Characters.Text = ""
cb.Name = "Check" & .Cells(i, 1).Address
With .Cells(i, 1).Offset(0, 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=" & .Cells(i, 1).Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 6
.FormatConditions(1).Interior.ColorIndex = 6
.Font.ColorIndex = 2
End With
Next i
End With
End Sub

Slyboots
09-23-2008, 11:07 AM
You can also streamline the code a bit, like this:


With ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height)
.LinkedCell = c.Address
.Characters.Text = ""
.Name = "Check" & c.Address
End With

slamet Harto
09-23-2008, 07:15 PM
Hi Bob,

Work well. Thanks a lot.

Have a great day!
Best, Harto