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.


" wrote:

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

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

" wrote:

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

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



"CLR" wrote:

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

" wrote:

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

Thanks a lot for your suggestions!!

Will try tonight.

Cheers,

Jim

Hi,

I'm also new to VBA and I'm using Excel 2010. I've tried using the code on this forum and have tried several others on other forums similar to it. Each time I attempt to test if this will work the workbork saves and does not prompt me to enter any missing information.

Am I missing a step?

Any help appreciated,
Jlofton