CODEWITHSUNDEEP
vbaexcel
crismelb
crismelb

Reputation: 21

Running excel macros only on one sheet

Hi Iam new to writing VBA code and need assistance. My VBA code and Macro works fine as long as I am on the active sheet.

My Problem: My VBA code and macro, stops running automatically, when i change from the active sheet to another within the same workbook and My VBA code and macro, stops running automatically, when i open a new excel workbook

Solution required: Run the VBA code and macro only on desired worksheet and prevent it from running on other worksheets and workbooks.

Background: I have an excel file, named "Net Weight" with two sheets. sheet 1 is named : "weight", sheet 2: is named "base data". sheet 1 is used as a user input form.

In sheet 1- cell B1 : user will type in a product code , in cell E1: a look up formula will place the description of the product code using the data from sheet 2

I have setup a VBA code and macro that does the following:

  1. As soon as a user inputs a product code into cell B1, in sheet 1, sheet 1 is saved as PDF file into a predefined folder location data from cell B1 and E1
  2. A macro saves and overwrties the PDF file every 10 seconds.
  3. This process is repeated every time a new product code is entered

There are no buttons on sheet 1, everything is done automatically.

Here is my current code:

Sheet 1: set as Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Call Macro1
    End If
End Sub

Module 1 macro : set as general

Sub Macro1()
    Application.DisplayAlerts = False
    If ThisWorkbook.Name = "Nett Weight.xlsm" And ActiveSheet.Name = "Weight" Then
        Sheets("Weight").ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\Nett weight\" & Range("B1 ").Text & Range(" E1").Text
        Application.OnTime Now + TimeValue("00:00:10"), "Macro1"
    Else
        Exit Sub
    End If
End Sub

Upvotes: 2

Views: 8423

Answers (1)

zaphodalive
zaphodalive

Reputation: 233

First, you need to create a public variable to hold your timer, otherwise you'll never be able to cancel it so it will continue trying to fire even when your workbook is closed. You should also create a public variable to store when the timer is running, so you can check before creating a new timer.

At the top of a code module put:

Public nextTime As Date

Then in your Workbook_BeforeClose() event method (within ThisWorkbook), disable the existing timer so it doesn't keep trying to fire after the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error Resume Next   ' Continue with next line of code if we encounter an error
    Application.OnTime Earliesttime:=nextTime, Procedure:="Macro1", Schedule:=False
    On Error GoTo 0   ' Resume error-trapping

End Sub

In Macro1() you should explicitly and directly reference your workbook components - ThisWorkbook always refers to the workbook the code is running from, so you don't need your If statement. Then you set the nextTime and activate the timer using that variable if it is not already running.

Sub Macro1()

    Dim sht As Worksheet   ' Creates a variable to hold your Weight worksheet
    Set sht = ThisWorkbook.Sheets("Weight")   ' Sets the reference

    Application.DisplayAlerts = False

    sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Nett weight\" & sht.Range("B1").Text & sht.Range("E1").Text   ' Remember to preceed Range with sht. to explicitly reference the range of your Weight worksheet

    On Error Resume Next   ' Continue with next line of code if we encounter an error
    Application.OnTime Earliesttime:=nextTime, Procedure:="Macro1", Schedule:=False
    On Error GoTo 0   ' Resume error-trapping

    nextTime = Now + TimeSerial(0, 0, 10)   ' Adds 10 seconds to Now
    Application.OnTime Earliesttime:=nextTime, Procedure:="Macro1", Schedule:=True
    timerIsRunning = True

    Application.DisplayAlerts = True   ' Remember to enable alerts at the end of code

End Sub

Your Worksheet_Change() event method can stay as is. Now if there is a change in B1 it will call Macro1(). Macro1() will save the Weight worksheet as a PDF regardless of whether the workbook or worksheet is active, and create a new timer to re-run Macro1() every 10 seconds after deactivating an existing timer. When you're finished with the workbook, you close it and the existing timer is also deactivated.

EDIT:

Fortunately (as it fixes a spreadsheet of my own) I have figured out why the solution I originally provided wasn't working. Placing the Public variables under ThisWorkbook meant they no longer held their values after code execution. The remedy was to place them in a module instead. Once that was sorted out, I also realised that when the timer fires to call Macro1() it will throw an error when trying to unschedule the existing timer (as none exists unless Macro1() was called ad hoc by the Worksheet_Change() event).

Long story short: Public variables have been moved to a code module, and the timerIsRunning flag has been removed entirely and errors when attempting to unschedule the timer are simply ignored in the event that no timer exists.

Upvotes: 1

Related Questions