Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
This line:
MsgBox Msg To: MsgBox Msg, , "Title" Substituye your title for the word "Title". Note the commas. "Brian" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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" wrote in message ... 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? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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" wrote in message ... 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? . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
I had an extra End Sub didn't I. I removed it and it runs good.
"JLGWhiz" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
Is it possible to set the File Save as File Name from User Form Box Names?
Example: These are text Box Names. "Spec TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2 .xls" I would like for it to look something like this: Spec 2HCC201200 ATLNGACS 403711 00 I am not sure it can be done Thanks Again "JLGWhiz" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . . . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
You need to start a new thread. When you post your questions, try to
realize that the person responding to you cannot see your monitor screen and cannot read your mind. The questions should be clear and unambiguous. Use VBA terminology if you can, so there is no misunderstanding of what you are referring to. Instead of asking if something is possible: 1. State your objective. 2. Explain what you have tried, if anything. 3. Post any code you have tried and show where error messages occurred and what the message was. 4. When referring to controls like ListBox, ComboBox, TextBox, etc. state whether they are on a UserForm or a Sheet. If they are on a sheet, identify their source as Forms Toolbar or Control Toolbox, since the two types have different property characteristics and require different code for use. 5. It is helpful to know what version of Excel you are using, but is not always necessary. 6. Questions related to worksheet data manipulation should provide a top level description of the data base layout, i.e. Headers on row 1, Columns A thru F, with or without formulas or any information pertinent to analyzing the problem or developing the solution. Colums are/are not dynamic in length. Which ones? These type things help the responders to see what you see. "Brian" wrote in message ... Is it possible to set the File Save as File Name from User Form Box Names? Example: These are text Box Names. "Spec TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2 .xls" I would like for it to look something like this: Spec 2HCC201200 ATLNGACS 403711 00 I am not sure it can be done Thanks Again "JLGWhiz" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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? . . . . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing Meassage Box Title
I posted a new thread
Thanks for all your help "JLGWhiz" wrote: You need to start a new thread. When you post your questions, try to realize that the person responding to you cannot see your monitor screen and cannot read your mind. The questions should be clear and unambiguous. Use VBA terminology if you can, so there is no misunderstanding of what you are referring to. Instead of asking if something is possible: 1. State your objective. 2. Explain what you have tried, if anything. 3. Post any code you have tried and show where error messages occurred and what the message was. 4. When referring to controls like ListBox, ComboBox, TextBox, etc. state whether they are on a UserForm or a Sheet. If they are on a sheet, identify their source as Forms Toolbar or Control Toolbox, since the two types have different property characteristics and require different code for use. 5. It is helpful to know what version of Excel you are using, but is not always necessary. 6. Questions related to worksheet data manipulation should provide a top level description of the data base layout, i.e. Headers on row 1, Columns A thru F, with or without formulas or any information pertinent to analyzing the problem or developing the solution. Colums are/are not dynamic in length. Which ones? These type things help the responders to see what you see. "Brian" wrote in message ... Is it possible to set the File Save as File Name from User Form Box Names? Example: These are text Box Names. "Spec TEO_No_1 CLLI_Code_1 CES_No_1 TEO_Appx_No_2 .xls" I would like for it to look something like this: Spec 2HCC201200 ATLNGACS 403711 00 I am not sure it can be done Thanks Again "JLGWhiz" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple meassage box | Excel Programming | |||
Changing title from default | Excel Programming | |||
Can I add a Warning Meassage to a Macros Designed to Delete a Row | Excel Worksheet Functions | |||
changing the application name in the title bar | Excel Programming | |||
changing chart title via vba? | Excel Programming |