pop up message

[keffw]

i need to know if there is way to create a pop up message for a cell to warn user from entering information into that cell. but it still needs to be able to accept information by the user with authority to do so. like if they clicked on a cell and then a message gave a warning do not enter.

 

[DonkeyOte]

Not sure about a pop up box (though you may be able to use an on activate) - but you can use "data validation" to create a box that will appear when that cell is active...

 

[Tom Urtis]

It sounds like you only want some users (the ones with authorization) to access the cell, and all others to stay out.

If so, try this. Right click on your sheet tab, left click on View Tab, and paste in the code below. It is two event procedures, one for when the cell is selected, and one for when the cell is changed.

Modify the code for password and target cell (A1 is used in this example).

If this is what you are looking for, then the next obvious step is to protect the VBE from snoopers, to stop them from getting into the sheet code to learn the password. But first let's see if this code is what you want:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Dim WarnPass As String
WarnPass = InputBox(prompt:="You just selected cell " & Target.Address & ", which is a special cell." & vbCrLf & vbCrLf & _
"If you did this by mistake, or if you" & vbCrLf & _
"don't even know what I'm talking about," & vbCrLf & _
"then hit the OK or Cancel button to escape," & vbCrLf & _
"no harm no foul, it's all good." & vbCrLf & vbCrLf & _
"Otherwise, enter your password below" & vbCrLf & _
"in order to enter or edit data in this cell:", _
Title:="Your password is required to access cell " & Target.Address & ".")
If WarnPass <> "Password" Then
MsgBox "Click OK to return to to the worksheet.", _
16, "Cancelled -- correct password not entered."
ActiveSheet.Unprotect ("Password")
ActiveCell.Locked = True
ActiveSheet.Protect ("Password")
Else
ActiveSheet.Unprotect ("Password")
ActiveCell.Locked = False
ActiveSheet.Protect ("Password")
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
ActiveSheet.Unprotect ("Password")
ActiveCell.Locked = True
ActiveSheet.Protect ("Password")
End Sub
_________________