Make a user fill in certain cells before they can save

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
I have a spreadsheet with an order form in it that I created that includes a customerid and address fields. I want to make sure that they can not save the spreadsheet without filling in certain cells. If the cells are populated then the SaveAs window will open.
This is what I have

.........A...............B
1...CustomerId..._____
2...Addresss......._____
3...City.............._____
4...State............_____
5...Zip..............._____

So in Cell B1is where the customerid goes,
Cell B2 is where the address goes
Cell B3 is where the city goes
Cell B4 is where the state goes
Cell B5 is where the zip goes.

Also the sheet name is 'Order Form'
I am using Excel 2003 with windows Xp SP2
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
Try something like this in the ThisWorkbook module:

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Order Form")
If (.[B2] = "" Or .[B3] = "" Or .[B4] = "" Or .[B5] = "") Then
    MsgBox "You cannot save until you fill in B2:B5 on the Order Form sheet"
    Cancel = True
    End If
End With
End Sub
 

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
How do I Try something like this in the ThisWorkbook module:

This is how I tried it, Open the spreadsheet. Clicked Tools, Macro, Visual Basic Editor, Then in that I clicked Insert, Module and pasted the code you gave me in there.
Should I have done it a different way?

Thank you
 

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
I clicked Report on your posting thinking that the info you gave me would give me it in a different format, but it said it reported you to Admin. Sorry about that I didn't want to do that and hope I didn't mess anything up for you.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
In the Visual Basic Editor, in the margin on the left, you should see a couple modules associated with the current file. One for each sheet, one for each module you inserted, and one called "ThisWorkbook". The code needs to go in the ThisWorkbook module.
 

Seanzies

Board Regular
Joined
Nov 19, 2005
Messages
212
THANKS!!! that worked. In the Visual Basic Editor I had to click View, Project Explorer in order to see the "ThisWorkBook"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,111
Messages
5,835,444
Members
430,356
Latest member
RadStorm20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top