Solved

Excel VB:  Creating a new page with a specific name

Posted on 2001-07-24
5
155 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks! May have some follow up questions as I implement -
0
 

Author Comment

by:robjay
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 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