Creating and Naming Worksheet in Excel VBA

Question

I have some very simple code that adds a new Worksheet, after the current worksheets, to an Excel document, and then changes its name to one entered in a text box on a userform. Works fine on a new workbook, however in a workbook that has a number of existing worksheets it creates the new worksheet, but does not rename it.

This only happens the first time you run this code, the next time it will run fine. The thing that makes it even stranger is that if you open the VBA editor to try and debug it, it then runs fine as well. This obviously makes finding the error pretty hard.

The code I'm using is here:

     Dim WS As Worksheet

     Set WS = Sheets.Add(After:=Sheets(Worksheets.count))
     WS.name = txtSheetName.value

Pretty simple. I'm wondering if this problem is that it is trying to rename the sheet before it is properly created? Is there a better way to write this code?

Update: I've started debugging this using msgboxes, as opening the debugger makes the problem stop, and it seems that it just stops processing the code halfway through:

  Dim WS As Worksheet
  MsgBox (WS Is Nothing)

    Set WS = Sheets.Add(After:=Sheets(Worksheets.count))
    '***** Nothing after this point gets processed *******
    MsgBox (WS Is Nothing)
    MsgBox WS.name

    WS.name = txtSheetName.value
    MsgBox WS.name

Answer 1

http://www.mrexcel.com/td0097.html

Dim WS as Worksheet
Set WS = Sheets.Add

You don't have to know where is it located, what's it's name, you just refer to it as WS.

If you still want to do this the "old fashioned" way, try this:

Sheets.Add.Name = "Test"

Answer 2

Are you committing the cell before pressing the button (pressing Enter)? The contents of the cell must be stored before it can be used to name a sheet.

A better way to do this is to pop up a dialog box and get the name you wish to use.

Answer 3

Are you using an error handler? If you're ignoring errors and try to name a sheet the same as an existing sheet or a name with invalid characters, it could be just skipping over that line. See the CleanSheetName function here

http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/

for a list of invalid characters that you may want to check for.

Update

Other things to try: Fully qualified references, throwing in a Doevents, code cleaning. This code qualifies your Sheets reference to ThisWorkbook (you can change it to ActiveWorkbook if that suits). It also adds a thousand DoEvents (stupid overkill, but if something's taking a while to get done, this will allow it to - you may only need one DoEvents if this actually fixes anything).

Dim WS As Worksheet
Dim i As Long

With ThisWorkbook
    Set WS = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
End With

For i = 1 To 1000
    DoEvents
Next i

WS.Name = txtSheetName.Value

Finally, whenever I have a goofy VBA problem that just doesn't make sense, I use Rob Bovey's CodeCleaner. It's an add-in that exports all of your modules to text files then re-imports them. You can do it manually too. This process cleans out any corrupted p-code that's hanging around.