Solved

Excel VB:  Creating a new page with a specific name

Posted on 2001-07-24
5
175 Views
Last Modified: 2010-08-05
With VB in Excel, I'm attempting to create a new page with a specific name but it seems like the internal name given every time is Sheet1, Sheet2, etc.  I also need some VB code to check for existence of this page and delete it.
0
Comment
Question by:robjay
  • 2
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
DennisBorg earned 50 total points
ID: 6314510
Simple example of adding a WorkSheet to the ActiveWorkbook and specifying its name:

   ActiveWorkbook.Worksheets.Add().Name = "TestMe"

To see if a worksheet exists with a certain name, you can use the following function:

Public Function WorkSheetExists(ByVal wsName As String) As Boolean
   Dim ws As Worksheet
   Dim rv As Boolean   'Return Value
   
   For Each ws In ActiveWorkbook.Worksheets
      If StrComp(wsName, ws.Name, vbTextCompare) = 0 Then
         rv = True
         Exit For
      End If
   Next 'ws
   Set ws = Nothing
   
   WorkSheetExists = rv
End Function


Sample code to delete a WorkSheet:

   ActiveWorkbook.Worksheets("Sheet2").Delete


If you want a single sub to delete a worksheet if it exists, you could use the following along with the WorkSheetExists given earlier:

Public Sub KillWorkSheet(ByVal SheetName As String)
   If WorkSheetExists(SheetName) Then
      ActiveWorkbook.Worksheets(SheetName).Delete
   End If
End Sub


-Dennis Borg
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6314532
Sub AddNewSheet(SheetName As String)
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Sheets
    If wsh.Name = SheetName Then
        wsh.Delete
        Exit For
    End If
Next
Set wsh = ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))
wsh.Name = SheetName
End Sub
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6314583
In my previous post, I illustrated how you can perform some different tasks:

   1) Add a sheet with a given name
   2) Delete a sheet
   3) How to tell if a sheet exists
   4) How to "AutoKill" a sheet if it exists (sub KillWorkSheet)


In the event you want to be able to add a sheet with a given name, and if a sheet by that name already exists to simply delete it and then add a new one with that name, you can use the following sub:

Public Sub AddSheet(ByVal SheetName As String)
   On Error GoTo ErrHandler
   
   ActiveWorkbook.Worksheets.Add().Name = SheetName
   Exit Sub
   
ErrHandler:
   Select Case Err.Number
      Case 1004:
         ActiveWorkbook.Worksheets(SheetName).Delete
         Resume
      Case Else:
         With Err
            .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
         End With
   End Select
End Sub
0
 

Author Comment

by:robjay
ID: 6314933
Thanks! May have some follow up questions as I implement -
0
 

Author Comment

by:robjay
ID: 6324193
Public Sub AddSheet(ByVal MySheet As String)
On Error GoTo ErrHandler

ActiveWorkbook.Worksheets.Add().Name = "MySheet"
Exit Sub

ErrHandler:
Select Case Err.Number
    Case 1004:
        ActiveWorkbook.Worksheets(MySheet).Delete
        Resume
    Case Else:
    With Err
        .Raise .Number, .Source, .Description, .HelpFile, .HelpContext
    End With
End Select
End Sub

I get an error:  subscript out of range when this is ran with MySheet in existance and highlight the Delete statement.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now