| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace VBA run-time error message with custom message
Used J-Walks Menu maker to create menu of frequently used Spread-Sheets.
This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ................ I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Further:
The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
SLOWLY waking up this lovely Friday:
Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
If I don't have many to check, I do it inline.
dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Thanks for your response.
I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
If testStr = "" Then
Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
You don't need the () in the workbooks.open line:
If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Dave, Thank-you
I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Take a look at .open in VBA's help. You'll see that it has a parm to tell excel
to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Thanks so much. Very Helpful. Will be checking out that site some more.
"Dave Peterson" wrote: Take a look at .open in VBA's help. You'll see that it has a parm to tell excel to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
Sorry, it just doesn't seem to stop.
The previous stuff works fine, thanks to your input. The following is in "ThisWorkBook" Option Explicit Private Sub Workbook_Open() ' If Active Workbook is in Read-Only State then ' Call SaveAs_Message, Else End Procedure If ThisWorkbook.ReadOnly = True _ Then Call SaveAs_Message _ Else: End End Sub When I open the file thru File, Open, ............. and select Cancel, the file disappears (just as I want). When I open the file thru my custom menu, then Cancel, I get "Application Defined or Object Defined Error. If I select OK, everything works just fine I don't know where to look for this problem. Can I trouble you again? "Dave Peterson" wrote: Take a look at .open in VBA's help. You'll see that it has a parm to tell excel to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
I wonder what's in SaveAs_message that does that work???
BEEJAY wrote: Sorry, it just doesn't seem to stop. The previous stuff works fine, thanks to your input. The following is in "ThisWorkBook" Option Explicit Private Sub Workbook_Open() ' If Active Workbook is in Read-Only State then ' Call SaveAs_Message, Else End Procedure If ThisWorkbook.ReadOnly = True _ Then Call SaveAs_Message _ Else: End End Sub When I open the file thru File, Open, ............. and select Cancel, the file disappears (just as I want). When I open the file thru my custom menu, then Cancel, I get "Application Defined or Object Defined Error. If I select OK, everything works just fine I don't know where to look for this problem. Can I trouble you again? "Dave Peterson" wrote: Take a look at .open in VBA's help. You'll see that it has a parm to tell excel to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
The following is the SaveAs_Message, as well as the Other Message
the 1st one refers to: Option Explicit Sub SaveAs_Message() Dim Msg As String, Title As String Dim Config As Integer, Ans As Integer Msg = " This is a 'READ ONLY' File" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " In order to Proceed, Please select 'OK' NOW!!" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " The Save As Dialog Box will pop up" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " Be SURE to Change the File Name" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & "Selecting CANCEL will STOP this Process and CLOSE this file " Title = "Walinga Inc W A R N I N G ! !" Config = vbOKCancel + vbCritical Ans = MsgBox(Msg, Config, Title) If Ans = vbOK Then Call SaveAs_Process If Ans = vbCancel Then ThisWorkbook.Close End Sub Sub SaveAs_Process() ' Bring up the Save As Dialog Box Application.Dialogs(xlDialogSaveAs).Show End Sub I hope this helps. "Dave Peterson" wrote: I wonder what's in SaveAs_message that does that work??? BEEJAY wrote: Sorry, it just doesn't seem to stop. The previous stuff works fine, thanks to your input. The following is in "ThisWorkBook" Option Explicit Private Sub Workbook_Open() ' If Active Workbook is in Read-Only State then ' Call SaveAs_Message, Else End Procedure If ThisWorkbook.ReadOnly = True _ Then Call SaveAs_Message _ Else: End End Sub When I open the file thru File, Open, ............. and select Cancel, the file disappears (just as I want). When I open the file thru my custom menu, then Cancel, I get "Application Defined or Object Defined Error. If I select OK, everything works just fine I don't know where to look for this problem. Can I trouble you again? "Dave Peterson" wrote: Take a look at .open in VBA's help. You'll see that it has a parm to tell excel to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
replace VBA run-time error message with custom message
I didn't have any trouble--but I didn't use your custom menu.
Maybe you could set a breakpoint in your code and use your custom menu to start the macro--but step through it after the breakpoint. BEEJAY wrote: The following is the SaveAs_Message, as well as the Other Message the 1st one refers to: Option Explicit Sub SaveAs_Message() Dim Msg As String, Title As String Dim Config As Integer, Ans As Integer Msg = " This is a 'READ ONLY' File" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " In order to Proceed, Please select 'OK' NOW!!" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " The Save As Dialog Box will pop up" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & " Be SURE to Change the File Name" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & "Selecting CANCEL will STOP this Process and CLOSE this file " Title = "Walinga Inc W A R N I N G ! !" Config = vbOKCancel + vbCritical Ans = MsgBox(Msg, Config, Title) If Ans = vbOK Then Call SaveAs_Process If Ans = vbCancel Then ThisWorkbook.Close End Sub Sub SaveAs_Process() ' Bring up the Save As Dialog Box Application.Dialogs(xlDialogSaveAs).Show End Sub I hope this helps. "Dave Peterson" wrote: I wonder what's in SaveAs_message that does that work??? BEEJAY wrote: Sorry, it just doesn't seem to stop. The previous stuff works fine, thanks to your input. The following is in "ThisWorkBook" Option Explicit Private Sub Workbook_Open() ' If Active Workbook is in Read-Only State then ' Call SaveAs_Message, Else End Procedure If ThisWorkbook.ReadOnly = True _ Then Call SaveAs_Message _ Else: End End Sub When I open the file thru File, Open, ............. and select Cancel, the file disappears (just as I want). When I open the file thru my custom menu, then Cancel, I get "Application Defined or Object Defined Error. If I select OK, everything works just fine I don't know where to look for this problem. Can I trouble you again? "Dave Peterson" wrote: Take a look at .open in VBA's help. You'll see that it has a parm to tell excel to open the file readonly: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls", _ readonly:=true End If There are other options for that .open statement, too. BEEJAY wrote: Dave, Thank-you I have one more problem, now. If possible, I need to open the File(s) as Read Only My attempts shown below. F8 gives me: Compile error, Syntax error Other times, the curser stays on 'AS', and message is Compile error, expect End of Statement. I'm positive that I'm missing the obvious (again), but...... Help?!! testStr = Dir("C:\Contract Templates\HP-Truck-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\HP-Truck-2006_R1C0.xls", [ReadOnly]) As Workbook End If End Sub "Dave Peterson" wrote: You don't need the () in the workbooks.open line: If testStr = "" Then Call NotAvailable Else Workbooks.Open "C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls" End If Dave Peterson wrote: If testStr = "" Then Call NotAvailable Else Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If BEEJAY wrote: Thanks for your response. I now have the following, but it comes up with Compile error - Else without IF What am I missing here? Dim testStr As String testStr = "" On Error Resume Next testStr = Dir("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") On Error GoTo 0 If testStr = "" Then Call NotAvailable Else: Workbooks.Open ("C:\Contract Templates\BB-Pup-Chassis-2006_R1C0.xls") End If Thank-you "Dave Peterson" wrote: If I don't have many to check, I do it inline. dim testStr as string ..... teststr = "" on error resume next teststr = dir("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") on error goto 0 if teststr = "" then 'not found else 'was found end if Watch you're typing, too. You have a vertical bar instead of a backslash in that first line. BEEJAY wrote: SLOWLY waking up this lovely Friday: Now have: If Exists("C:\Contracts|BB-Pup-Chassis-2006_R0C0.xls") Then Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") Else: Call NotAvailable the 1st line comes up with a complie error - Sub or Function not defined. Now I am truly Stuck. Help Please. End Sub "BEEJAY" wrote: Further: The code that selects the files are as per this sample: Sub BB_Chassis() Workbooks.Open ("C:\Contracts\BB-Pup-Chassis-2006_R0C0.xls") End Sub I wondering if an If-Then_Else could somehow be used. If Exits, then select/open Work Book Else, Call Message Something like that? "BEEJAY" wrote: Used J-Walks Menu maker to create menu of frequently used Spread-Sheets. This add-in will be sent to all our salesmen. The menu lists ALL of the available Sp.Sheets, BUT not every salesman gets every Sp.Sheet. When a non-existant Sp.Sheet is selected from menu, error message pops up: "C:\ .................... (file Name), could not be found. Check spelling ............... I'd like to NOT have this message come up, but replace it with a custom message, something like: The requested file is not on your available list. Please select the correct template. Therefo Can I deactivate/delete the VBA message? If yes, How? How can I program in a message that tests for the requested Sp. Sheet, and selects the Sp. Sheet, if exists, or else comes up with my custom message? I hope this makes sense. Help!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom Views Error Message | Excel Discussion (Misc queries) | |||
| Replace Excel Message w/Custom Message | Excel Programming | |||
| Intercept/replace standard 'cell protected' message with my own message? | Excel Programming | |||
| Error Message When Using Replace | Excel Programming | |||
| Why error message when trying to display custom view? | Excel Discussion (Misc queries) | |||
Linear Mode
