how to delete a very very hidden sheet? (vba sheet1.anyproperty gives error 429)

vba IDE show 4 sheets. Sheet1 and Sheet4 are weird because they have the icon and properties that are normally associated with ThisWorkbook.

I cannot delete those sheets.
Worksheets.count is 2 NOT 4, so I cannot enumerate them using <for each sheet in worksheets>
debug.print typename(sheet1) gives error 429 Activex component cant create object
sheet.visible = true will not compile, it gives "method or data member not found"

I can view the sheets in the IDE and they clearly contain obsolete code like this
   Private Sub CommandButton1_Click()
    End Sub
When I use f4 to view properties, Sheet1 & 4 do not have the 13 properties of a normal sheet. Instead, they have the 35 properties that are normally associated with ThisWorkbook.
f4 show that .name is "ThisWorkbook" for BOTH Sheet1 and Sheet4 !!!

How can I delete them?  (I don't want to "start over" by copying everything to a new workbook so  I will not give points for that answer.)

Here is what the IDE looks like in the project explorer pane

icon codename  sheetname
 -----  ---------------   -----------------
        January       january
x      sheet1
        sheet2        February
        sheet3        March
x      sheet4
x      Thisworkbook
LVL 5
rberkeConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rberkeConnect With a Mentor ConsultantAuthor Commented:
Anyway, I ended up rebuilding the sheet as follows.
Grouped all worksheet with shift click on sheet names.
right click to make copy in a new workbook.  (Excel moved all the worksheet code automatically.)
saved the work book as new.xls (excel 97 to 2003)
Manually copied the Thisworkbook code and each module from  old.xls to new.xls.

For some reason, the resulting workbook was not quite done - none of the vba code would run because activex would fail.  I used the macro recorder to record to this workbook and selected a random cell then stopped the recorder.
Excel automatically fixed the activex problem and now I am done.


I'll leave this open another day, then PAQ it.  I'll give you points because of you valiant attempts.
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
Can you please, save, reopen your file and send a picture of project explorer pane?
0
 
rberkeConsultantAuthor Commented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Hakan YılmazTechnical Office MEP EngineerCommented:
Can you attach your file, at least without codes, connections or other personel things?
0
 
rberkeConsultantAuthor Commented:
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
I see only one worksheet in your file. Other documents seems like embedded in your vba project.
I didn't understand at all.
Are you using Excel or other spreadsheet software?
0
 
rberkeConsultantAuthor Commented:
It may very well be that my only choice is to rebuild the sheet.  (I might even try the vbaexpress Excel Workbook Rebuilder program which I haven't used in years.)

the small number of worksheets is because I deleted everything I could before sending it to you.  I just want to make it simple.  Of course, I could not delete Sheet1 and Sheet5, because they are "very very hidden".

You said <<I didn't understand at all.>>  and neither do I.  (I have been doing Excel/Outlook/Access vba development for 15 years and am pretty good at it.)

The workbook is my "Daily Playpen and Timesheet" which is a '.xls' file I have been using every day for about 10 years.  When I want to test something interesting or weird in Excel, I often test inside this workbook.  When I noticed the problem two days ago, I tried converting the book to xlsm.  Excel gave me a few conversion warning messages which looked 'normal', and seemed unrelated to the very very hidden worksheets.

Sometime in the last 10 years one of my little experiments must have gone wrong leaving this garbage behind.

Don't waste anymore of your time on this. I will leave this open for a few more days in the hopes that someone else has a suggestion.
0
 
rberkeConsultantAuthor Commented:
By the way, I tried the following, but the last line gives error 5 invalid procedure call or argument.
    Sub DeleteModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
    
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Sheet1")
        VBProj.VBComponents.Remove VBComp
    End Sub

Open in new window

0
 
Hakan YılmazConnect With a Mentor Technical Office MEP EngineerCommented:
They don't look like "very very hidden" sheets. Seems something different.
When you click them, properties window shows properties of "ThisWorkbook". I think they don't even have these properties.

I was saved it as xlsm and looked at xml files in it (don't know if it lost some data), there are no trace about them for beign a sheet.
0
 
rberkeConsultantAuthor Commented:
I think we agree that they are weird.  



I found one more clue. For many years, sheets("timesheet") had a command button named "askanother".


The button worked when things looked like this

CodeName  Sheet Display Name      vba code
--------   -----------------      ------------      
Sheet1     (TimeSheet)             Private Sub askAnother_Click()
                                     call askquestion
                                   End Sub

About two months ago, the button stopped working.  To fix it, I just reassigned the macro,
I wasn't paying much attention back then, and I'll bet something weird happened that I just did not notice.

Now things look like this.

CodeName  Sheet Display Name      vba code
--------   -----------------      ------------      
Sheet1                             Private Sub askAnother_Click()
                                                 call askquestion
                                               End Sub
     
Sheet11     (TimeSheet)             Private Sub askAnother_Click()
                                                 call askquestion
                                               End Sub
0
 
rberkeConsultantAuthor Commented:
Neither the responding export nor I could figure out what caused the problem.
I "fixed" it by rebuilding the workbook.
0
All Courses

From novice to tech pro — start learning today.