Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VB:  Creating a new page with a specific name

Posted on 2001-07-24
5
Medium Priority
?
220 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 200 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

971 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