Solved

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

Posted on 2015-01-31
11
138 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
  • 7
  • 4
11 Comments
 
LVL 5

Expert Comment

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

Author Comment

by:rberke
Comment Utility
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
Comment Utility
Can you attach your file, at least without codes, connections or other personel things?
0
 
LVL 5

Author Comment

by:rberke
Comment Utility
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 5

Author Comment

by:rberke
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Neither the responding export nor I could figure out what caused the problem.
I "fixed" it by rebuilding the workbook.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now