Solved

Excel VB:  Creating a new page with a specific name

Posted on 2001-07-24
5
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 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…
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…

751 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