check for fields filled out

[mhart210]

I have an Excel (ver. 2003) spreadsheet that needs certain fields populated before it can be saved or closed. I know I have to use the BeforeSave function, but I am unfamilar with it. I am also aware that macros need enabled and would like some help writing code that would lock the workbook and prompt the user to enable macros if they initially choose not to enable them. Thanks in advance for any help!!

[Static]

1) yes, use the BeforeSave event of ThisWorkbook
2) you cant.. if the user chooses to disable macros, then you cant get around it by normal means. since they are disabling macros, no macro can run to lock and prompt

you could write an Add-in or a macro workbook in XLStart
workbooks in XLStart can run macros without users having to enable them

If you do this, you will need to Grab the application on open of the workbook.. and watch the app events for Beforesave... then when it occurs.. check the workbook, see if its the "correct" one and not some other workbook. then check the fields.
the user would need to add this workbook to their XLStart forlder for it to work.

Add-in would work much the same way, but its a little harder to write and would require the user to "install" by registering the dll.

[mhart210]

Could I get some guidance on the code for the beforesave function?

[Static]

Start a new workbook...
MENU: Window > Hide
so you cant see the workbook

now ALT+F11 to bring up the code window

Dim WithEvents XLS As Application
Private Sub Workbook_Open()
    Set XLS = Application
End Sub
 
 
 
Private Sub XLS_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox "You are about to save " & Wb.Name
End Sub

no use the wb object in the before save to test if its the right workbook...
then you can run thru and look for the fields...

now save this in your XLStart Folder...
C:\.....\OFFICE11\XLSTART\

now close excel.

open the other workbook.. and trigger the code by saving it,,,

[mhart210]

Thank you.

[Static]

you are welcome.

if this is resolved.. please click thread tools > mark thread resolved. (its about your first post)

Thanks!! and

[westconn1]

if you save the workbook locked and passworded, then use the macro to unlock it, lock again on close.

no macro, no unlock

pete