Checkbox Appear where there is no data

[happygma]

I have added a checkbox to my spreadsheet, I would like to not have the checkbox appear unless there is data in the referenced cell...

Here is the code I used

ub AddCheckBoxToCell(Ref_Cell As Range)

Dim ChkBox As CheckBox
Dim N As Double

With Ref_Cell.Cells(1, 1)
refLeft = .Left
refTop = .Top
refHeight = .Height
End With

Set ChkBox = ActiveSheet.CheckBoxes.Add(10, 10, 15, 12)

N = (refHeight - ChkBox.Height) / 2#

With ChkBox
.Caption = ""
.Top = refTop + N
.Left = refLeft
.OnAction = ""
End With

End Sub

Sub AddCheckBoxes()

Dim BoxCol As Variant
Dim CtrlCol As Variant
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long

BoxCol = "B" 'Column where Check Box is inserted
CtrlCol = "A" 'Column that controls if Check Box is inserted
StartRow = 1
LastRow = Cells(Rows.Count, CtrlCol).End(xlUp).Row

For R = StartRow To LastRow
If Cells(R, CtrlCol) <> "" Then
AddCheckBoxToCell Cells(R, BoxCol)
End If
Next R

End Sub

[Tinbendr]

Please use code tags when posting code.

Do you mean if the value in column A is deleted?

[happygma]

Yes

there are really two things now that I want...

1. if the referenced cell "A" is blank then do not consider for a checkbox
2. If the referenced cell "A" is equal to "STEP 1" consider for a Checkbox

Thank you

[Tinbendr]

then do not consider for a checkbox

I'm not sure I understand this. The way the code is written now, if A is blank, no checkbox is added.

2. If the referenced cell "A" is equal to "STEP 1"

If you have the workds Step 1 in Coumna, then just add another test in the sub.

Please Login or Register  to view this content.

[happygma]

Well then...I thought that was true, I may be handling it wrong myself, it has been several years since I used VBA, used to have a staff that did this...

when I added the macro to my spreadsheet, it only put the checkbox in the first cell, so I copied that cell down instead of doing a range in the VBA...

so would I change this line of code to read differently?

With Ref_Cell.Cells(1, 1)

[Tinbendr]

If you have breaks in the data in column A, then the code used to determine LastRow may be stopping short. You can hard code it if you like.

Please Login or Register  to view this content.

Or, if you'd like to keep it dynamic, there are other alternatives.

[happygma]

I would like to keep it dynamic as I am using this in several spreadsheets in our QA world...would like for it to consider the entire column "A"

So what is the alternatives?

[Tinbendr]

Please Login or Register  to view this content.

[happygma]

Got an error message when I added below code..."Runtime Error '9': Subscript out of Range

LastRow = Sheets("Sheet1").Cells.Find(What:="*", _
After:=Sheets("Sheet1").Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

[happygma]

Figured it out...user error...

That worked!! Refreshing my memory, had to dust off the cob webs...Thank you

[happygma]

Have another issue, if I run the macro, then add items to "A" and rerun macro it adds the checkbox BUT

If I delete lines from "A" and rerun Macro it does not remove the Checkbox, do I need more to do that?

[Tinbendr]

Will you need to keep the current checked checkboxes?

Can we delete all the checkboxes and start over?

[happygma]

Yes would like to keep not do it all over...

I think I found a work around, if you delete the whole line it gets rid of the box as well, except the last one...

I will just put that in the instructions on how to run the macro....people using this think I am magical.... little do they know....

I am finished now, it works great...THank you for your help....