Macro/VBA Questions:

[reneev]

1) Uncheck boxes with Macro Button - I have created a form in Excel which when protected, users can tab through and fill out either by typing, drop down boxes or checking a box. I created a macro button to "Clear Form" but the only thing it won't clear are the check boxes even though I've unchecked them while recording the macro. I'm using the legacy check boxes if that matters

2) Once I've created the macro, I cannot go back in and edit or delete it. I get an error: "Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command" Except, I don't have any hidden workbooks! I'm having to recreate the entire macro if I need to edit it.

3) I need this macro to be able to be run by different people on their own computers.

I'm not very experienced in VBA or Macros.

 

[DanteAmor]

You can put all the code or codes that you have.
There are 2 types of boxes, userform and activeX, which one do you use?

 

[reneev]

I'm using the legacy boxes (not activeX) but if it matters, I can switch.

When I click on the Visual Basic box, code comes up for one of the older macros and not the latest one. I don't know how to get the code for the current macro.

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[reneev]

Here's the link to the file via dropbox. It has been stripped of data containing confidential information therefore some of the data formulas no longer work, but that should not affect my questions above: https://www.dropbox.com/s/bmzikxqsxzsutjk/PAF-SCL 2019 3.25.19.xlsm?dl=0

However, anything you recommend that I do to the original file, I still need to figure out how to make them.

BTW... It seems that the macros are saved in a "PERSONAL.XLSB" file. I don't know that that is or how it could be affecting what I'm trying to do.

 

[DanteAmor]

Add your code to a module.
Press Alt-F11 to open the VBA editor. From the menu select Insert> Module.


After cleaning the cells add the following part:

    For Each ch In ActiveSheet.DrawingObjects
        If TypeName(ch) = "CheckBox" Then
            ch.Value = False
        End If
    Next

Your code should look something like this:

Sub Clear_Form()
    Range("B2").Value = ""
    Range("B3").Value = ""
    Range("F3").Value = ""
    Range("D11").Value = ""
    '...etc
    '
    For Each ch In ActiveSheet.DrawingObjects
        If TypeName(ch) = "CheckBox" Then
            ch.Value = False
        End If
    Next
    
    MsgBox "Form cleaned"
End Sub

 

[reneev]

This might be helpful if I knew what I was doing. I can get the VBA editor to open, I can figure out how to add a module but how do I know what macro it's being applied to? There's currently 3 of them (about to be 4) because I don't have an answer to question #2 yet.

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]After cleaning the cells add the following part:[/COLOR]

What do you mean?

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 33333]Your code should look something like this:[/COLOR]

Which code, and where would I see this?


I also don't have an answer to #3 .

 

[DanteAmor]

Just add this in your code

    For Each ch In ActiveSheet.DrawingObjects
        If TypeName(ch) = "CheckBox" Then
            ch.Value = False
        End If
    Next

 

[reneev]

Ok, I think I figured it out.

What about my other two questions?

2) Once I've created the macro, I cannot go back in and edit or delete it. I get an error: "Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command" Except, I don't have any hidden workbooks! I'm having to recreate the entire macro if I need to edit it.

3) I need this macro to be able to be run by different people on their own computers. BTW... It seems that the macros are saved in a "PERSONAL.XLSB" file. I don't know that that is or how it could be affecting what I'm trying to do?

 

[DanteAmor]

Ok, I think I figured it out.

What about my other two questions?

2) Once I've created the macro, I cannot go back in and edit or delete it. I get an error: "Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command" Except, I don't have any hidden workbooks! I'm having to recreate the entire macro if I need to edit it.

3) I need this macro to be able to be run by different people on their own computers. BTW... It seems that the macros are saved in a "PERSONAL.XLSB" file. I don't know that that is or how it could be affecting what I'm trying to do?

Put the macro in a module inside your thisworkbook, I'm afraid with that your 2 questions are solved.