How to fix the error of "Run-time error: 9"? 2

[feipezi]

Hi,

I tried to delete some of the tabs that are to be replaced by Export of SAS (Statistical Analysis Software) application. But as I ran it, I got error 9 on the yellow line below and on error resume next won't work. The funny thing is that as I debugged, Excel pointed to some macros that had nothing much to do with the deleting process (like UpdateChart below). How can leave UpdateChart alone as I delete those tabs? I tried EnableEvents=0 but not working.

What should I do?

Thanks in advance.

Sub DeleteSheets()
On Error Resume Next
Application.DisplayAlerts = False
For Each st In Worksheets
If UCase(st.Name) <> "REPORTMONTHS" And _
      UCase(st.Name) <> "CRITERIA" And _
      UCase(st.Name) <> "GRAPHGEODATASHEET" And _
      UCase(st.Name) <> "GRAPHDATASHEET" And _
      UCase(st.Name) <> "NOTES" And _
      UCase(st.Name) <> "DATAVIEW" And _
      UCase(st.Name) <> "VOLUMESHARECHARTDATA" And _
      UCase(st.Name) <> "VIEWGRAPHS" And _
      UCase(st.Name) <> "VOLUMESHARECHARTS" And _
      UCase(st.Name) <> "HELP TAB" And _
      UCase(st.Name) <> "PRODMKT" And _
      UCase(st.Name) <> "GEOPLANLIST" Then
         st.Delete
End If
Next
Application.DisplayAlerts = 1
End Sub

Private Sub UpdateChart()
Application.ScreenUpdating = False
Application.EnableEvents = False
'On Error GoTo errHandler
Dim strSelection As String
Dim wsD As Worksheet
Dim wsC As Worksheet
Dim wsE As Worksheet

Set wsD = Worksheets("VolumeShareChartData")
Set wsC = Worksheets("RegionKeyAcctMetrics")
Set wsE = Worksheets("VolumeShareCharts")
Set wsF = Worksheets("AreaKeyAcctMetrics")
Set wsG = Worksheets("DistrictKeyAcctMetrics")
Set wsH = Worksheets("PodKeyAcctMetrics")
Set wsR = Worksheets("RDAMKeyAcctMetrics")
Set wsK = Worksheets("StateKeyAcctMetrics")

strSelection = Range("AA1").Text
'MsgBox "strselect " & strSelection
...
...

 

[SkipVought]

hi,

Do you actually STILL have a sheet named EXACTlY, [highlight]DistrictKeyAcctMetrics[/highlight]?

I'd use code like this for the delete sheet

Sub DeleteSheets()
    Dim st As Worksheet
    
    Application.DisplayAlerts = False
    
    For Each st In Worksheets
        Select Case UCase(st.Name)
            Case "REPORTMONTHS", "CRITERIA", "GRAPHGEODATASHEET" 'etc the sheets you want to KEEP
            Case Else
                st.Delete
        End Select
    Next
    
    Application.DisplayAlerts = True
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

You likely have some event driven code (see worksheet and workbook modules). In this case you need to disable them:
Application.EnableEvents=False
...
your code
...
Application.EnableEvents=True

combo

 

[feipezi]

Hi,

Sorry about the delay getting back to you.

To Skip, no, I don't have that particular tab as the error happens. My question is the Sub UpdateChart has nothing to do with the deletion process.

To combo, I agree with you 100% about some Events have been invoked but not supposed to. But I have about 70 tabs in the workbook and tons of events. I cannot disable all of them, can I? You can see I've disabled the Sub UpdateChart but it did not help.

More weird things happened as the following. This is a test macro.

Sub testMacro()
Sheet9.Activate
Range("B1997").Select
ActiveCell.FormulaR1C1 = "National"
Selection.AutoFill Destination:=Sheets("GEOLIST").Range("B1997:B2004"), Type _
:=xlFillDefault
Sheet9.Activate
End Sub


sheet9 is the same thing as Sheets("GEOLIST"). I tried to keep the tab active but Excel won't let me. It always directs me to other tabs after running testMacro(). I hid those tabs. Then other tabs become active except the one I want. Anything like this happened to you before?

Thanks in advance.

 

[SkipVought]

some Events have been invoked but not supposed to

HUH? What is that supposed to mean?

Events are envoked when certain things happen and when those certaing things happen, those events are supposed to happen!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Yes, you can, Application.EnableEvents=False tells excel to ignore all events handling. Excel stops passing information about events to potential event procedures in all workbooks after this command (and opposite, setting this property to True restores passing information about events to code). The structure above can be in ONE procedure, events will be disabled in ALL workbooks as long as your code runs within those two commands. Typically this is used to disable in code action that has to work as response to user action only.
Mind that:
- if your code breaks when EnableEvents=False events will stay disabled, can be restored in immediate window for instance,
- only excel objects' events can be disabled, MSForms controls and UserForm still work.




combo

 

[feipezi]

To Skip, what I meant is that the sub UpdateChart was not supposed to react as I tried to delete the tabs; in addition, I had EnableEvents=false statement. In another word, I did not call UpdateChart as I deleted the tabs.

To combo, are you saying ONE EnableEvents=false will disable all the events in a particular workbook? I thought it only disable the activities within a sub.

Thank you both again.

 

[SkipVought]

have you tried stepping thru your code to see where this phantom is rising?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PHV]

IMHO you have to try Application.EnableEvents=False before the delete stuff, and then tell us what's happen

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

It is APPLICATION.EnableEvents, you set property for the whole excel instance, it is persistent until you close excel.

Suppose you have Worksheet_Change event procedure in worksheet's module and standard TestProc procedure that changes this worksheet. More or less event handling works in this way:
CODE, TestProc, line x: change cell
EXCEL: hey, a range was changed in a specific worksheet,
VBA: ok. If any event procedure was interested in changes in in this worksheet, breaks TestProc and executes all event procedures involved,
VBA: return control to TestProc,
CODE, TestProc, line x+1: continue procedure.

Disabling events changes "hey" response, you are left with:
CODE, TestProc, line x: change cell
(?) EXCEL: hey, a range was changed in a specific worksheet,
VBA: don't care,
CODE, TestProc, line x+1: continue procedure.


combo

 

[feipezi]

Hi folks,

Thanks for all your efforts. Now I have something new to report to you.

As I press 'RunQuery' button, which should bring up new data on a show tab through AdvanceFilter, it will end up with a tab that is supposed to be hidden, which I knew by running "Msgbox Activesheet.Name". But it looks like I'm on the show tab with RunQuery button on it. As I tried to press the button again, it felt like the screen was frozen.

I've run the process for a while and it did not happen before.

I thought the workbook was corrupted so I updated an old workbook with the new data but it gave me the same thing. I rebooted the PC but still the same.

Btw, APPLICATION.EnableEvents is in a macro UpdateChart, which is a regular macro not an Event macro like Sub Worksheet_Change(). Is that going to serve the same purpose? Not sure about it.

Thanks again for all your help.

 

[PHV]

As clearly stated above, Application.EnableEvents should be called just before the delete sheets stuff and just after.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886