Required fields in Excel

[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

 

[JMB]

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.

 

[CLR]

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

 

[CLR]

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

 

[noxsaj]

Thanks a lot for your suggestions!!

Will try tonight.

Cheers,

Jim