Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup box appears based on cell value entered
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup box appears based on cell value entered
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup box appears based on cell value entered
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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup box appears based on cell value entered
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? "JLGWhiz" wrote: 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" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Popup box appears based on cell value entered
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. "bruner" wrote: 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? "JLGWhiz" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Satisfying two variables entered via popup | Excel Discussion (Misc queries) | |||
adding text that appears after the entered data | Setting up and Configuration of Excel | |||
If values are'nt entered in cell a popup window comes up?? | Excel Worksheet Functions | |||
How do I create a popup window based on a cell value? | Excel Discussion (Misc queries) | |||
User enters data in popup box - its entered into desired cell | Excel Programming |