• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

msgbox for different entries

rrocker1405

rrocker1405

Member
I'm not sure how to but this is what I intend to have, if my selection is LA msgbox should state "enter details", if my selection is CA msgbox should state "enter details", if my selection is "others" msgbox should state "please try again", if cell is blank there should not be a msgbox prompt.


range of cells a1:a10

criteria of selection: LA,CA,Others.


Best regards.......
 
Right-click on sheet tab, view code, paste this in.

[pre]
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim msg As String
Select Case UCase(Target.Value)

Case "LA"
msg = "enter details"
Case "CA"
msg = "enter details"
Case "OTHERS"
msg = "please try again"
Case ""
GoTo NoNeed
End Select
MsgBox msg, vbOKOnly
NoNeed:

End Sub
[/pre]
 
hey...thanks luke. The code works but after selecting the data and revisiting the cell otherwise the msgbox does not pop up. could lemme know why?


best regards!
 
You used the word "selection", so I wrote the macro to only be triggered after you select a cell. If you want it to go after you make a change to a cell, change the title of macro to be:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
[/pre]
 
Thanks Luke, I just did a few changes to the code that you gave but after changing the codes it gives me a msgbox but its blank with a OK button. would you know why? this is what i tried.


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub

Dim msg As String

Select Case UCase(Target.Value)

Case "Los Angeles"

msg = "Ensure to enter details"

Case "California"

msg = "Ensure to enter details"

Case "OTHERS"

msg = "Please specify details in column B"

Case ""

GoTo NoNeed

End Select

MsgBox msg, vbOKOnly

NoNeed:

End Sub


Best regards....!
 
As VB is case sensitive, while XL is usually not, it's usually good practice to make sure VB is comparing what we want it to compare. To handle any possible case differences done by the user, I used this line:

[pre]
Code: 
Select Case UCase(Target.Value)
to force VB to just look at a capitalized (UCase = Upper Case) version of what's in the cell. However, this means that all of the cases need to be in upper case form as well.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim msg As String
Select Case UCase(Target.Value)
Case "LOS ANGELES"
msg = "Ensure to enter details"
Case "CALIFORNIA"
msg = "Ensure to enter details"
Case "OTHERS"
msg = "Please specify details in column B"

'I changed this too as it's a little more robust, makes sure we
'always do something
Case Else
GoTo NoNeed
End Select
MsgBox msg, vbOKOnly
NoNeed:
End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top