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

User · Answer

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.

User · Answer

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

User · Answer

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

[vba] Creating and Naming Worksheet in Excel VBA

Examples related to vba

Examples related to excel