Need help with making fields required before save/print/close

[tnsvolley]

I have a form that has two tabs, Authorization and Final Reconciliation. I want to make several fields (b7:b12,h4,j7,m7,j8,m8,h9,h10) on the Authorization tab required before the user can save, print or close the form. Is there a way to do this??

I have attached the spreadsheet for a better visual.

Out of Area Travel Form Effective 2016-01-01.xlsm

Thanks in Advance for any help

[Mumps1]

Place the following macros in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, doubleclick 'ThisWorkbook'. An empty code window will open up. Copy and paste all three macros into the open window. Close the window to return to your sheet. When you attempt to save, close or print the form, you will be prompted to enter any missing information.

Please Login or Register  to view this content.

[LJMetzger]

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

[tnsvolley]

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

Thank you, that worked perfectly!
Eileen

[tnsvolley]

Hi tnsvolley,

See the attached sample copy of your file. You need the following code in the 'ThisWorkbook' code module of your file.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

If you need help with Macros and/or VBA the following may help:
To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

That worked how I wanted, however, i did not think my plan through as now I can not save it as a blank form for others to fill in because the fields have to be filled in. Any ideas??

[LJMetzger]

Hi,

You don't have to quote entire previous posts when replying.

See the attached file that contains the following code. New Code and Modified Code are in red.

To implement the code, Double Click cell 'A1' (to save), 'B1' (to print), 'C1' (to close) on sheet 'Authorization' and enter password abc (Case Insensitive). This will allow the 'Administrator' to save, print, or close the file at any time.

Please Login or Register  to view this content.

[tnsvolley]

Wow...that is awesome...thanks so much!!!