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


vba skipping to another module

Posted on 2013-10-22
Medium Priority
Last Modified: 2013-10-23
I have no idea why but every time the code below performs an action on my workbook it automatically skips it to another module to a piece a code which is triggered by a change to a drop down list by the user. There's no reason that I can see for this code to constantly be bouncing to the drop down list code.  It works fine....it just insists on also going through this other module as well...many times over.
(The other module (on the drop down list) errors and forces it to exit the sub which it then returns to the below code and continues on until it does this odd thing again.)

I kind of have the idea that somehow this code has parasitically attached itself to this and another tab called "Pressure Data" or "Raw Data" so anytime anything happens to one of these tabs, the code runs. I notice if I try to edit a formula on these tabs this code runs. But there is no code specific to these tabs and nothing tying them together. How can I troubleshoot this?

Here's the code which swaps to the other module at certain points; I've marked in CAPS where it's shifting to the other module. And btw...i'm not a vba wiz if you can't tell.
Sub RefreshQuery()
    On Error GoTo Error_RefreshQuery  
    'Refreshes query
    Worksheets("Raw Data").Visible = True
    Worksheets("Raw Data").Select
    Worksheets("Raw Data").Range("j3").Value = "Last Refreshed: " & Now()  
    'Copies query data to Pressure Data tab
    Dim latestRow As Long ' determine last row
    Dim str1 As String 'determine range values
    Dim rng1 As Range  'put range syntax together
    Dim wks As Worksheet
    Set wks = Sheets("Raw Data")
    With wks
        latestRow = .Range("A" & .Rows.Count).End(xlUp).Row
        str1 = "'Raw Data'!$A$3:$G$" & latestRow
        Set rng1 = .Range(str1)
    End With
    Worksheets("Pressure Data").Select
    Range("C3").PasteSpecial Paste:=xlPasteValues
    Worksheets("Raw Data").Visible = False
    'Drags formulas down sheet into new rows
    Dim sheet As Worksheet
    Dim lastform As Long 'finds last formula row
    Dim lastrow As Long 'finds last row of raw data (aka last row in destination range)
    Dim source As String, source2 As String  'builds range text for formula row
    Dim dest As String, dest2 As String   'builds range text for destination range
    Dim format As String, format2 As String
    Set sheet = ActiveWorkbook.Sheets("Pressure Data")
    With sheet
        lastform = .Range("J" & .Rows.Count).End(xlUp).Row
        lastrow = .Range("H" & .Rows.Count).End(xlUp).Row
        source = "$A" & lastform & ":$B" & lastform
        source2 = "$J" & lastform & ":$T" & lastform
        dest = "$A" & lastform & ":$B" & lastrow
        dest2 = "$J" & lastform & ":$T" & lastrow
        format = "$C" & lastform & ":$I" & lastform
        format2 = "$C" & lastform & ":$I" & lastrow
        Range(source).AutoFill destination:=.Range(dest) ', Type:=xlFillDefault 'fills down to last row of data
        Range(format2).PasteSpecial xlPasteFormats
        Range(source2).AutoFill destination:=.Range(dest2) ', Type:=xlFillDefault 'fills down to last row of data
    End With
    Worksheets("Pressure Data").Range("r1").Value = "Last Refreshed: " & Now()
    MsgBox Err.Description
    Exit Sub
End Sub

Here's the annoying code which keeps getting activated:
Private Sub Wells_Change()
On Error GoTo Error_Wells_Change

'wait 2 seconds then reset axes on chart
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)

ActiveSheet.Unprotect "abc"

ActiveSheet.ChartObjects("Chart 16").Activate
    With ActiveChart.Axes(xlValue)
        .MaximumScale = Range("$u$7")
        .MinimumScale = Range("$t$7")
        .MinorUnit = Range("$x$7")
        .MajorUnit = Range("v7")
        .MinorUnitIsAuto = Range("$w$7")
        .MajorUnitIsAuto = True
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MaximumScale = Range("$u$8")
        .MinimumScale = Range("$t$8")
        .MinorUnit = Range("$x$8")
        .MajorUnit = Range("v8")
        .MinorUnitIsAuto = Range("$w$8")
        .MajorUnitIsAuto = True
    End With
ActiveSheet.Protect "abc"

    Exit Sub

    Dim R As Range
    Set R = Range("$r$7")
    If IsError(R.Value) = True Then
        Resume Exit_Wells_Change
        'MsgBox Err.Description
        Resume Exit_Wells_Change
    End If

End Sub
Question by:culpees
  • 6
  • 4
LVL 49

Expert Comment

by:Martin Liss
ID: 39591741
Can you attach your project?

Author Comment

ID: 39591811
I knew you'd ask that. no. not really. It's far too difficult to remove all proprietary data. Everything would break then. I've tried but removing elements of the file it keeps shutting down the file. ??

Is there any reason why the problem code would be triggered by something outside of the original trigger (drop down box)?  In VBA Editor, the problem code is only on the "Summary" tab. And the data in the drop down box which serves as reference is NOT coming from either of the two tabs being modified by the desired module. Does the file need to be repaired?
LVL 49

Expert Comment

by:Martin Liss
ID: 39591901
Do you know how to use Debug? If you do, put a breakpoint on the Range(format2).PasteSpecial xlPasteFormats line and step through the code to see what's happening. In case you don't know how to use Debug, click in the left-hand margin of that line and a red dot will appear which is the breakpoint. When the code gets there use F8 to step through the code as it executes.

For more information on debugging see my article on debugging. Don't be put off because it says VB6 since most of it applies to VBA.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 14

Expert Comment

ID: 39593175
Your error description seems to point at the presence of an event procedure that fires when a certain cell is changed. Use
Application.EnableEvents = False
to turn off Excel's response to changes that you make to any cells in your workbook and
Application.EnableEvents = True
to turn them back on after changes have been made. The best places to add the code might be just before you lift the protection and after you reinstate it.

Author Comment

ID: 39594103
MartinLiss, Thanks for your input but I actually did debug it already. That's how I knew exactly where the code was switching to the other module.

Application.EnableEvents = False
to turn off Excel's response to changes that you make to any cells in your workbook and
Application.EnableEvents = True
to turn them back on after changes have been made."
-I want events to run though. Else, the problem code itself will never run.

Thank you for your suggestions. Maybe this will help...
The problem appears to only switch to the protected sheet's code when there's a change to any tab in the workbook. eg when it pastes something, autofills, or applies the date stamp. OR when I manually make a change, that code also runs. So ANY change to the workbook and the protected sheet's code runs.
LVL 49

Expert Comment

by:Martin Liss
ID: 39594120
Okay but when you put a breakpoint on that line and after the code gets there you press F8, where do you wind up? I wouldn't be surprised if it's code in the Worksheet_Change event.

Author Comment

ID: 39594142
It goes straight to the same code. The Wells_Change() code. Not a worksheet_change() code.

That's what I don't get. I've been programming MS Access for years and understand events and troubleshooting. But I don't understand here why this code keeps getting activated when it's set specifically to a drop down box called "Wells" and my other module does nothing to activate that drop down box. What's additionally odd is that I have 3 other drop down boxes all with the EXACT same code and those do not get activated. Just this one.
LVL 49

Expert Comment

by:Martin Liss
ID: 39594182
I know it wouldn't be able to run, but could you supply a workbook with no data, just the code?

Author Comment

ID: 39594912
Well, well, well.
I created a dummy file. Removed all extraneous sheets, data, changed and replaced values for use in testing.
The dumb thing is working now. I went back and checked my original file and it's working too.
I assume restarting my machine has something to do with it?

I hate Microsoft. Well, it's a love-hate thing.
LVL 49

Accepted Solution

Martin Liss earned 1500 total points
ID: 39594932
I assume restarting my machine has something to do with it?
Most likely. I'm happy it's resolved and maybe I inadvertently put you on the right path but I don't need any points.
LVL 49

Expert Comment

by:Martin Liss
ID: 39595313
I'm glad I was able to help.

Marty - MVP 2009 to 2013

Featured Post

Industry Leaders: 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

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.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

926 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