vba skipping to another module

Posted on 2013-10-22
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 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 48

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 48

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 48

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 48

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 48

Accepted Solution

Martin Liss earned 500 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 48

Expert Comment

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

Marty - MVP 2009 to 2013

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 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