Macro/VBA Questions:

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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?
 
Last edited:

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Apr 26, 2017
Messages
51

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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:

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

Your code should look something like this:



Code:
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

Board Regular
Joined
Apr 26, 2017
Messages
51

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Just add this in your code
Code:
    For Each ch In ActiveSheet.DrawingObjects
        If TypeName(ch) = "CheckBox" Then
            ch.Value = False
        End If
    Next
 

reneev

Board Regular
Joined
Apr 26, 2017
Messages
51
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,817
Messages
5,525,068
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top