lock a workbook until certain cells are filled

[batmanz]

I have a workbook that I would like to have certain cells filled (as in:
name, address, phone, etc) before you are able to just start entering other
information. what would be the direction to use? a macro? or an excel
function. i found one example where cells had to be filled before you were
allowed to save the workbook. that involved writing some code at the location
of the excel sign next to the file button.
I already have a macro to do a "save as" using the cells, however, the cells
do not have to be filled to use the function. (it just defaults to a date).
i don't know enough about VBA to write something on my own. i'm getting
pretty good at copying other examples and applying them to my application.
thx

 

[Mark Ivey]

Here is one you can try for a starter....

From your worksheet... press ALT + F11
Double click the worksheet you want this applied to in the Project Toolbar.

Once you have data in cells A1, B1, C1, D1, and E1... it will unlock your
sheet (provided you don't have a password on it).

Mark Ivey





Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1").Value <> "" And Range("B1").Value <> "" And _
Range("C1").Value <> "" And Range("D1").Value <> "" And _
Range("E1").Value <> "" Then

Sheets("Sheet1").Unprotect

MsgBox "Your worksheet is now unlocked", vbOKOnly

End If

End Sub

 

[Mark Ivey]

Added some better tweaks... give this one a shot (put into same location as
mentioned in my other post)

Mark Ivey



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1").Value <> "" And Range("B1").Value <> "" And _
Range("C1").Value <> "" And Range("D1").Value <> "" And _
Range("E1").Value <> "" Then


If ActiveSheet.ProtectContents = True Then
Sheets(ActiveSheet.Name).Unprotect
MsgBox "Your worksheet is now unlocked", vbOKOnly
End If

End If

End Sub

 

[Mark Ivey]

How to apply it...

From your worksheet... press ALT + F11
Double click the worksheet you want this applied to in the Project Toolbar
and paste the code shown below in the right-hand window.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1").Value <> "" And Range("B1").Value <> "" And _
Range("C1").Value <> "" And Range("D1").Value <> "" And _
Range("E1").Value <> "" Then


If ActiveSheet.ProtectContents = True Then
Sheets(ActiveSheet.Name).Unprotect
MsgBox "Your worksheet is now unlocked", vbOKOnly
End If

End If

End Sub

 

[batmanz]

Mark,
just picked up your information. I will give it a try later today. Looks
great on paper! thanks for the help and I will let you know the outcome.

Regards, Robert Newcomb

 

[batmanz]

Mark,
try as i may, i cannot get the code to actuate. i have it assigned to the
sheet. even renamed the code to match the sheet name.
i am running 03 excel, so instead of ALT+F11, i go the the sheet name,
right click and view code to enter the information.
any additional suggestions

 

[Mark Ivey]

Which sheet did you paste the code to?

What needs to be done is when you open the Visual Basic Editor is to double
click on the sheet you need to monitor for this change event.

Then paste the code below into the right hand screen. This code will monitor
to see when cells A1, B1, C1, D1, and E1 have something in them. When all
these cells have some type of data in them, the code will unprotect the
sheet and prompt you with a Messagebox letting you know it has been
unprotected. You can change the A1, B1, etc. references to the cells you
need to monitor. I just used these to get you started.

Mark Ivey


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1").Value <> "" And Range("B1").Value <> "" And _
Range("C1").Value <> "" And Range("D1").Value <> "" And _
Range("E1").Value <> "" Then


If ActiveSheet.ProtectContents = True Then
Sheets(ActiveSheet.Name).Unprotect
MsgBox "Your worksheet is now unlocked", vbOKOnly
End If

End If

End Sub

 

[batmanz]

I applied it to the first sheet named "Quote".

In 2003 Excel, I can only right click on the sheet and go to code.
I will copy and paste and make it run

Thanks for your help!