gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 351
- Office Version
- 365
- Platform
- Windows
- Mobile
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:
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:
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.
I refer to each checkbox by its caption which is the absolute address of the cell it resides in. Check out this code:
VBA 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:
VBA Code:
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.