macro skips end sub

[joecast]

here is a strange one, hopefully someone know why this is happening or has seen something like this before.

i have a query update that is running from either a calendar or combobox on another sheet.

when i step through the macro, it starts out fine, but when it gets to the end of the macro for the query refresh, it skips the end sub part and jumps to a different macro that runs off a seperate sheet all together. i end up with a run time error telling me the pivot refresh method failed (even though when i normally run that pivot refresh its fine.)

here is some of the code:

this is off the combobox which starts it off. the refresh roster allocation is run from here.

Private Sub ServZoneBox1_Change()
    Worksheets("Service Zone Viewer").Activate
    ActiveSheet.Unprotect
    Cells(6, 5).Value = ServZoneBox1.Value
    RefreshRosterAllocation 'in main module
    RefreshResourceCapacity 'in main module
    Worksheets("Service Zone Viewer").Activate
    ActiveSheet.Protect
End Sub

here is the end of the roster allocation query

     wsBMIS.Cells(1, 1).ListObject.QueryTable.CommandText = query

    'refresh BMIS Data
    On Error GoTo BMISFail2
    wsBMIS.Cells(1, 1).ListObject.QueryTable.Refresh BackgroundQuery:=False
BMISFail2:
    
End Sub

it runs to "wsBMIS.Cells(1, 1).ListObject.QueryTable.Refresh BackgroundQuery:=False", and instead of running end sub and returning to the initiating macro, it jumps to a private sub on a different sheet module.

and here is where i end up with the runtime error

Private Sub Worksheet_Activate()
    PivotTables("PivotTable1").RefreshTable
End Sub

any help would be appreciated. the rest of the workbook is running fine except for this error. cheers
joe

 

[SamTYler]

I'l ponder what's happening here first

CommandText = query

 

[joecast]

is this what you are asking for?

query = "SELECT " & _
            "[Southern Water$Roster Allocation].[Resource No_], " & _
            "[Southern Water$Roster Allocation].[Allocation Date], " & _
            "[Southern Water$Roster Allocation].[Roster Code], " & _
            "[Southern Water$Roster Allocation].Quantity, " & _
            "[Southern Water$Resource].[Service Zone Allocation] " & _
            "FROM " & _
            "[TWSC Dynamics NAV].dbo.[Southern Water$Resource] [Southern Water$Resource], " & _
            "[TWSC Dynamics NAV].dbo.[Southern Water$Roster Allocation] [Southern Water$Roster Allocation]" & _
            "WHERE " & _
            "[Southern Water$Roster Allocation].[Resource No_] = [Southern Water$Resource].No_ AND ((" & _
            "[Southern Water$Roster Allocation].Quantity=$1) AND (" & _
            "[Southern Water$Roster Allocation].[Allocation Date] Between " & sqlStartDate & " AND " & sqlEndDate & ") AND (" & _
            "[Southern Water$Resource].[Service Zone Allocation]='" & SDTeam & "'))"

 

[parry]

Hi, it appears there's more code than you posted but somewhere you have activated the sheet and hence the Worksheet_Activate code will run. Either dont select sheet, or prevent this code by running via Application.EnableEvents = False then turn back on events (i.e. make true) when your finished.

regards,
Graham

 

[joecast]

Either dont select sheet, or prevent this code by running via Application.EnableEvents = False then turn back on events (i.e. make true) when your finished.

regards,
Graham

thanks Graham, i think that last part of your post may be the issue.
what i had been trying to do was fill two comboboxes on different sheets with one common list of data. so for example sheet1 and sheet2 both have comboboxes with ListFillRange coming from sheet3 (so i could update one list rather than maintaining two seperate lists for the same data).
those comboboxes also had vba code for "Private Sub ServZoneBox1_Change()".
would using that EnableEvents method allow me to make changes to each sheet independently without running the combobox change sub?
hope that makes sense. thanks
joe

 

[parry]

Hi, the Change event happens when you select a value from the ComboBox - is this what you are doing? If your using ListFillRange then you indicate a range, Excel only supports this range if entered as an address (eg A1:A10) on the same sheet as the control but if you define a Name (i.e. Formula>Define Name in 2007 or off Insert menu in 2003) then this can be sitting on any sheet so the ListFillRange property can just refer to this named range. This may avoid changing the range if thats what your doing - you could simply change the Named range cells.

Often you dont need to select the sheet or cell to do something with it. If your selecting the sheet merely to update cells then this is unnecessary.