Renaming form menu checkboxes on sheet from a userform control

[deepfriedcheese]

I have a questionnaire that has a few hundred checkboxes (not ActiveX checkboxes) that users complete as a part of our quality control process. Depending on the nature of the job different boxes need to be available, so each checkbox has been named according to when it is to be displayed. An example of this naming convention is "01b-IR-MgrYN-xBx-GYN". Each of the 5 portions of the name are used to determine whether it is visible. As you can imagine, this is a royal pain when a new step is added and what used to be checkboxes 02a... through 82d... all have to be renamed to 03a... through 83d...

I have created a userform (running in vbModeless) that I want to use to automate this process, but when I try to update the name of a checkbox from the private sub, I get error 70, permission denied. So I put a sub in a public module to handle the renaming and called it from the private sub using public variables. No luck, same error. Here is a code snippet that is attempting to change 01b-IR-MgrYN-xBx-GYN to 01b-IR-MgrYN-ABx-GYN. A very minor, but important change in the fourth criteria.

My public variables are:

Public currentactivename As String
Public newactivename As String

A checkbox on the userform indicates that Jobtype "A" should be present and runs this code:

Private Sub JobTypeCBA_Click()
currentactivename = UserForm1.ActiveName.Value
If UserForm1.JobTypeCBA.Value = True Then
    UserForm1.ActiveJobType.Value = "A" & Right(UserForm1.ActiveJobType, 2)
Else
    UserForm1.ActiveJobType.Value = "x" & Right(UserForm1.ActiveJobType, 2)
End If
newactivename = UserForm1.ActiveLineNumber & "-" & _
        UserForm1.ActiveColumnType & "-" & UserForm1.ActiveManagerIndicator & "-" & _
        UserForm1.ActiveJobType & "-" & UserForm1.ActiveGroup
Module2.RenameBoxes
End Sub

At the end of that code I have the follwing values in the public variables:

currentactivename = 01b-IR-MgrYN-xBx-GYN
newactivename = 01b-IR-MgrYN-ABx-GYN

The RenameBoxes macro is very simple:

Sub RenameBoxes()
Sheets("Questionnaire").Shapes(currentactivename).Name = newactivename
End Sub

Sheets("Questionnaire").Shapes(currentactivename).Name = newactivename is where I get the error. I know the variables are in place because I've tracked them through. I can run this code on its own and it works. But when the userform is active, I can't seem to get it to work inside the private module or in the public one.

This doesn't seem like it should be that hard, so I'm sure I'm missing something simple. Any ideas?

 

[sijpie]

Why does the renaming have to be done with the userform active?

I would just have a simple loop starting from the last name (so you can't create double names) and increase its number-name until you get to the number that is to be inserted.

 

[deepfriedcheese]

Why does the renaming have to be done with the userform active?

I don't suppose it has to be, but I'd need to reload the userform at the end of each name change. The nature of changes to this questionnaire are such that several changes occur after process reviews. It's never as easy as just adding one box or changing the visibility criteria of a single box, so I figured it would be quickest for the user if the userform didn't have to reload every time.

I would just have a simple loop starting from the last name (so you can't create double names) and increase its number-name until you get to the number that is to be inserted.

I will do just that in the code to add new checkboxes, but quite often we only need to adjust the visibility criteria of existing boxes. I just can't get it to rename anything.

 

[deepfriedcheese]

Anyone have any ideas for me?

 

[ChrisM]

Can't help solve your issue, but I have a couple thoughts:

1. When you start messing with forms and controls, not everything works cleanly. Little software bugs and idiosyncrasies will pop up and you just have to deal with them. For example I was using a spin button to do something, but it forced a 2 second recalc delay every time I clicked it, so I had to change to a regular command button and calcs are instant now.

2. I wouldn't consider it a good practice to rely so heavily on the name of a control to be so important. Had you used ActiveX checkboxes I would have suggested you leave the name alone and store your 5 word code in the Tag property. But since you are already committed, I would create a data structure (an array) to manage your controls for you. Insert a new worksheet (hide it from the user later), and down the rows in column A write the name of the control. It should be a simple sequencing name, like Check1 or Check2. Now in the columns five columns to the right assign your various codes. That's it for setup, hide the sheet. Now when your workbook fires up, read that table into an array and create some logic rules to help you search the array for a particular control, do some searching on the 5 words then look up the matching control to return the name. In the future when your data changes all you have to do is edit that one worksheet.

 

[deepfriedcheese]

ChrisM,

I inherited this beast, so I'm working with what I have. That said, your advice just simplified my life considerably. Thank you!

As I worked through the issues I noticed that some of the check boxes behaved normally and some presented me with the permission denied error. I wrote a script to delete them and replace them exactly as they were before, and now they all work. I'm assuming they were glitchy.

After replacing them all, I was able to run through a macro to rename them all in the default manner, e.g., Check Box 1. Then I took your advice and moved the list of names and the criteria to a hidden sheet. This will be no more difficult to maintain than using a userform, but it is approximately 7000x easier to develop.

Thanks again.