Delete Sheets in VBA Project

  • Thread starter Thread starter Frank Rudd via OfficeKB.com
  • Start date Start date
F

Frank Rudd via OfficeKB.com

I've just completed a project that kind of developed "scope creep" (everybody
wanted me to add something), and even before I've linked it to other
workbooks, there will be four others, it's already kind of big. I've been
trying to clean up the code, and I see that in the project window of the VB
window it still lists worksheets that are no longer there. The interesting
thing is that the workbook originally contained 75 worksheets, that I've
condensed down to 8, but the project window shows those 8 plus 5 that have
been deleted and two chart sheets. It never had charts to begin with. I've
tried to delete these, there's no code in them, but I don't have that option
in the file menu. Are these adding to the file size? If so, how do I get rid
of them?

Any help is appreciated.
 
Hi,
there might be hidden sheets in the workbook.
try the below,
(if the workbook was corrupt, it might crash excel)

Sub Test()
Dim vbc As Object
Debug.Print "***** " & ActiveWorkbook.Name & " *****"
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = 100 Then
If Not vbc.Properties("Parent").Object Is Application Then
Debug.Print vbc.Name, vbc.Properties("Name"), _
IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden")
End If
End If
Next
End Sub
 
I did this...what was supposed to happen?
Hi,
there might be hidden sheets in the workbook.
try the below,
(if the workbook was corrupt, it might crash excel)

Sub Test()
Dim vbc As Object
Debug.Print "***** " & ActiveWorkbook.Name & " *****"
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = 100 Then
If Not vbc.Properties("Parent").Object Is Application Then
Debug.Print vbc.Name, vbc.Properties("Name"), _
IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden")
End If
End If
Next
End Sub
I've just completed a project that kind of developed "scope creep" (everybody
wanted me to add something), and even before I've linked it to other
Click to expand...
[quoted text clipped - 9 lines]
Any help is appreciated.
Click to expand...
Click to expand...
 
The worksheets were hidden. The code didn't find them, but for anybody else
who has this problem, I opened the properties window and while one of the
sheets that wasn't supposed to be there was selected, and saw that it was
hidden. Deleting these made a significant difference, as one might expect.

Frank said:
I did this...what was supposed to happen?
Hi,
there might be hidden sheets in the workbook.
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
Hi,
that macro outputs result to 'immidiate' window in the visual basic
editor. for example,

***** Book1.xls *****
Sheet1 Sheet1 Visible
Sheet2 Sheet2 Visible
Sheet3 Sheet3 Visible

the 1st column is sheet name in the vb project, the 2nd column is name
in the workbook.
in this list, are there the sheets that you are seeing in 'project' window?
are they all visible?
if there are not all sheets, try the following macro. this lists all
vbcomponents in the active workbook. a number in the 2nd column is
component type.(1:standard module, 2: class module, 3: userform)

Sub Test2()
Dim vbc As Object
Debug.Print "***** " & ActiveWorkbook.Name & " *****"
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = 100 Then
If Not vbc.Properties("Parent").Object Is Application Then
Debug.Print vbc.Name, vbc.Properties("Name"), _
IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden")
End If
Else
Debug.Print vbc.Name, vbc.Type
End If
Next
End Sub

if they are unusual hidden sheets, the project might be corrupt.

--
HTH,

okaizawa

The worksheets were hidden. The code didn't find them, but for anybody else
who has this problem, I opened the properties window and while one of the
sheets that wasn't supposed to be there was selected, and saw that it was
hidden. Deleting these made a significant difference, as one might expect.

Frank said:
I did this...what was supposed to happen?

Hi,
there might be hidden sheets in the workbook.
Click to expand...

[quoted text clipped - 19 lines]
Any help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Okay, thanks.
Hi,
that macro outputs result to 'immidiate' window in the visual basic
editor. for example,

***** Book1.xls *****
Sheet1 Sheet1 Visible
Sheet2 Sheet2 Visible
Sheet3 Sheet3 Visible

the 1st column is sheet name in the vb project, the 2nd column is name
in the workbook.
in this list, are there the sheets that you are seeing in 'project' window?
are they all visible?
if there are not all sheets, try the following macro. this lists all
vbcomponents in the active workbook. a number in the 2nd column is
component type.(1:standard module, 2: class module, 3: userform)

Sub Test2()
Dim vbc As Object
Debug.Print "***** " & ActiveWorkbook.Name & " *****"
For Each vbc In ActiveWorkbook.VBProject.VBComponents
If vbc.Type = 100 Then
If Not vbc.Properties("Parent").Object Is Application Then
Debug.Print vbc.Name, vbc.Properties("Name"), _
IIf(vbc.Properties("Visible") = -1, "Visible", "Hidden")
End If
Else
Debug.Print vbc.Name, vbc.Type
End If
Next
End Sub

if they are unusual hidden sheets, the project might be corrupt.
The worksheets were hidden. The code didn't find them, but for anybody else
who has this problem, I opened the properties window and while one of the
Click to expand...
[quoted text clipped - 9 lines]
Click to expand...
 
Back
Top