Solved

vba skipping to another module

Posted on 2013-10-22
11
321 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 45

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 45

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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

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 45

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 45

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 45

Expert Comment

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

Marty - MVP 2009 to 2013
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now