Hello All,
I am using a Excel file were we have drop down list as WIP, Sanctioned, Rejected file etc. what I am looking for is if user selects the option Sanctioned or Rejected from the drop down list in col F should get a pop up asking for confirmation with yes or no that he want to change the status to Sanctioned or Rejected.
Because, if he selects Sanctioned or Rejected from the drop down list in col F another cell in Col P will change to Completed and entire row will be locked for editing.
Hello,
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AnswerNum As Integer
Dim Answer As String
If Not Intersect(Target, Range("F:F")) Is Nothing And Len(Target.Value) > 0 Then
AnswerNum = MsgBox("Would you like to change the status to " & Target.Value & "?", vbYesNo)
If AnswerNum = 6 Then Answer = "Yes"
If AnswerNum = 7 Then Answer = "No"
If Answer = "Yes" Then
'Code for Yes Answer
End If
If Answer = "No" Then
'Code for No Answer
End If
End If
End SubUpdate the code where indicated, to do whatever you want when they select Yes/No
Oh I forgot to mention, place this in the worksheet module for the worksheet that is being edited.
Hey Max, Thanks for your help and time. I would like to understand your code a little as I am still in learning stage. Hope you don't mind.
The message box pop ups for all the following options [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 112"]Pending[/TD]
Work in Progress
[/td]Submitted
[/td]Sanctioned
[/td]Doubtful
[/td]Rejected file
[/td]
[/TABLE]
The code should only pop up for Sanctioned or Rejected status only as on basis of this the entire row will be locked and can't be edited further unless the sheet is unprotected.
First you will need unlock the cells in the worksheet (re-lock any cells you would like to prevent people from editing):
Select all cells in the worksheet (Cntrl + A) -> Right Click the Cells and click "Format Cells" -> Protection Tab -> Uncheck the "Locked" box.
Then you can use this code. Anytime "Sanctioned" or "Rejected File" is selected in column F, and the user chooses yes on the input box, the entire row will be locked from editing:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AnswerNum As Integer
Dim Answer As String
Dim Password As String
Password = "Password123"
If Not Intersect(Target, Range("F:F")) Is Nothing And Len(Target.Value) > 0 Then
If Target.Value <> "Sanctioned" And Target.Value <> "Rejected file" Then GoTo Endsub
AnswerNum = MsgBox("Would you like to change the status to " & Target.Value & "?", vbYesNo)
If AnswerNum = 6 Then Answer = "Yes"
If AnswerNum = 7 Then Answer = "No"
If Answer = "Yes" Then
'Code for Yes Answer
ActiveSheet.Unprotect (Password)
Target.EntireRow.Locked = True
ActiveSheet.Protect (Password)
End If
If Answer = "No" Then
'Code for No Answer
End If
End If
Endsub:
End SubPlease update the password in the code. It is currently "Password123"
Don’t have an account yet? Register yourself now and be a part of our community!