PDA

View Full Version : Macro for drop down menu based message box



aneshdas
12-17-2013, 04:54 AM
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.

Bob Phillips
12-18-2013, 03:36 AM
Add this to the worksheet code module


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("U8:U357")) Is Nothing Then

If Target.Value = "A" Then

MsgBox "Please enter the absent mode", vbOKOnly
Target.Offset(0, 1).Select
End If
End If
End Sub

BrianMH
12-18-2013, 08:59 AM
Aneshdas I notice that you crossposted at the below link. It appears a solution has been provided. I notice you haven't replied to xld. Can you please reply to both threads if this has now been solved.http://www.mrexcel.com/forum/excel-questions/745529-macro-drop-down-menu-based-message-box.htmlThanks

aneshdas
12-20-2013, 12:29 AM
Add this to the worksheet code module


Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("U8:U357")) Is Nothing Then

If Target.Value = "A" Then

MsgBox "Please enter the absent mode", vbOKOnly
Target.Offset(0, 1).Select
End If
End If
End Sub


Thanks sir,

But it has just selected the respective "V" cell only. I am using drop down menu on V cell also. I need to activate the V drop down menu as soon as I enter the "A" in U cell and click the OK in message box.

Bob Phillips
12-20-2013, 09:31 AM
Can you respond to BrianMH please, I think we deserve that courtesy.