Macro for Drop down menu based Massage box
Friends,
Good day to everyone !
I need a macro code for the following problem.
I have an excel sheet and on that I am using drop down menu in Cell U8 to U357 (Data Validation). In this work sheet, If I selected the value "A" from the drop down menu, I want to Display a message box with a message " Please enter the absent mode" and once the user clicked OK on the message box automatically the selection should go to its parallel "V" cell.
Eg: As soon as I selected "A" from drop down menu in cell U10, the " Please enter the absent mode" message should appear with an OK button and after clicking the OK the cursor should select the "V10" Cell where the absent mode has to enter. I will be more happy if only the "V10" cell is active and the user can do anything in other cells Only after entering any value in the "V10" cell.
Kindly help me with a macro for this and thanks in advance.
Macro For Drop Down Menu
Option Explicit
Dim ActiveCellAddress As String
Dim AllowNavigation As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
' Limit the range to column U
If Target.Column = Columns("U").Column Then
' Compare cell value
If ActiveCell.Value = "A" Then
' Display message
MsgBox "Please enter the absent mode", , "DATA ENTRY REQUIRED"
' Move to column V
ActiveCell.Offset(0, 1).Select
' Populate variable for absent mode cell address
ActiveCellAddress = ActiveCell.Address
' Flag
AllowNavigation = False
End If
End If
' Check if absent mode cell was changed
If Target.Address = ActiveCellAddress Then
' If data was deleted and parent cell value is still A - don't allow navigation
If Range(ActiveCellAddress) = "" And Range(ActiveCellAddress).Offset(0, -1) = "A" Then AllowNavigation = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Flag
If AllowNavigation = True Then Exit Sub
' Persist absent cell selection
If Range(ActiveCellAddress) = "" Then
Range(ActiveCellAddress).Select
End If
' Flag
If Range(ActiveCellAddress) <> "" Then AllowNavigation = True
End Sub
Suggestion from bionicle
Hello,
You can try the script bellow. It runs every time when a sheet is changed(any value from the entire sheet is changed)
You should be careful if you have any other cells that contain the value "A"
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveCell.Value = "A" Then MsgBox "Please enter the absent mode" Else Exit Sub
ActiveCell.Offset(0, 1).Select
Dim i As Integer
For i = 1 To 5
If ActiveCell.Value = "" Then ActiveCell.Value = InputBox("Please enter the absent mode") Else Exit Sub
ActiveCell.Select
Next i
End Sub
Best regards,