I have a message box that I would like to change the title of. Right now it
say's Microsoft Excel

Here is the code I have, but I am missing something. According to the book I
have this should work.

'Greeting Message Bob
Dim Title As String

If Me.Engineer_2.Value = "Bozeman" Then
If Time < 0.5 Then
Msg = "Morning "
ElseIf Time < 0.75 Then
Msg = "Afternoon "
Else
Msg = "Evening "
End If

Msg = "Good " & Msg & "Mr. Bozeman"
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Welcome to the C.E.S."
MsgBox Msg

Title = "C.E.S."

End If

Any ideas?

This line:

MsgBox Msg


To:

MsgBox Msg, , "Title"

Substituye your title for the word "Title". Note the commas.




"Brian" <(E-Mail Removed)> wrote in message
news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
>I have a message box that I would like to change the title of. Right now it
> say's Microsoft Excel
>
> Here is the code I have, but I am missing something. According to the book
> I
> have this should work.
>
> 'Greeting Message Bob
> Dim Title As String
>
> If Me.Engineer_2.Value = "Bozeman" Then
> If Time < 0.5 Then
> Msg = "Morning "
> ElseIf Time < 0.75 Then
> Msg = "Afternoon "
> Else
> Msg = "Evening "
> End If
>
> Msg = "Good " & Msg & "Mr. Bozeman"
> Msg = Msg & vbNewLine & vbNewLine
> Msg = Msg & "Welcome to the C.E.S."
> MsgBox Msg
>
> Title = "C.E.S."
>
> End If
>
> Any ideas?

Just for clarification:

The message box has three elements:
1. The message must be a string or a string variable. Strings are enclosed
in quoteation marks, but the string variables are not.

2. Buttons and icons. These are optional items but the default is vbOK
which puts a button with the caption "OK" on the message box display.

3. The title must be a string or string variable.

When using the message box as an information medium only, you do not use the
parenthesies or equal sign. However when using the message box as a
function, you must assign a variable and use the equal sign and then the
parentheses to enclose the three elements.

I hope this helps rather than confuses.


"Brian" <(E-Mail Removed)> wrote in message
news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
>I have a message box that I would like to change the title of. Right now it
> say's Microsoft Excel
>
> Here is the code I have, but I am missing something. According to the book
> I
> have this should work.
>
> 'Greeting Message Bob
> Dim Title As String
>
> If Me.Engineer_2.Value = "Bozeman" Then
> If Time < 0.5 Then
> Msg = "Morning "
> ElseIf Time < 0.75 Then
> Msg = "Afternoon "
> Else
> Msg = "Evening "
> End If
>
> Msg = "Good " & Msg & "Mr. Bozeman"
> Msg = Msg & vbNewLine & vbNewLine
> Msg = Msg & "Welcome to the C.E.S."
> MsgBox Msg
>
> Title = "C.E.S."
>
> End If
>
> Any ideas?

After I changed it, it still say's Microsoft Excel at the top on the message
box.

I also tried to change the = to a -, but it gives me a Compile error:
Expected Sub, Function, or Property Meassage

In the book I am reading it shows the - sign, not the = sign. Which one
should I be using?

'Greeting Message Bozeman
Dim Title As String

If Me.Engineer_2.Value = "Bozeman" Then
If Time < 0.5 Then
Msg = "Morning "
ElseIf Time < 0.75 Then
Msg = "Afternoon "
Else
Msg = "Evening "
End If

Msg = "Good " & Msg & "Mr. Bozeman"
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Welcome to the C.E.S."

MsgBox Msg, , "Title"

Title = "C.E.S."

End If

Thanks & Merry Christmas



"JLGWhiz" wrote:

> Just for clarification:
>
> The message box has three elements:
> 1. The message must be a string or a string variable. Strings are enclosed
> in quoteation marks, but the string variables are not.
>
> 2. Buttons and icons. These are optional items but the default is vbOK
> which puts a button with the caption "OK" on the message box display.
>
> 3. The title must be a string or string variable.
>
> When using the message box as an information medium only, you do not use the
> parenthesies or equal sign. However when using the message box as a
> function, you must assign a variable and use the equal sign and then the
> parentheses to enclose the three elements.
>
> I hope this helps rather than confuses.
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
> >I have a message box that I would like to change the title of. Right now it
> > say's Microsoft Excel
> >
> > Here is the code I have, but I am missing something. According to the book
> > I
> > have this should work.
> >
> > 'Greeting Message Bob
> > Dim Title As String
> >
> > If Me.Engineer_2.Value = "Bozeman" Then
> > If Time < 0.5 Then
> > Msg = "Morning "
> > ElseIf Time < 0.75 Then
> > Msg = "Afternoon "
> > Else
> > Msg = "Evening "
> > End If
> >
> > Msg = "Good " & Msg & "Mr. Bozeman"
> > Msg = Msg & vbNewLine & vbNewLine
> > Msg = Msg & "Welcome to the C.E.S."
> > MsgBox Msg
> >
> > Title = "C.E.S."
> >
> > End If
> >
> > Any ideas?
>
>
> .
>

I got it fixed,

Thanks for all your help & Have a Merry Christmas!!

"JLGWhiz" wrote:

> Just for clarification:
>
> The message box has three elements:
> 1. The message must be a string or a string variable. Strings are enclosed
> in quoteation marks, but the string variables are not.
>
> 2. Buttons and icons. These are optional items but the default is vbOK
> which puts a button with the caption "OK" on the message box display.
>
> 3. The title must be a string or string variable.
>
> When using the message box as an information medium only, you do not use the
> parenthesies or equal sign. However when using the message box as a
> function, you must assign a variable and use the equal sign and then the
> parentheses to enclose the three elements.
>
> I hope this helps rather than confuses.
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
> >I have a message box that I would like to change the title of. Right now it
> > say's Microsoft Excel
> >
> > Here is the code I have, but I am missing something. According to the book
> > I
> > have this should work.
> >
> > 'Greeting Message Bob
> > Dim Title As String
> >
> > If Me.Engineer_2.Value = "Bozeman" Then
> > If Time < 0.5 Then
> > Msg = "Morning "
> > ElseIf Time < 0.75 Then
> > Msg = "Afternoon "
> > Else
> > Msg = "Evening "
> > End If
> >
> > Msg = "Good " & Msg & "Mr. Bozeman"
> > Msg = Msg & vbNewLine & vbNewLine
> > Msg = Msg & "Welcome to the C.E.S."
> > MsgBox Msg
> >
> > Title = "C.E.S."
> >
> > End If
> >
> > Any ideas?
>
>
> .
>

If I was doing it, I would use the ComboBox click event. I would put this
code in the UserForm code module. In design mode, right click on the form
and select View Code. Paste this in the code window.


Private Sub Engineer_2_Click()
If Me.Engineer_2.ListIndex = - 1 Then
Exit Sub
Else
If Time < 0.5 Then
Msg = "Morning "
ElseIf Time < 0.75 Then
Msg = "Afternoon "
Else
Msg = "Evening "
End If
Msg = "Good " & Msg & Me.Engineer_2.Value
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Welcome to the C.E.S."
Titl = "C.E.S."
MsgBox Msg, , Titl
End If
End Sub


Now the code will run as soon as the user selects a name from the ComboBox.
If the ComboBox has no selection, then the message box does not display.
Notice also that the "e" was remove from the word "Title". That is because,
Title is a reserved word that is used in the parameters portion of the
message box and some other functions and methods such as InputBox. It is a
good practice to avoid using reserved words for variables that might have
different definitions and characteristics than those assigned in VBA source
code.

Have a good Christmas and New Year.



"Brian" <(E-Mail Removed)> wrote in message
news:0C015BD2-C950-402A-B929-(E-Mail Removed)...
>I got it fixed,
>
> Thanks for all your help & Have a Merry Christmas!!
>
> "JLGWhiz" wrote:
>
>> Just for clarification:
>>
>> The message box has three elements:
>> 1. The message must be a string or a string variable. Strings are
>> enclosed
>> in quoteation marks, but the string variables are not.
>>
>> 2. Buttons and icons. These are optional items but the default is vbOK
>> which puts a button with the caption "OK" on the message box display.
>>
>> 3. The title must be a string or string variable.
>>
>> When using the message box as an information medium only, you do not use
>> the
>> parenthesies or equal sign. However when using the message box as a
>> function, you must assign a variable and use the equal sign and then the
>> parentheses to enclose the three elements.
>>
>> I hope this helps rather than confuses.
>>
>>
>> "Brian" <(E-Mail Removed)> wrote in message
>> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
>> >I have a message box that I would like to change the title of. Right now
>> >it
>> > say's Microsoft Excel
>> >
>> > Here is the code I have, but I am missing something. According to the
>> > book
>> > I
>> > have this should work.
>> >
>> > 'Greeting Message Bob
>> > Dim Title As String
>> >
>> > If Me.Engineer_2.Value = "Bozeman" Then
>> > If Time < 0.5 Then
>> > Msg = "Morning "
>> > ElseIf Time < 0.75 Then
>> > Msg = "Afternoon "
>> > Else
>> > Msg = "Evening "
>> > End If
>> >
>> > Msg = "Good " & Msg & "Mr. Bozeman"
>> > Msg = Msg & vbNewLine & vbNewLine
>> > Msg = Msg & "Welcome to the C.E.S."
>> > MsgBox Msg
>> >
>> > Title = "C.E.S."
>> >
>> > End If
>> >
>> > Any ideas?
>>
>>
>> .
>>

Would you help me with several other Items I am working on? I have a command
button that I want to open a file.

In this line of code I only want to retrieve the WorkBook ("Master
Engineering Spec.xlsm". It shows all files with the xlsm extension. Can I
narroww it down to only to only Show the Document "Master Engineering
Spec.xlsm"? I bet you have a better way of doing this. It seems like I took
the long way and even then I see all the files with the same extention.

' Open Existing Engineer Spec 9 Control Button

Private Sub Open_Existing_Engineer_Spec_9_Click()

FileToOpen = Application.GetOpenFilename("Master Engineering
Spec(*.xlsm), *.xlsm")

If FileToOpen = False Then

MsgBox ("Cannot Open File")

Exit Sub

End If

Set bk = Workbooks.Open(Filename:=FileToOpen)
End Sub


Thanks

"JLGWhiz" wrote:

> If I was doing it, I would use the ComboBox click event. I would put this
> code in the UserForm code module. In design mode, right click on the form
> and select View Code. Paste this in the code window.
>
>
> Private Sub Engineer_2_Click()
> If Me.Engineer_2.ListIndex = - 1 Then
> Exit Sub
> Else
> If Time < 0.5 Then
> Msg = "Morning "
> ElseIf Time < 0.75 Then
> Msg = "Afternoon "
> Else
> Msg = "Evening "
> End If
> Msg = "Good " & Msg & Me.Engineer_2.Value
> Msg = Msg & vbNewLine & vbNewLine
> Msg = Msg & "Welcome to the C.E.S."
> Titl = "C.E.S."
> MsgBox Msg, , Titl
> End If
> End Sub
>
>
> Now the code will run as soon as the user selects a name from the ComboBox.
> If the ComboBox has no selection, then the message box does not display.
> Notice also that the "e" was remove from the word "Title". That is because,
> Title is a reserved word that is used in the parameters portion of the
> message box and some other functions and methods such as InputBox. It is a
> good practice to avoid using reserved words for variables that might have
> different definitions and characteristics than those assigned in VBA source
> code.
>
> Have a good Christmas and New Year.
>
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:0C015BD2-C950-402A-B929-(E-Mail Removed)...
> >I got it fixed,
> >
> > Thanks for all your help & Have a Merry Christmas!!
> >
> > "JLGWhiz" wrote:
> >
> >> Just for clarification:
> >>
> >> The message box has three elements:
> >> 1. The message must be a string or a string variable. Strings are
> >> enclosed
> >> in quoteation marks, but the string variables are not.
> >>
> >> 2. Buttons and icons. These are optional items but the default is vbOK
> >> which puts a button with the caption "OK" on the message box display.
> >>
> >> 3. The title must be a string or string variable.
> >>
> >> When using the message box as an information medium only, you do not use
> >> the
> >> parenthesies or equal sign. However when using the message box as a
> >> function, you must assign a variable and use the equal sign and then the
> >> parentheses to enclose the three elements.
> >>
> >> I hope this helps rather than confuses.
> >>
> >>
> >> "Brian" <(E-Mail Removed)> wrote in message
> >> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
> >> >I have a message box that I would like to change the title of. Right now
> >> >it
> >> > say's Microsoft Excel
> >> >
> >> > Here is the code I have, but I am missing something. According to the
> >> > book
> >> > I
> >> > have this should work.
> >> >
> >> > 'Greeting Message Bob
> >> > Dim Title As String
> >> >
> >> > If Me.Engineer_2.Value = "Bozeman" Then
> >> > If Time < 0.5 Then
> >> > Msg = "Morning "
> >> > ElseIf Time < 0.75 Then
> >> > Msg = "Afternoon "
> >> > Else
> >> > Msg = "Evening "
> >> > End If
> >> >
> >> > Msg = "Good " & Msg & "Mr. Bozeman"
> >> > Msg = Msg & vbNewLine & vbNewLine
> >> > Msg = Msg & "Welcome to the C.E.S."
> >> > MsgBox Msg
> >> >
> >> > Title = "C.E.S."
> >> >
> >> > End If
> >> >
> >> > Any ideas?
> >>
> >>
> >> .
> >>
>
>
> .
>

The code you are using is for when you do not know the exact name of the
file you want to open. When you do know the exact name, including the file
extension, and the workbook is in the current directory that your active
workbook is in then you can use the syntax:

Workbooks.Open Filename:="Master Engineering Spec.xlsm"

or another syntax form:

Workbooks.Open("Master Engineering Spec.xlsm")

So your event procedure would then become:

Private Sub Open_Existing_Engineer_Spec_9_Click()
On Error Resume Next
Workbooks.Open("Master Engineering Spec.xlsm")
If Err.Number <> 0 Then
MsgBox "The open method failed"
End If
End Sub

The above code will open the file if the directory path is the same as the
workbook containing the calling code AND if the file extension is correct.
In xl2007 the file extension is of importance because some people have the
same file name with macros and without macros, each having a different file
extension code. Prior to xl2007, the files would have required a different
name or would have to have been in different directories. Give the above
code a try to see if it works. If it does not find the file it will give
you a message.



"Brian" <(E-Mail Removed)> wrote in message
news:402FDD5D-7EB5-418C-BF9D-(E-Mail Removed)...
> Would you help me with several other Items I am working on? I have a
> command
> button that I want to open a file.
>
> In this line of code I only want to retrieve the WorkBook ("Master
> Engineering Spec.xlsm". It shows all files with the xlsm extension. Can I
> narroww it down to only to only Show the Document "Master Engineering
> Spec.xlsm"? I bet you have a better way of doing this. It seems like I
> took
> the long way and even then I see all the files with the same extention.
>
> ' Open Existing Engineer Spec 9 Control Button
>
> Private Sub Open_Existing_Engineer_Spec_9_Click()
>
> FileToOpen = Application.GetOpenFilename("Master Engineering
> Spec(*.xlsm), *.xlsm")
>
> If FileToOpen = False Then
>
> MsgBox ("Cannot Open File")
>
> Exit Sub
>
> End If
>
> Set bk = Workbooks.Open(Filename:=FileToOpen)
> End Sub
>
>
> Thanks
>
> "JLGWhiz" wrote:
>
>> If I was doing it, I would use the ComboBox click event. I would put
>> this
>> code in the UserForm code module. In design mode, right click on the
>> form
>> and select View Code. Paste this in the code window.
>>
>>
>> Private Sub Engineer_2_Click()
>> If Me.Engineer_2.ListIndex = - 1 Then
>> Exit Sub
>> Else
>> If Time < 0.5 Then
>> Msg = "Morning "
>> ElseIf Time < 0.75 Then
>> Msg = "Afternoon "
>> Else
>> Msg = "Evening "
>> End If
>> Msg = "Good " & Msg & Me.Engineer_2.Value
>> Msg = Msg & vbNewLine & vbNewLine
>> Msg = Msg & "Welcome to the C.E.S."
>> Titl = "C.E.S."
>> MsgBox Msg, , Titl
>> End If
>> End Sub
>>
>>
>> Now the code will run as soon as the user selects a name from the
>> ComboBox.
>> If the ComboBox has no selection, then the message box does not display.
>> Notice also that the "e" was remove from the word "Title". That is
>> because,
>> Title is a reserved word that is used in the parameters portion of the
>> message box and some other functions and methods such as InputBox. It is
>> a
>> good practice to avoid using reserved words for variables that might have
>> different definitions and characteristics than those assigned in VBA
>> source
>> code.
>>
>> Have a good Christmas and New Year.
>>
>>
>>
>> "Brian" <(E-Mail Removed)> wrote in message
>> news:0C015BD2-C950-402A-B929-(E-Mail Removed)...
>> >I got it fixed,
>> >
>> > Thanks for all your help & Have a Merry Christmas!!
>> >
>> > "JLGWhiz" wrote:
>> >
>> >> Just for clarification:
>> >>
>> >> The message box has three elements:
>> >> 1. The message must be a string or a string variable. Strings are
>> >> enclosed
>> >> in quoteation marks, but the string variables are not.
>> >>
>> >> 2. Buttons and icons. These are optional items but the default is
>> >> vbOK
>> >> which puts a button with the caption "OK" on the message box display.
>> >>
>> >> 3. The title must be a string or string variable.
>> >>
>> >> When using the message box as an information medium only, you do not
>> >> use
>> >> the
>> >> parenthesies or equal sign. However when using the message box as a
>> >> function, you must assign a variable and use the equal sign and then
>> >> the
>> >> parentheses to enclose the three elements.
>> >>
>> >> I hope this helps rather than confuses.
>> >>
>> >>
>> >> "Brian" <(E-Mail Removed)> wrote in message
>> >> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
>> >> >I have a message box that I would like to change the title of. Right
>> >> >now
>> >> >it
>> >> > say's Microsoft Excel
>> >> >
>> >> > Here is the code I have, but I am missing something. According to
>> >> > the
>> >> > book
>> >> > I
>> >> > have this should work.
>> >> >
>> >> > 'Greeting Message Bob
>> >> > Dim Title As String
>> >> >
>> >> > If Me.Engineer_2.Value = "Bozeman" Then
>> >> > If Time < 0.5 Then
>> >> > Msg = "Morning "
>> >> > ElseIf Time < 0.75 Then
>> >> > Msg = "Afternoon "
>> >> > Else
>> >> > Msg = "Evening "
>> >> > End If
>> >> >
>> >> > Msg = "Good " & Msg & "Mr. Bozeman"
>> >> > Msg = Msg & vbNewLine & vbNewLine
>> >> > Msg = Msg & "Welcome to the C.E.S."
>> >> > MsgBox Msg
>> >> >
>> >> > Title = "C.E.S."
>> >> >
>> >> > End If
>> >> >
>> >> > Any ideas?
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>

With this code I am getting a compile error: Only Comments may appear after
End Sub, End Funcition, or End Property. All I wanted to do was to be able to
open an exsisting file Named "Spec.xlsm".

' Open Existing Engineer Spec 9 Control Button

Private Sub Open_Existing_Engineer_Spec_9_Click()

End Sub

FileToOpen = Application.GetOpenFilename("Spec (*.xlsm), *.xlsm")

If FileToOpen = False Then

MsgBox "The Open Method Failed, No Document Opened", , "C.E. S."

Exit Sub

End If

Set bk = Workbooks.Open(Filename:=FileToOpen)
End Sub

Thanks



"JLGWhiz" wrote:

> The code you are using is for when you do not know the exact name of the
> file you want to open. When you do know the exact name, including the file
> extension, and the workbook is in the current directory that your active
> workbook is in then you can use the syntax:
>
> Workbooks.Open Filename:="Master Engineering Spec.xlsm"
>
> or another syntax form:
>
> Workbooks.Open("Master Engineering Spec.xlsm")
>
> So your event procedure would then become:
>
> Private Sub Open_Existing_Engineer_Spec_9_Click()
> On Error Resume Next
> Workbooks.Open("Master Engineering Spec.xlsm")
> If Err.Number <> 0 Then
> MsgBox "The open method failed"
> End If
> End Sub
>
> The above code will open the file if the directory path is the same as the
> workbook containing the calling code AND if the file extension is correct.
> In xl2007 the file extension is of importance because some people have the
> same file name with macros and without macros, each having a different file
> extension code. Prior to xl2007, the files would have required a different
> name or would have to have been in different directories. Give the above
> code a try to see if it works. If it does not find the file it will give
> you a message.
>
>
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:402FDD5D-7EB5-418C-BF9D-(E-Mail Removed)...
> > Would you help me with several other Items I am working on? I have a
> > command
> > button that I want to open a file.
> >
> > In this line of code I only want to retrieve the WorkBook ("Master
> > Engineering Spec.xlsm". It shows all files with the xlsm extension. Can I
> > narroww it down to only to only Show the Document "Master Engineering
> > Spec.xlsm"? I bet you have a better way of doing this. It seems like I
> > took
> > the long way and even then I see all the files with the same extention.
> >
> > ' Open Existing Engineer Spec 9 Control Button
> >
> > Private Sub Open_Existing_Engineer_Spec_9_Click()
> >
> > FileToOpen = Application.GetOpenFilename("Master Engineering
> > Spec(*.xlsm), *.xlsm")
> >
> > If FileToOpen = False Then
> >
> > MsgBox ("Cannot Open File")
> >
> > Exit Sub
> >
> > End If
> >
> > Set bk = Workbooks.Open(Filename:=FileToOpen)
> > End Sub
> >
> >
> > Thanks
> >
> > "JLGWhiz" wrote:
> >
> >> If I was doing it, I would use the ComboBox click event. I would put
> >> this
> >> code in the UserForm code module. In design mode, right click on the
> >> form
> >> and select View Code. Paste this in the code window.
> >>
> >>
> >> Private Sub Engineer_2_Click()
> >> If Me.Engineer_2.ListIndex = - 1 Then
> >> Exit Sub
> >> Else
> >> If Time < 0.5 Then
> >> Msg = "Morning "
> >> ElseIf Time < 0.75 Then
> >> Msg = "Afternoon "
> >> Else
> >> Msg = "Evening "
> >> End If
> >> Msg = "Good " & Msg & Me.Engineer_2.Value
> >> Msg = Msg & vbNewLine & vbNewLine
> >> Msg = Msg & "Welcome to the C.E.S."
> >> Titl = "C.E.S."
> >> MsgBox Msg, , Titl
> >> End If
> >> End Sub
> >>
> >>
> >> Now the code will run as soon as the user selects a name from the
> >> ComboBox.
> >> If the ComboBox has no selection, then the message box does not display.
> >> Notice also that the "e" was remove from the word "Title". That is
> >> because,
> >> Title is a reserved word that is used in the parameters portion of the
> >> message box and some other functions and methods such as InputBox. It is
> >> a
> >> good practice to avoid using reserved words for variables that might have
> >> different definitions and characteristics than those assigned in VBA
> >> source
> >> code.
> >>
> >> Have a good Christmas and New Year.
> >>
> >>
> >>
> >> "Brian" <(E-Mail Removed)> wrote in message
> >> news:0C015BD2-C950-402A-B929-(E-Mail Removed)...
> >> >I got it fixed,
> >> >
> >> > Thanks for all your help & Have a Merry Christmas!!
> >> >
> >> > "JLGWhiz" wrote:
> >> >
> >> >> Just for clarification:
> >> >>
> >> >> The message box has three elements:
> >> >> 1. The message must be a string or a string variable. Strings are
> >> >> enclosed
> >> >> in quoteation marks, but the string variables are not.
> >> >>
> >> >> 2. Buttons and icons. These are optional items but the default is
> >> >> vbOK
> >> >> which puts a button with the caption "OK" on the message box display.
> >> >>
> >> >> 3. The title must be a string or string variable.
> >> >>
> >> >> When using the message box as an information medium only, you do not
> >> >> use
> >> >> the
> >> >> parenthesies or equal sign. However when using the message box as a
> >> >> function, you must assign a variable and use the equal sign and then
> >> >> the
> >> >> parentheses to enclose the three elements.
> >> >>
> >> >> I hope this helps rather than confuses.
> >> >>
> >> >>
> >> >> "Brian" <(E-Mail Removed)> wrote in message
> >> >> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
> >> >> >I have a message box that I would like to change the title of. Right
> >> >> >now
> >> >> >it
> >> >> > say's Microsoft Excel
> >> >> >
> >> >> > Here is the code I have, but I am missing something. According to
> >> >> > the
> >> >> > book
> >> >> > I
> >> >> > have this should work.
> >> >> >
> >> >> > 'Greeting Message Bob
> >> >> > Dim Title As String
> >> >> >
> >> >> > If Me.Engineer_2.Value = "Bozeman" Then
> >> >> > If Time < 0.5 Then
> >> >> > Msg = "Morning "
> >> >> > ElseIf Time < 0.75 Then
> >> >> > Msg = "Afternoon "
> >> >> > Else
> >> >> > Msg = "Evening "
> >> >> > End If
> >> >> >
> >> >> > Msg = "Good " & Msg & "Mr. Bozeman"
> >> >> > Msg = Msg & vbNewLine & vbNewLine
> >> >> > Msg = Msg & "Welcome to the C.E.S."
> >> >> > MsgBox Msg
> >> >> >
> >> >> > Title = "C.E.S."
> >> >> >
> >> >> > End If
> >> >> >
> >> >> > Any ideas?
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>

Look at what you have in the code window. Each procedure can have only one
title line, the line that starts with Private Sub or just Sub, and only one
closing line, the line that says End Sub. If you have entries before the
title line that are not declarations such as Option Explicit, Public or
Const the compiler will balk. If you paste code into a window and it
automatically adds the End Sub line where you already have an End Sub line,
you have to delete one of those lines or it will cause the compiler to balk.
Just check in that code window to make sure you have deleted any extra line
before or after your procedure.



"Brian" <(E-Mail Removed)> wrote in message
news:701A449A-9BFB-494A-AFFA-(E-Mail Removed)...
> With this code I am getting a compile error: Only Comments may appear
> after
> End Sub, End Funcition, or End Property. All I wanted to do was to be able
> to
> open an exsisting file Named "Spec.xlsm".
>
> ' Open Existing Engineer Spec 9 Control Button
>
> Private Sub Open_Existing_Engineer_Spec_9_Click()
>
> End Sub
>
> FileToOpen = Application.GetOpenFilename("Spec (*.xlsm), *.xlsm")
>
> If FileToOpen = False Then
>
> MsgBox "The Open Method Failed, No Document Opened", , "C.E. S."
>
> Exit Sub
>
> End If
>
> Set bk = Workbooks.Open(Filename:=FileToOpen)
> End Sub
>
> Thanks
>
>
>
> "JLGWhiz" wrote:
>
>> The code you are using is for when you do not know the exact name of the
>> file you want to open. When you do know the exact name, including the
>> file
>> extension, and the workbook is in the current directory that your active
>> workbook is in then you can use the syntax:
>>
>> Workbooks.Open Filename:="Master Engineering Spec.xlsm"
>>
>> or another syntax form:
>>
>> Workbooks.Open("Master Engineering Spec.xlsm")
>>
>> So your event procedure would then become:
>>
>> Private Sub Open_Existing_Engineer_Spec_9_Click()
>> On Error Resume Next
>> Workbooks.Open("Master Engineering Spec.xlsm")
>> If Err.Number <> 0 Then
>> MsgBox "The open method failed"
>> End If
>> End Sub
>>
>> The above code will open the file if the directory path is the same as
>> the
>> workbook containing the calling code AND if the file extension is
>> correct.
>> In xl2007 the file extension is of importance because some people have
>> the
>> same file name with macros and without macros, each having a different
>> file
>> extension code. Prior to xl2007, the files would have required a
>> different
>> name or would have to have been in different directories. Give the above
>> code a try to see if it works. If it does not find the file it will give
>> you a message.
>>
>>
>>
>> "Brian" <(E-Mail Removed)> wrote in message
>> news:402FDD5D-7EB5-418C-BF9D-(E-Mail Removed)...
>> > Would you help me with several other Items I am working on? I have a
>> > command
>> > button that I want to open a file.
>> >
>> > In this line of code I only want to retrieve the WorkBook ("Master
>> > Engineering Spec.xlsm". It shows all files with the xlsm extension. Can
>> > I
>> > narroww it down to only to only Show the Document "Master Engineering
>> > Spec.xlsm"? I bet you have a better way of doing this. It seems like I
>> > took
>> > the long way and even then I see all the files with the same extention.
>> >
>> > ' Open Existing Engineer Spec 9 Control Button
>> >
>> > Private Sub Open_Existing_Engineer_Spec_9_Click()
>> >
>> > FileToOpen = Application.GetOpenFilename("Master Engineering
>> > Spec(*.xlsm), *.xlsm")
>> >
>> > If FileToOpen = False Then
>> >
>> > MsgBox ("Cannot Open File")
>> >
>> > Exit Sub
>> >
>> > End If
>> >
>> > Set bk = Workbooks.Open(Filename:=FileToOpen)
>> > End Sub
>> >
>> >
>> > Thanks
>> >
>> > "JLGWhiz" wrote:
>> >
>> >> If I was doing it, I would use the ComboBox click event. I would put
>> >> this
>> >> code in the UserForm code module. In design mode, right click on the
>> >> form
>> >> and select View Code. Paste this in the code window.
>> >>
>> >>
>> >> Private Sub Engineer_2_Click()
>> >> If Me.Engineer_2.ListIndex = - 1 Then
>> >> Exit Sub
>> >> Else
>> >> If Time < 0.5 Then
>> >> Msg = "Morning "
>> >> ElseIf Time < 0.75 Then
>> >> Msg = "Afternoon "
>> >> Else
>> >> Msg = "Evening "
>> >> End If
>> >> Msg = "Good " & Msg & Me.Engineer_2.Value
>> >> Msg = Msg & vbNewLine & vbNewLine
>> >> Msg = Msg & "Welcome to the C.E.S."
>> >> Titl = "C.E.S."
>> >> MsgBox Msg, , Titl
>> >> End If
>> >> End Sub
>> >>
>> >>
>> >> Now the code will run as soon as the user selects a name from the
>> >> ComboBox.
>> >> If the ComboBox has no selection, then the message box does not
>> >> display.
>> >> Notice also that the "e" was remove from the word "Title". That is
>> >> because,
>> >> Title is a reserved word that is used in the parameters portion of the
>> >> message box and some other functions and methods such as InputBox. It
>> >> is
>> >> a
>> >> good practice to avoid using reserved words for variables that might
>> >> have
>> >> different definitions and characteristics than those assigned in VBA
>> >> source
>> >> code.
>> >>
>> >> Have a good Christmas and New Year.
>> >>
>> >>
>> >>
>> >> "Brian" <(E-Mail Removed)> wrote in message
>> >> news:0C015BD2-C950-402A-B929-(E-Mail Removed)...
>> >> >I got it fixed,
>> >> >
>> >> > Thanks for all your help & Have a Merry Christmas!!
>> >> >
>> >> > "JLGWhiz" wrote:
>> >> >
>> >> >> Just for clarification:
>> >> >>
>> >> >> The message box has three elements:
>> >> >> 1. The message must be a string or a string variable. Strings are
>> >> >> enclosed
>> >> >> in quoteation marks, but the string variables are not.
>> >> >>
>> >> >> 2. Buttons and icons. These are optional items but the default
>> >> >> is
>> >> >> vbOK
>> >> >> which puts a button with the caption "OK" on the message box
>> >> >> display.
>> >> >>
>> >> >> 3. The title must be a string or string variable.
>> >> >>
>> >> >> When using the message box as an information medium only, you do
>> >> >> not
>> >> >> use
>> >> >> the
>> >> >> parenthesies or equal sign. However when using the message box as
>> >> >> a
>> >> >> function, you must assign a variable and use the equal sign and
>> >> >> then
>> >> >> the
>> >> >> parentheses to enclose the three elements.
>> >> >>
>> >> >> I hope this helps rather than confuses.
>> >> >>
>> >> >>
>> >> >> "Brian" <(E-Mail Removed)> wrote in message
>> >> >> news:7F884012-1766-4F0B-A7CD-(E-Mail Removed)...
>> >> >> >I have a message box that I would like to change the title of.
>> >> >> >Right
>> >> >> >now
>> >> >> >it
>> >> >> > say's Microsoft Excel
>> >> >> >
>> >> >> > Here is the code I have, but I am missing something. According to
>> >> >> > the
>> >> >> > book
>> >> >> > I
>> >> >> > have this should work.
>> >> >> >
>> >> >> > 'Greeting Message Bob
>> >> >> > Dim Title As String
>> >> >> >
>> >> >> > If Me.Engineer_2.Value = "Bozeman" Then
>> >> >> > If Time < 0.5 Then
>> >> >> > Msg = "Morning "
>> >> >> > ElseIf Time < 0.75 Then
>> >> >> > Msg = "Afternoon "
>> >> >> > Else
>> >> >> > Msg = "Evening "
>> >> >> > End If
>> >> >> >
>> >> >> > Msg = "Good " & Msg & "Mr. Bozeman"
>> >> >> > Msg = Msg & vbNewLine & vbNewLine
>> >> >> > Msg = Msg & "Welcome to the C.E.S."
>> >> >> > MsgBox Msg
>> >> >> >
>> >> >> > Title = "C.E.S."
>> >> >> >
>> >> >> > End If
>> >> >> >
>> >> >> > Any ideas?
>> >> >>
>> >> >>
>> >> >> .
>> >> >>
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>