Is my condition's syntax wrong or is this an Excel bug?

[gravanoc]

It's been a bizarre couple of days trying to create some checkboxes via script and then automate them to use the click event. I'm concerned that something may be wrong with the workbook, possible corruption, or maybe I'm just overlooking something. I just don't want a serious issue causing me to backtrack.

I refer to each checkbox by its caption which is the absolute address of the cell it resides in. Check out this code:

Sub DeleteCheckboxes()
    
    Dim optSht As Worksheet: Set optSht = Sheets("Options")
    
    For Each cb In optSht.OLEObjects
    
        On Error Resume Next
        Debug.Print (cb.Name)
        Debug.Print (cb.Object.Caption)
        If Left(cb.Object.Caption, 1)= "$" Then cb.Delete
        On Error GoTo 0
        
    Next
    
End Sub

Pretty simple, I thought, and it used to work without a hitch. Then randomly, it decided that a listbox called NameBox on the same tab met that criteria & would delete it. Now I've changed it to:

        If Left(cb.Name, 1)= "$" Then cb.Delete

This was working, although while stepping through the code to ensure no more deletions occur, it says "Can't Enter break mode at this time.", and promptly zips through the rest of the code. Later it decides that my new condition is also TRUE for my NameBox, yet again deleting it. I even threw in "And cb.Name <> 'NameBox' " in hopes of squashing this, but ... deleted. True enough, its name is NameBox.

In another, related module, I experienced another inexplicable error. I decided to turn on the option to enforce data type checking (this preceded the NameBox issue as well). I'm not sure if it was related to this option change. The checkboxes occupy a non-contiguous range named Stations. A subset of this range is called CurrentStations and is used to determine which cells to highlight for UI convenience because each checkbox has an owner, corresponding to one of the workbook's tabs. This 1004 error kept breaking on a line:
VBA Code:
.RefersToRange = optSht.Range(newRng.Address)
It said that the naming convention was incorrect for something in that line, or that it was a duplicate of another variable in scope. Neither seems to be true. Any idea what could cause this? I was able to make it accept the code by removing "optSht.", but it later stopped on the edited code and complained again.

 

[GWteB]

it says "Can't Enter break mode at this time."

This is no bug. It's explicable from the fact, that you are programmatically (!!) making changes to your VBA project, since you're adding or deleting ActiveX elements to your project.

Regarding your second issue, provided optSht and newRng are valid objects it should work. Hard to tell what's causing it without seeing the code involved and the contents of the names collection.
(sounds almost the same as the bottom line of my reply to your previous post)

 

[Gokhan Aycan]

You can check for textbox type like this:

If InStr(UCase(cb.progID), "TEXTBOX") > 0 Then

Another point would be text compares are case sensitive normally, unless you "Option Compare Text" at module start
. MS Option Compare Doc

 

[gravanoc]

This is no bug. It's explicable from the fact, that you are programmatically (!!) making changes to your VBA project, since you're adding or deleting ActiveX elements to your project.

Regarding your second issue, provided optSht and newRng are valid objects it should work. Hard to tell what's causing it without seeing the code involved and the contents of the names collection.
(sounds almost the same as the bottom line of my reply to your previous post)

Apologies, I must have missed your other post.