+ Reply to Thread
Results 1 to 14 of 14

replace VBA run-time error message with custom message

  1. #1
    BEEJAY
    Guest

    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.
    Therefore: 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. #2
    BEEJAY
    Guest

    RE: 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.
    > Therefore: 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. #3
    BEEJAY
    Guest

    RE: 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.
    > > Therefore: 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. #4
    Dave Peterson
    Guest

    Re: 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.
    > > > Therefore: 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. #5
    BEEJAY
    Guest

    Re: 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.
    > > > > Therefore: 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. #6
    Dave Peterson
    Guest

    Re: 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.
    > > > > > Therefore: 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. #7
    Dave Peterson
    Guest

    Re: 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.
    > > > > > > Therefore: 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. #8
    BEEJAY
    Guest

    Re: 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.
    > > > > > > > Therefore: 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. #9
    Dave Peterson
    Guest

    Re: 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.
    > > > > > > > > Therefore: 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. #10
    BEEJAY
    Guest

    Re: 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.
    > > > > > > > > > Therefore: 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. #11
    BEEJAY
    Guest

    Re: 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.
    > > > > > > > > > Therefore: 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. #12
    Dave Peterson
    Guest

    Re: 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.
    > > > > > > > > > > Therefore: 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. #13
    BEEJAY
    Guest

    Re: 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.
    > > > > > > > > > > > Therefore: 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. #14
    Dave Peterson
    Guest

    Re: 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.
    > > > > > > > > > > > > Therefore: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1