Quote:
Originally Posted by y0rk1e72
isabelle
thanks for that however i've been told today that the list of sheets may change AND be added to.
I was trying to have a sheet with a list of the sheets in to file, that i could flag as to clear. My problem is the sheet names can change as new staff come and go so the sheet names are not fixed.
Due to confidentially i can't upload a file. However i'm looking at taking those bits out so i can post an example.
hopefully it will be done late tomorrow.
|
kinda fixed it by using the following
Formula:
Private Sub Worksheet_Activate()
Dim c As Range, msg As String, style As String, title As String
msg = "Do you want to clear your entries?" & vbCrLf & "click No to cancel"
style = vbYesNo + vbQuestion + vbDefaultButton2
title = "Action required"
response = MsgBox(msg, style, title)
If response = vbNo Then Exit Sub
Me.Protect "test", , , , True 'change test to your password
On Error Resume Next
Range("D9:E39").Value = ""
Range("G9:i39").Value = ""
Range("B44:B49").Value = ""
Range("D44:E49").Value = ""
Range("g44:I49").Value = ""
MsgBox ("Your entries have been cleared.")
End Sub
Not pretty but will do the job for now.
thanx everyone