Message Box Question

[Brian]

I have a User Form that has a Combo Box in it. I would like for it to greet
the user by name based on the time of day.

I have tried several variations of this code, but no luck as of yet.

Private Sub Greeting()

'Time Greeting

If Me.Engineer_2.Value = "Name" Then MsgBox

If Time < 0.5 Then Msg = "Morning" Else
If Time >= 0.5 And Time < 0.75 Then Msg -"Afternoon" Else
If Time > 0.75 Then Msg -"Evening"

MsgBox "Good" & Msg

End Sub


What am I doing wrong?

 

[Mike H]

Brian,

You only need to test for Mornimg or Afternoon, if it's neither of those it
has to be evening. Try this

If Time < 0.5 Then
Msg = "Morning"
ElseIf Time >= 0.5 And Time < 0.75 Then
Msg = "Afternoon"
Else
Msg = "Evening"
End If

MsgBox "Good " & Msg

Mike

 

[Rick Rothstein]

Try this structure instead...

If Time < 0.5 Then
Msg = "Morning"
ElseIf Time < 0.75 Then
Msg -"Afternoon"
Else
Msg -"Evening"
End If

Your original code was missing an End If statement at the end, but the
overall structure you were using was kind of awkward, so I restructured it
for you. A couple of things to note about my code...

First, there is an ElseIf..Then statement that is part of the If..End If
blocking structure which can be used instead of doing an Else followed by a
separate If..Then statement... this makes the structure easier to read.

Second, If..Then..ElseIf..Else conditions are executed only until one of
them is satisfied, so if you have a Time value greater than 0.5 and less
than 0.75, it will pass over the first test and get caught by the second one
automatically... there is no need to do a separate Time>=0.5... it is
automatically equal or greater than 0.5 when the first test didn't trap it.

 

[Dave Peterson]

Sometimes, if you're comparing lots of values, the if/then/elseif... structure
can get difficult to decipher.

But there are other options:

Dim myTime As Date
Dim myMsg As String

myTime = Time
Select Case myTime
Case Is < TimeSerial(6, 0, 0): myMsg = "Early Morning"
Case Is < TimeSerial(12, 0, 0): myMsg = "Morning"
Case Is < TimeSerial(16, 0, 0): myMsg = "Afternoon"
Case Is < TimeSerial(21, 0, 0): myMsg = "early evening"
Case Else
myMsg = "evening"
End Select

MsgBox myMsg

I like using timeserial(). I find it easier to understand.

 

[Brian]

How do I get the Message to use the Input from Engineer_2 on the user form?

Where would I put that in the code?

 

[Rick Rothstein]

Just concatenate it onto the greeting. If you are using my structure, that
would look like this...

If Me.Engineer_2.Value = "Name" Then
If Time < 0.5 Then
Msg = "Morning"
ElseIf Time < 0.75 Then
Msg -"Afternoon"
Else
Msg -"Evening"
End If
Msg = Msg & " " & Me.Engineer_2.Value
MsgBox Msg
End If

 

[Brian]

What triggers the Message to pop up?

Just concatenate it onto the greeting. If you are using my structure, that
would look like this...

If Me.Engineer_2.Value = "Name" Then
If Time < 0.5 Then
Msg = "Morning"
ElseIf Time < 0.75 Then
Msg -"Afternoon"
Else
Msg -"Evening"
End If
Msg = Msg & " " & Me.Engineer_2.Value
MsgBox Msg
End If

--
Rick (MVP - Excel)





.

 

[Brian]

Here is the code. I can't get it to open when the name 'Steve" is choosen in
the User Form. I broke it again.

'Time Greeting

Private Sub Greeting()

If Me.Engineer_2.Value = "Steve" Then
If Time < 0.5 Then
Msg = "Morning"
ElseIf Time < 0.75 Then
Msg -"Afternoon"
Else
Msg -"Evening"
End If
Msg = Msg & "Good" & Me.Engineer_2.Value
MsgBox Msg
End If

End Sub

 

[Rick Rothstein]

It kind of depends. Is the engineer's name being selected by the user from
the ComboBox? If so, then the ComboBox's Click event should work for you.
If, on the other hand, you are getting the engineer's name from a TextBox,
then you will need a CommandButton for the user to press when they are done
typing (for this case, you would put the code in the CommandButton's Click
event).

 

[Rick Rothstein]

You need to provide an argument to your Sub so you can pass in the
engineer's name from the Click event of the ComboBox. Change the Greeting
subroutine to this...

Private Sub Greeting(EngineersName As String)
Dim Msg As String
If EngineersName = "Steve" Then
If Time < 0.5 Then
Msg = "Morning "
ElseIf Time < 0.75 Then
Msg = "Afternoon "
Else
Msg = "Evening "
End If
Msg = "Good " & Msg & EngineersName
MsgBox Msg
End If
End Sub

And then, in the Click event for the ComboBox, use this line of code to
initiate the greeting...

Greeting ComboBox2.Value

 

[JLGWhiz]

Is Greeting the name of the ComboBox? If so:

Private Sub Greeting_Click()

If Me.Engineer_2.Value = "Steve" Then
If Time < 0.5 Then
Msg = "Morning"
ElseIf Time < 0.75 Then
Msg -"Afternoon"
Else
Msg -"Evening"
End If
Msg = Msg & "Good" & Me.Engineer_2.Value
MsgBox Msg
End If

End Sub

I did not see how you are trying to trigger the procedure in any of the
postings. Also,

This line:
Msg = Msg & "Good" & Me.Engineer_2.Value
Would make more sense like:
Msg = "Good" & Msg & Me.Engineer_2.Value


Also, the first If statement appears to require a manual change each time to
make it equate to true. You could change it to:

If Me.Engineer_2.ListIndex <> -1 Then

That way it would execute if any name is selected and then the Msg part
would add the name in.