Solved

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

Posted on 2015-01-31
11
159 Views
Last Modified: 2015-02-14
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
0
Comment
Question by:rberke
[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
  • 7
  • 4
11 Comments
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40581334
Can you please, save, reopen your file and send a picture of project explorer pane?
0
 
LVL 5

Author Comment

by:rberke
ID: 40581568
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40581608
Can you attach your file, at least without codes, connections or other personel things?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:rberke
ID: 40581781
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40581846
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
 
LVL 5

Author Comment

by:rberke
ID: 40582300
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
 
LVL 5

Author Comment

by:rberke
ID: 40582315
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
 
LVL 5

Assisted Solution

by:Hakan Yılmaz
Hakan Yılmaz earned 500 total points
ID: 40582345
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
 
LVL 5

Author Comment

by:rberke
ID: 40582395
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
 
LVL 5

Accepted Solution

by:
rberke earned 0 total points
ID: 40582409
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
 
LVL 5

Author Closing Comment

by:rberke
ID: 40609563
Neither the responding export nor I could figure out what caused the problem.
I "fixed" it by rebuilding the workbook.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

710 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