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 47

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 47

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.
Independent Software Vendors: 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!

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 47

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 47

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 47

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 47

Expert Comment

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

Marty - MVP 2009 to 2013

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

739 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