33

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
Community
  • 1
  • 1
Sam Cogan
  • 4,124
  • 7
  • 44
  • 76
  • 1
    Excel has many ways to keep you amused. Often you can't do anything about it. Try various relatively useless things, such as using `Worksheets` collection instead of `Sheets` or assigning `txtSheetName.value` to a string variable first. – GSerg Oct 01 '10 at 16:39
  • It looks like you're setting the name before it's in txtSheetName. Are you running this code off of the txtSheetName_Change event? If not, that would probably fix the problem. – Marc Thibault Oct 02 '10 at 16:13
  • @Marc the event is run from a button event, which validates if the text box has been set – Sam Cogan Oct 03 '10 at 10:20
  • Cue the theremin. Looks like it's time for goat's blood and chicken feathers. Is there somewhere you can drop the xls file for download? – Marc Thibault Oct 04 '10 at 00:51
  • @marc unfortunately not as it's full of data, and only occurs in that sheet (or ones with similar amounts of data) smaller sheets do not have the issue. – Sam Cogan Oct 04 '10 at 10:26
  • Try adding the sheet without assigning it to a Worksheet variable, `Sheets.Add After:=Sheets(Worksheets.Count)` and then setting the `ActiveSheet.Name`. Do you get the same error? – David Zemens Apr 20 '13 at 03:31

3 Answers3

42

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

Dim WS as Worksheet
Set WS = Sheets.Add

You don't have to know where it's located, or what it's name is, you just refer to it as WS.
If you still want to do this the "old fashioned" way, try this:

Sheets.Add.Name = "Test"
zigzaugg
  • 61
  • 10
Sage
  • 4,769
  • 1
  • 21
  • 28
  • How is that different to what the OP is doing? – GSerg Oct 01 '10 at 16:16
  • I didnt write the article, I pointed him to a source. when I tested the code in the article, specifically the [Sheets.Add.Name = "test"] it worked flawlessly. – Sage Oct 01 '10 at 16:24
  • 4
    Yes, I've tried that. The problem is, it does work flawlessly the second time, or of you open the debugger or the sheet is blank, or the moon is in the third quarter ... – Sam Cogan Oct 03 '10 at 10:21
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.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Unfortunately not, I'm not ignoring errors, and the name does work the second time you run it which says to me that it's ok. – Sam Cogan Oct 03 '10 at 10:23
0

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.

Sprague
  • 1,610
  • 10
  • 22