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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
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
7,988
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,118,028
Messages
5,569,741
Members
412,289
Latest member
Kingchaos64
Top