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
Solved

vba skipping to another module

Posted on 2013-10-22
11
348 Views
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
    ActiveWorkbook.Connections("PressureDataQuery").Refresh
    Worksheets("Raw Data").Range("j3").Value = "Last Refreshed: " & Now()  
>>MODULE SWAP!!!
   
    '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
   
    rng1.Select
    rng1.Copy
    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")
    sheet.Select
    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(format).Copy
        Range(format2).PasteSpecial xlPasteFormats
>>MODULE SWAP!!!
        Range(source2).AutoFill destination:=.Range(dest2) ', Type:=xlFillDefault 'fills down to last row of data
>>MODULE SWAP!!!
        Range(source2).Select
    End With
   
    Worksheets("Pressure Data").Range("r1").Value = "Last Refreshed: " & Now()
>>MODULE SWAP!!!
Error_RefreshQuery:
    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
    ActiveChart.Axes(xlValue).Select
    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_Wells_Change:
    Exit Sub

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

End Sub
0
Comment
Question by:culpees
  • 6
  • 4
11 Comments
 
LVL 46

Expert Comment

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

Author Comment

by:culpees
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?
0
 
LVL 46

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.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 14

Expert Comment

by:Faustulus
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.
0
 

Author Comment

by:culpees
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.

Faustulus,
"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."
-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.
0
 
LVL 46

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.
0
 

Author Comment

by:culpees
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.
0
 
LVL 46

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?
0
 

Author Comment

by:culpees
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.
0
 
LVL 46

Accepted Solution

by:
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.
0
 
LVL 46

Expert Comment

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

Marty - MVP 2009 to 2013
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

790 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