Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

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
0
rberke
Asked:
rberke
  • 7
  • 4
2 Solutions
 
Hakan YılmazTechnical Office MEP EngineerCommented:
Can you please, save, reopen your file and send a picture of project explorer pane?
0
 
rberkeAuthor Commented:
0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
Can you attach your file, at least without codes, connections or other personel things?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now