Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: vba: if error show msgbox

  1. #1
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba: if error show msgbox

    I have a subroutine that opens a file based on some input from the user.... generally the file it looks for has a standard naming format used. However sometimes the person responsible for this will use some other name, thus screwing up my macro.

    Rather than excel showing the generic error box, confusing the user completely, I want it to show MY msgbox telling the user to go in and find the file manually...

    I'm sure I've done this before, but I'm drawing a blank on how I managed it. Any help out there??


    Sub openphiac()

    Dim strfolder as string
    Dim strphiacfile as string

    strfolder = Range("folder")
    strphiacfile = Range("phiacfile")

    Workbooks.Open Filename:="O:\Phiac Data\PhiacTables\" & strfolder & "\" & strphiacfile & ".xls"

    End Sub

  2. #2
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,453
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi cmhoz
    Something like this ought to do it.
    Code:
    Sub openphiac()
    
    Dim strfolder As String
    Dim strphiacfile As String
    
    strfolder = Range("folder")
    strphiacfile = Range("phiacfile")
    
    On Error GoTo ErrMsg
    Workbooks.Open Filename:="O:\Phiac Data\PhiacTables\" & strfolder & "\" & strphiacfile & ".xls"
    Exit Sub
    
    ErrMsg:
    MsgBox ("Type in your message here."), , "MESSAGE TITLE"
    End Sub

  3. #3
    Board Regular
    Join Date
    Aug 2005
    Posts
    165
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't test it, but maybe this:

    Code:
    Sub openphiac()
    On Error GoTo Errormask
    Dim strfolder As String
    Dim strphiacfile As String
    
    strfolder = Range("folder")
    strphiacfile = Range("phiacfile")
    
    Workbooks.Open Filename:="O:\Phiac Data\PhiacTables\" & strfolder & "\" & strphiacfile & ".xls"
    Errormask:
    MsgBox "Text Here!"
    End Sub

  4. #4
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    works like a charm... except that the errmsg is coming up always... how do I tell it not to show that unless there IS an error?

    can I use "Is error" or something similar in an If or Select Case statement??

  5. #5
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,453
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    works like a charm... except that the errmsg is coming up always... how do I tell it not to show that unless there IS an error?
    Which code are you using?
    With the line Exit Sub before the line ErrMsg: it should quit the sub before
    showing the message if there is no error.

  6. #6
    Board Regular
    Join Date
    Aug 2006
    Location
    Australia
    Posts
    268
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ah ha. i'm just blind!

    added the Exit Sub and all is well.

    Thanks!!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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