Macro error

[DJ]

Hi,


I am facing an issue with a code. I am a beginer and practicing with writing small codes. I just want to insert a worksheet with desired sheet name.


I wrote this code to work like this: if at first time, I don't enter any name or click on "cancel" button, it run macro again and ask for a name, if I enter a name, it should check if there isn't an existing sheet with this name. when both tests are clear it add a new worksheet with entered name.


When I enter a name at first instance, it is not giving any error and working well. However, if at first instance, I don't enter any name or click on cancel button then it is giving me an error and exicuting code even after exicuting "end sub".


Code is mentioned below:


Sub AddNewSheet()

Dim ws As Worksheet

Dim sname As String

sname = Application.InputBox("Enter desired Sheet Name", "InputBox", , , , , , 2)

If sname = vbNullString Or sname = "False" Then

MsgBox "Please enter a name"

Run "addnewsheet"

End If

For Each ws In ActiveWorkbook.Sheets

If ws.Name = sname Then

MsgBox "Sheet with this name exists, please enter a unique name"

Run "addnewsheet"

End If

Next ws

Sheets.Add after:=Sheets(Sheets.Count)

ActiveSheet.Name = sname

End Sub


Please help.

DJ

 

[DJ]

Hi,


In addition to the first post, I know that my code has got stuck in a loop and I can get rid of it by using "Exit Sub" but I don't want to exit from code, I want that if I don't enter a name, it just keep asking me until I enter a name.


Thanks,

DJ

 

[Hui]

DJ


Fixing your first problem

just change these few lines:

[pre]

10: sname = Application.InputBox("Enter desired Sheet Name", "InputBox", , , , , , 2)
If sname = vbNullString Or sname = "False" Then
MsgBox "Please enter a name"
GoTo 10
End If

[/pre]

 

[DJ]

Hi Hui,


Thank you very much. I worked now.


Plz help me understand the mistake I did. As I told in my first post that I am a beginner so I do not want to repeat this error.


Thanks,

DJ

 

[DJ]

Hi Hui,


Plzzzz... help me understand the mistake I did. In my code I was asking code to start again from the beginning to take input again (incase entered no string) and as per your suggestion I am asking code to take the input again. The problem is I am unable to see any much difference so seeking your help once again.


Also, I want to draw your attention towards the error I noticed in my code (that I mentioned my my first post) that if I do not enter any string or press the "Cancel" button at the first time and then enter a unique name in second attempt, it inserts a new sheet with the entered name and execute the "End Sub" but again it executes code from the middle i.e. was in a loop.


Plz help me understand why, coz ideally, once "End Sub" is executed, code should not run and stop that point only.


Please help.

DJ