| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Message Box Question
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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 "Brian" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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. -- Rick (MVP - Excel) "Brian" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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 wrote: 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? -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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? "Brian" wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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" wrote in message ... 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? "Brian" wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
What triggers the Message to pop up?
"Rick Rothstein" wrote: 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" wrote in message ... 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? "Brian" wrote: 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? . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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" wrote: 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" wrote in message ... 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? "Brian" wrote: 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? . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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 (MVP - Excel) "Brian" wrote in message ... What triggers the Message to pop up? "Rick Rothstein" wrote: 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" wrote in message ... 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? "Brian" wrote: 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? . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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 -- Rick (MVP - Excel) "Brian" wrote in message ... 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" wrote: 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" wrote in message ... 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? "Brian" wrote: 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? . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Message Box Question
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. "Brian" wrote in message ... 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" wrote: 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" wrote in message ... 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? "Brian" wrote: 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? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Message box question | Excel Discussion (Misc queries) | |||
| Message Box Coding question | Excel Programming | |||
| Simple message box question | Excel Discussion (Misc queries) | |||
| Message Box Question | Excel Programming | |||
| message box question | Excel Programming | |||
Linear Mode
