Show message box when select drop down list

[trivedi]

Hi,

I have a drop down list which has y1,y2 and y3 - what i want to do is if I select year 1 message box says you've selected y1 and the same goes for y2 and y3.

I am new to Macros, please help

 

[johnnyL]

Right click the sheet tab that has your drop down list and select 'view code'.

Paste the following code into the big window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Not Intersect(Target, Range("C1")) Is Nothing And Target.Cells.Count = 1 Then
        Select Case Target
            Case Is = "y1"
                MsgBox "you've selected y1"
            Case Is = "y2"
                MsgBox "you've selected y2"
            Case Is = "y3"
                MsgBox "you've selected y3"
        End Select
    End If
End Sub

Change the 'C1' in the following line to the cell address that has your drop down list:

    If Not Intersect(Target, Range("C1")) Is Nothing And Target.Cells.Count = 1 Then

Then go back to your sheet with the drop down list and try it out.

 

[My Aswer Is This]

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script assumes your Drop down list is in column 3
Modify this if needed
When you select a value in the drop down list the script runs

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/23/2021  3:40:42 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Column = 3 Then
    MsgBox "you've selected  " & Target.Value
End If
End Sub

 

[trivedi]

Hi

Right click the sheet tab that has your drop down list and select 'view code'.

Paste the following code into the big window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Not Intersect(Target, Range("C1")) Is Nothing And Target.Cells.Count = 1 Then
        Select Case Target
            Case Is = "y1"
                MsgBox "you've selected y1"
            Case Is = "y2"
                MsgBox "you've selected y2"
            Case Is = "y3"
                MsgBox "you've selected y3"
        End Select
    End If
End Sub

Change the 'C1' in the following line to the cell address that has your drop down list:

    If Not Intersect(Target, Range("C1")) Is Nothing And Target.Cells.Count = 1 Then

Then go back to your sheet with the drop down list and try it out.

Hi Johnny,
Thank you so much...works perfectly fine
I have one more question.
here is the code....if you see I wrote in the message box delete values in I2 cell in converter tab...can we do this with the macro?
if I select Y1 it will show the message and automatically delete value in Cell I2 (converter tab) and same with Y2 and Y3

Private Sub Worksheet_Change(ByVal Target As Range)
'
If Not Intersect(Target, Range("F1")) Is Nothing And Target.Cells.Count = 1 Then
Select Case Target
Case Is = "Y1"
MsgBox "Please select Y1" & "(Same year)" & " " & "in CONVERTER TAB Cell H2 and delete values" & " " & "(if any) from Cell I2" & " " & "(Import)"
Case Is = "Y2"
MsgBox "Please select Y2" & "(Same year)" & " " & "in CONVERTER TAB Cell H2 and delete values" & " " & "(if any) from Cell I2" & " " & "(Import)"
Case Is = "Y3"
MsgBox "Please select Y3" & "(Same year)" & " " & "in CONVERTER TAB Cell H2 and delete values" & " " & "(if any) from Cell I2" & " " & "(Import)"
End Select
End If
End Sub

 

[johnnyL]

Please explain in words what exactly you want to happen if 'y1' is selected from the drop down list.

Is 'CONVERTER TAB' a different sheet name? What is 'Import'? What is 'Same year'? What is the name of the sheet tab with the drop down list?

 

[trivedi]

Please explain in words what exactly you want to happen if 'y1' is selected from the drop down list.

Is 'CONVERTER TAB' a different sheet name? What is 'Import'? What is 'Same year'? What is the name of the sheet tab with the drop down list?

1)Converter tab - different tab in the same sheet
2)Import is a cell in the converter tab
3)same year you can ignore
4)Name of the tab with drop-down list calculations

 

[trivedi]

1)Converter tab - different tab in the same sheet
2)Import is a cell in the converter tab
3)same year you can ignore
4)Name of the tab with drop-down list calculations

if i select any year from drop down list--- it should show the message and delete a value the cell which is in the converter tab

 

[trivedi]

if i select any year from drop down list--- it should show the message and delete a value the cell which is in the converter tab

which is in cell I2 in converter tab

 

[johnnyL]

So what is the 'CONVERTER TAB Cell H2' that you mentioned? nothing?

 

[johnnyL]

If 'y1'in the drop down list is selected you want the following to happen:
1) display message box
2) Delete Cell I2 from the 'Converter' tab

Is that It?