Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database problem to be solved... Please help! | Excel Discussion (Misc queries) | |||
solved problem | Excel Worksheet Functions | |||
Excel/MSIE problem with add-in, I can't get it solved | Setting up and Configuration of Excel | |||
Excel/MSIE problem with add-in, I can't get it solved | Excel Discussion (Misc queries) | |||
How can the Travelling Salesman Problem be solved using Solver Ad. | Excel Discussion (Misc queries) |