Delete an OLE Check Box in COde when a deleting a row

[BG Lad]

Functionality: To Remove an OLE check box in the row when the user deletes a
row.
This is my current attempt. I get an error message "Can't enter break mode
at this time".
The CheckBox is not linked, it is for visual and print only. There are
remmed statements from previous attempts. Any ideas on this one?

'** Snipped ******************
Dim rowcount As Long
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Worksheet_Change

Dim llCanDeleteTheBox As Boolean
Dim lcCheckBoxToDelete As String
'Application.EnableEvents = False 'should be part of Change macro

If IsEmpty(rowcount) Then
'* First time through ... *'
Else

If ActiveSheet.UsedRange.Rows.Count = rowcount - 1 Then
'* A row was deleted *'

lcCheckBoxToDelete = "CheckBox" + Trim(Str(ActiveSheet.Range("A"
+ Trim(Str(Target.Row))).Value))

For Each Obj In ThisWorkbook.ActiveSheet.OLEObjects
'* Find the Relative Checkbox *'
Stop
If Obj.Name = lcCheckBoxToDelete Then
Stop
'Obj.Delete
llCanDeleteTheBox = True
'lcCheckBoxToDelete = Obj.Name
'ThisWorkbook.ActiveSheet.OLEObjects(Obj.Name).Delete
Exit For
End If
Next
End If
End If

If llCanDeleteTheBox Then
Stop
'* Delete the Check Box *'
ThisWorkbook.ActiveSheet.OLEObjects(lcCheckBoxToDelete).Delete
End If

rowcount = ActiveSheet.UsedRange.Rows.Count
'** End of SNIPPED *****************

 

[Dave Peterson]

I didn't test your code, but when you step through code that works with
OLEObjects, you can get that error.

I'd put a break point above the row that does the work and after the row. Then
RUN (not Step) through the code.

Some other notes:

#1. VBA's syntax is very nice, you could use:

lcCheckBoxToDelete = "CheckBox" _
+ Trim(Str(ActiveSheet.Range("A" + Trim(Str(Target.Row))).Value))

lcCheckBoxToDelete = "CheckBox" & me.cells(target.row,"A").value)
or
lcCheckBoxToDelete = "CheckBox" & me.range("A" & target.row).value)

#2. VBA is forgiving, but it's better to use + to add numbers and & to
concatenate strings. You could get in trouble if the strings look like numbers
and they're added, not concatentated.

#3. Since you're in the worksheet_change event, it's better to use the keyword
Me instead of Activesheet. Me refters to the thing that owns the code. In this
case, it's the worksheet.

#4. You can check for a single/entire row being deleted by:
if target.address = target.cells(1).entirerow.address then

And check for multiple rows
if target.address = target.entirerow.address then

#5. If you know the name of the single checkbox to delete--but not sure if it's
there, you can use:

on error resume next
me.oleobjects(lccheckboxtodelete).delete
on error goto 0

#6. You may want to consider an alternative. Maybe give the user a dedicated
macro that would delete the rows (and checkboxes). Then you don't have to
struggle with the worksheet_event.

#7. In fact, you may want to rethink the checkboxes and use something
else/easier.

(saved from a previous post)

Select the range that would have held the checkboxes.
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings (make it as large as you want)

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:
=if(a1="","no checkmark","Yes checkmark")

Or you can filter by blanks and non-blanks.

 

[BG Lad]

Great Job Dave!
I am using the plus sign as a habit, I prefer it as it errors well with nulls.
I did use the On Error Resume Next, and the "Me.", and thanks for the
multi-row sensor, I will be using that too. I had thought of it but was not
there yet.
The spreadsheet is being created programatically so the reliability of the
cell contents is very high.
I put the 'Stop's after the .Delete and that seems to have been the problem.
Appreaciated muchly,
Bill

 

[BG Lad]

Any quickies on stepping through the deleted rows?

 

[BG Lad]

Guessed it, it is ...
For Each ER In Me.Rows(Target.EntireRow.Address)
Next
Thanks again, great motivation!
Happy New Year to ALL
Bill
--
B Good Lad

Any quickies on stepping through the deleted rows?

 

[Dave Peterson]

If they're gone, you won't be able to step through them.

But you could use the row numbers in the target range to delete the checkboxes.

dim iCtr as long
'your check to see if the target is the entire range here
'and your code to check to see if the rows were deleted

if thatIsTrue then
with target
for ictr = .row to .rows(.rows.count).row
'determine the checkbox name here
lccheckboxtodelete = "CheckBox" & me.cells(ictr,"A").value)
on error resume next
me.oleobjects(lccheckboxtodelete).delete
on error goto 0
next ictr
end with
end if


==
Untested and uncompiled.

Any quickies on stepping through the deleted rows?