Protect and Lock Problem SOLVED!!
 

Perhaps I have been trying all wrong with this project I have asked about
before. I tried a different approach

I decided that the original form (blank fields) can be saved without macros
since I know the macro password to do things behind the scenes

I used the macro to hide the "warning sheet" that macros must be enabled and
open all others if macros were enabled, otherwise all the user sees is a big
blue screen that says "macros must be enabled to use this file properly.
Since workbook is also protected, they cannot get at other forms with
'unhide'.

code:

Private Sub Workbook_Open()
Dim wks
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect password:="mypassword"
For Each wks In Me.Worksheets
wks.Visible = True
Next
Me.Worksheets("intro").Visible = False
ActiveWorkbook.Protect password:="mypassword"
Set wks = Me.Worksheets("request")

End Sub

Now instead of worrying about the file name, I simply went to the file save
event to lock things down- so as soon as someone saves a file with
information in it- critical fields are locked which prevent the user from
'recycling' the spreadsheet- which was causing a lot of problems- submitting
bad information- tis better if each user filled it out from scratch rather
than miss a fiew key fields with previous information in them. If the field
is blank to begin with, we know to kick it back as incomplete rather than to
proceed ahead with bad information... (very costly).

so for the save event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Sheets("Request").Range("K4") = Now 'date stamps file so user cannot
'backdate' the file and forward it.
Set wks = Me.Worksheets("request") ' sets current worksheet to first page

ActiveWorkbook.Unprotect password:="mypassword"

ActiveSheet.Unprotect password:="mypassword"
Range("B4:B9").Select 'locks the critical areas that make it a unique
record file
Selection.Locked = True
ActiveSheet.Protect password:="mypassword"
ActiveWorkbook.Protect password:="mypassword"



End Sub



Thanks to Frank, Simon and rjb for your various thoughts and techniques. By
grunting through this, I finally felt like I learned something!!!


Thanks
Paul