Error handler: two different errors generate the same error code

[BlackieHamel]

I'd like to construct an errorhandler: section with if-then-else logic based on different errors that arise. The two most common errors both generate the same error code: 1004. They occur when:

1. The user attempts to create a new worksheet with a name that already exists;
2. The subroutine attempts to import data from an external file that exists, but has 0 bytes.

Is there a way I can create an error handler that returns a different MsgBox for each case?

Blackie

 

[Joe4]

Can you post the VBA code relevant to these two parts?

 

[BlackieHamel]

Can you post the VBA code relevant to these two parts?

Here is the first part of the code that can generate a 1004 error:

abc = UCase(InputBox("Trigram to be created?", "Enter a 3-letter string"))

and later, when the same macro inputs a textfile generated by an external database:

    'filename = Worksheets("Tool").Range("G7")
    filenamelong = "c:\tc\output" & abc & ".txt"
    Open filenamelong For Input As #1
    i = 1
    Do While Not EOF(1)
    Line Input #1, textline
    Worksheets(abc).Cells(i, 2) = textline
    Worksheets(abc).Range("A" & CStr(i)) = "=RemainingLetters(B" & CStr(i) & "," & Chr(34) & abc & Chr(34) & ")"
    i = i + 1
    Loop
    Close #1

If the "OUTPUTabc.TXT file thus imported has a size of 0 bytes, it generates the same 1004 error.

Thanks.

 

[Joe4]

Where's the part of your code that is trying to create the new sheet (issue #1 that you mentioned)?
I don't see any code that would be creating/renaming a worksheet. Or do you mean "creating a new workbook" instead of "creating a new worksheet" (they are not the same thing).

 

[BlackieHamel]

Where's the part of your code that is trying to create the new sheet (issue #1 that you mentioned)?
I don't see any code that would be creating/renaming a worksheet. Or do you mean "creating a new workbook" instead of "creating a new worksheet" (they are not the same thing).

Sorry -- here are the lines I should have included:

     abc = UCase(InputBox("Trigram to be created?", "Enter a 3-letter string"))
   Sheets.Add
    ActiveSheet.Name = abc
    Application.GoTo Reference:="R1C1"

Blackie

 

[Joe4]

You can use the code in the following link to see if the worksheet name already exists before trying to add it. Then that should take that error out of the equation.
http://www.mrexcel.com/forum/excel-...if-worksheet-exists-workbook.html#post2923164

 

[BlackieHamel]

Thanks, Joe. The link you sent takes me to 4 pages of messages in which the OP keeps reporting that the code as presented creates more errors. So I'm a little timid about trying that.

I think maybe my question is a little more elementary. I gather from your suggestion that one can handle a situation before an error is triggered. I already have a working UDF to determine if a worksheet exists. I just don't know how to invoke it from within VBA -- I use it in the input form to manually warn the user:

=IF(G5="","",IF(WorksheetExists(G5),"That file already exists!",""))

But if the user ignores the warning, the error is triggered. How would I use my UDF from within VBA to require user input before Excel registers an error?

Blackie

 

[Joe4]

Focus in on post #2. All the other stuff is specific to his example, but the code in post 2 is generic and will work for you.
The first block of code is the UDF. Just copy it, exactly as it appears.

The second block of code is an example of how to call it from your VBA code. So right before the Sheets.Add line in your code, i.e.

   abc = UCase(InputBox("Trigram to be created?", "Enter a 3-letter string")) 
    
    If Not WorksheetExists(abc) Then
         MsgBox "Unable to Proceed", vbExclamation, "ERROR!"
         Exit Sub
   End If

   Sheets.Add 
   ActiveSheet.Name = abc
   Application.GoTo Reference:="R1C1"

It doesn't matter what they click on the Message Box, because the "Exit Sub" is kicking them out of the entire Macro! So it will never get to the Sheets.Add line.

 

[BlackieHamel]

Thanks, Joe! Works like a charm.

Blackie