Required fields in Excel

  • Thread starter Thread starter noxsaj
  • Start date Start date
N

noxsaj

I'm brand new to using VB.

I have been playing around with setting up required fields in Excel. I
was lucky to find a post with the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Worksheets("Sheet1").Range("A2").Value = "" Then
Cancel = True
MsgBox "Please Fill-In A2"
End If
End Sub

I have tested this and it works fine. This basically ensures that excel
can't be saved unless the specified cells are filled in.

Now for a really stupid question. How do I save this script to the
excel workbook so that I can distribute to other users? Obviously if I
try and save the workbook itself it prompts me to enter the required
fields before saving, and if I just close without saving the script is
lost.

Any suggestions would be appreciated.

Thanks,

Jim
 
Try disabling events on your machine. In the VB editor, open the Immediate
Window and type

Application.EnableEvents=False

Save the workbook, then set back to true.
 
Hi Jim.........
Try this "password" approach..........

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("A2").Value = "xxx" Then
End
End If
If Worksheets("Sheet1").Range("A2").Value = "" Then
Cancel = True
MsgBox "Please Fill-In A2"
End If

End Sub
 
Sorry, sent before finishing.........I meant to add that you can change the
"xxx" to be any "password" that you choose, just for the purpose of being
able to save the workbook without triggering the main intent of your
macro......and of course you can also put that password in another
cell............but hmmmmm, I guess it gets curiouser and curiouser cause
then you would need a WorksheetOpen event to delete the password when a user
opens the file or it would be there all the time...........ugh

Vaya con Dios,
Chuck, CABGx3
 
Back
Top