Popup box appears based on cell value entered

[bruner]

I have a population of users that can place a rate of 1.0 or greater in the
range E20:L20, if they want to enter less than 1.0 in this range, i want
there to be a popup box for a manager password to accept this rate below 1.0.
Anytime something below 1.0 is entered into these cells, this popup box
should appear and require the password.

Any suggestions on how to accomplish this?

 

[Carim]

Hi,

The initial control step ( without the password )

Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Double
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Range("E20:L20")) Is Nothing Then Exit
Sub
If .Value < 1 Then
val = .Value
MsgBox "You are not allowed to enter " & val & " without
Manager's Approval"
.Value = 1
End If
End With
End Sub

HTH

 

[JLGWhiz]

This modifies Carim's code a little to allow the password to be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Double
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Range("E20:L20")) Is Nothing Then Exit Sub
If .Value < 1 Then
val = .Value
MsgBox "You are not allowed to enter " & val & " without
Manager's Approval"

RETRY:
MgrApprv = InputBox("Enter Password", "MANAGER APPROVAL")
If MgrApprv <> "Password" Then
.Value = 1
GoTo RETRY:
End If
End If
End With
End Sub

A word of caution. If you make the value of the target equal blank [""]
then it sets up a perpetual loop for the msgbox and inputbox. That is why
the value is set to 1 if the wron password is used. If you get into the
loop, use Ctrl + Break to get out.

 

[bruner]

Carim and JLGWhiz...thanks so much! Really fantastic suggestions.

I have one more question. If a manager is not available at the moment and
they need to continue to work in the document, is there a way I could add a
third button to the dialog box that says "Keep rate at 1.0"?

This would then allow them to exit the loop without a manager over-ride to
go below the 1.0.

Thoughts?

This modifies Carim's code a little to allow the password to be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Double
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Range("E20:L20")) Is Nothing Then Exit Sub
If .Value < 1 Then
val = .Value
MsgBox "You are not allowed to enter " & val & " without
Manager's Approval"

RETRY:
MgrApprv = InputBox("Enter Password", "MANAGER APPROVAL")
If MgrApprv <> "Password" Then
.Value = 1
GoTo RETRY:
End If
End If
End With
End Sub

A word of caution. If you make the value of the target equal blank [""]
then it sets up a perpetual loop for the msgbox and inputbox. That is why
the value is set to 1 if the wron password is used. If you get into the
loop, use Ctrl + Break to get out.

I have a population of users that can place a rate of 1.0 or greater in the
range E20:L20, if they want to enter less than 1.0 in this range, i want
there to be a popup box for a manager password to accept this rate below 1.0.
Anytime something below 1.0 is entered into these cells, this popup box
should appear and require the password.

Any suggestions on how to accomplish this?

 

[bruner]

j.k. i figured it out. all i needed to do was eliminate that last "Go to
Retry:" and just replaced it with another message box saying "Password
Incorrect: Value will be set to 1.0 until correct password provided.

Carim and JLGWhiz...thanks so much! Really fantastic suggestions.

I have one more question. If a manager is not available at the moment and
they need to continue to work in the document, is there a way I could add a
third button to the dialog box that says "Keep rate at 1.0"?

This would then allow them to exit the loop without a manager over-ride to
go below the 1.0.

Thoughts?

This modifies Carim's code a little to allow the password to be entered.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim val As Double
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Range("E20:L20")) Is Nothing Then Exit Sub
If .Value < 1 Then
val = .Value
MsgBox "You are not allowed to enter " & val & " without
Manager's Approval"

RETRY:
MgrApprv = InputBox("Enter Password", "MANAGER APPROVAL")
If MgrApprv <> "Password" Then
.Value = 1
GoTo RETRY:
End If
End If
End With
End Sub

A word of caution. If you make the value of the target equal blank [""]
then it sets up a perpetual loop for the msgbox and inputbox. That is why
the value is set to 1 if the wron password is used. If you get into the
loop, use Ctrl + Break to get out.

I have a population of users that can place a rate of 1.0 or greater in the
range E20:L20, if they want to enter less than 1.0 in this range, i want
there to be a popup box for a manager password to accept this rate below 1.0.
Anytime something below 1.0 is entered into these cells, this popup box
should appear and require the password.

Any suggestions on how to accomplish this?