Runtime error 1004 replace with msgbox

[juslearning]

Hi guys and gals,

Am attempting to write a macro to create sheets automatically, have done the major bit and all works well but want to replace the runtime error 1004 'cannot rename sheet etc' when I accidently enter the same name of an existing sheet.

I would like to replace it with my own msgbox, so as to replace the end and debug options. Have tried several pieces of code but to no avail, can anyone please help.

thanks again.

 

[Loomah]

Try

On Error GoTo handler

    'Your code

Exit Sub
handler:
If Err.Description = "'cannot rename sheet etc'" Then
    'etc etc
End If
Resume Next 'or whichever option you want

Get more ifo by looking at the Err object in help and error handling in general.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[juslearning]

thanks for that, have put that into my code and the runtime error does not display now, however the macro runs through and does the rest. how can i stop it there and make it display the userform again.

here is the code that makes the error message go away and displays my msgbox(test) and is nested into my other code

On Error GoTo handler

MsgBox ("test")
UserForm1.Show


Exit Sub

yet when i click cancel in the userform it creates the sheet anyway with the default name, if i then click OK again (twice now) and then cancel it will create two sheets with default names

 

[vladk]

juslearning,

Set reference to Microsoft Scripting Runtime (Tools/References).

Put text box (to type some sheet name) and command button (to add and name the sheet) on the UserForm.

Add this code to the form:

Private Sub CommandButton1_Click()

On Error GoTo MethodExit

Dim dctSheets As Dictionary

Set dctSheets = GetSheetNames

If Not dctSheets.Exists(Trim$(UCase$(TextBox1.Text))) Then
ThisWorkbook.Sheets.Add
ActiveSheet.Name = Trim$(TextBox1.Text)
Else
MsgBox "Please give new name to the sheet!"
End If

MethodExit:

If Err.Number <> 0 Then
MsgBox Err.Description
End If

End Sub

Private Function GetSheetNames() As Dictionary
Dim dctReturn As New Dictionary
Dim objSheet As Worksheet

dctReturn.Add "", Nothing

For Each objSheet In ThisWorkbook.Sheets
dctReturn.Add Trim$(UCase$(objSheet.Name)), objSheet
Next objSheet

Set GetSheetNames = dctReturn

Set objSheet = Nothing
Set dctReturn = Nothing

End Function

vladk