Solved How to run a macro automatically

October 17, 2014 at 06:30:22
Specs: Windows 7
I have a macro in my spread sheet which returns a value in cell G154 and would like the macro to run automatically if a value of 13 is returned in cell F154. Is that possible ?

See More: How to run a macro automatically

Report •


✔ Best Answer
October 19, 2014 at 07:56:00
OK, I don't have a lot of time to play with this right now, but I will point out a few things so that you can move forward.

Feel free to read through this entire post, but please be aware that the First and Last points that I am going to make are the most important ones.

First, in an earlier post I suggested that you read this tutorial, which will give you some pointers on debugging VBA code. I can't imagine trying to write code without using at least some these debugging techniques. I use them all the time...in fact I just used a few of them to find some of the problems with your code.

http://www.computing.net/howtos/sho...

By using these techniques, specifically F8 to Single Step through your code, you can watch what the code is doing, instruction by instruction, so that you can see where your problems are. Once again, I can't imagine trying to write code without using at least some these debugging techniques.

Second, you said "The bottom line is that by me inserting a value"13" in cell F154 it will not automatically run the macro and give me a result in G154."

In my case, this statement is only partially true. The code will run automatically, but it will not produce a result in G154 because of the errors.

If I paste this code into the sheet module for Sheet1 and then change any cell in Sheet1, the code definitely runs automatically. How do I know this? I know this because as soon as I make a change to the worksheet the code displays the "Block If without End If" error and highlights the first line of the code. It would not display this error if it was not running automatically.

OK, so if we want to eliminate that error, we have to make sure that every If section has an associated End If. Your code appears to be missing several End If instructions.

For example:

'Use Find and FindNext to search for 9 within search range
    If Sheet1.Range("F154") = 9 Then
      With Range("D2:D151")
        Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
          If Not num Is Nothing Then
            firstAddress = num.Address
              Do
 'Build temp string for each 9 found
                tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
                  Set num = .FindNext(num)
             Loop While Not num Is Nothing And num.Address <> firstAddress
         End If
      End With
'Strip off extra "; " and place result in G154

There is no End If for the primary If:

If Sheet1.Range("F154") = 9 Then

This issue is present though out your code. You are missing the End If instructions all of the If sections that look like the one above.

Last (and this is extremely important) once you fix the Block If issues and eliminate the error, you are going to run into another problem. Since this is a Worksheet_Change macro, it is going to fire whenever the worksheet is changed, either manually by the user, or automatically by the code. If the code puts a value in G154, then that will be a change to the worksheet and the code will fire again, putting a value in G154, changing the worksheet and firing again, putting a value in G154, changing the worksheet and firing again, and again and again. In some cases you may not notice this, but in other cases it will cause the code to fail.

The way to handle this is to have the code Disable Events, make the changes, and then - this is extremely important - have the code Enable Events again.

For example, this simple code shows how that is done:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable Events
  Application.EnableEvents = False
'Change the worksheet and display a message
    Range("A1") = 5
     MsgBox "I put 5 in A1"
'Enable Events
  Application.EnableEvents = True
End Sub

Without the Application.EnableEvents = False instruction, the code will change the worksheet, the VBA Event Handler will see the change and run the code again and again and again, continuously trying to put a 5 in A1 until it finally throws up an error (best case) or runs until you use the Break key to stop it.

There is one other very important point to be aware of when using the Application.EnableEvents = False technique:

If the code fails or if you stop the code after the Application.EnableEvents = False instruction has been processed, but before the Application.EnableEvents = True instruction is executed, Events will remain disabled and Event macros will no longer run. For this reason, I often keep a small piece of code handy to re-enable events if my event code fails. If you manually run this code at any time, it will ensure that events are enabled and that your event code will run when it is supposed to.

Sub TurnEventsOn()
  Application.EnableEvents = True
End Sub

I know that this is a lot to absorb, but once you understand these concepts they will make sense and you'll see how powerful these event macros can be. Once again, I can not stress enough how important it is to use the debugging techniques described in the tutorial. Without them, all you can do is run the code, (possibly) get an error and not have any idea what is wrong. What's even worse than getting an error is when your code runs to completion - with no errors - but does not give you the results you expect. For example, if you are expecting one result in G154 for a given input, but you get a different result, how are you going to find the problem? You'll find the problem by Single Stepping through the code and watching when it branches off in a direction that you didn't expect it go.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
October 17, 2014 at 07:50:51
You could use the 'SheetChange' event to check cell F154 and test the returned value. If the value is 13 then run your macro, otherwise do noting.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Sheet1.Range("F154") = 13 Then
        
        ' Run your macro here
        
    End If

End Sub



Report •

#2
October 17, 2014 at 09:10:36
Do I include the 'SheetChange" event in the same module as the macro, because if so it does not run automatically when the value 13 is entered in cell F154

Report •

#3
October 17, 2014 at 16:07:17
All Sheet Event macros must be stored in the Sheet module for the Sheet in which you want them to run. However, the code can reference other sheets. In other words, an event macro gets fired by an event that occurs in the Sheet whose module the code is stored. Once it fires, an event macro runs (more or less) like any other macro.

In addition, you can only have one instance of any given type of Sheet Event macro (Worksheet_Change, Worksheet_SelectionChange, etc.)

If you want more than one thing to happen per event or if you want one thing to happen when one cell changes but something else to happen if a different cell changes, you simply use If-Then clauses within the same event macro to test for a specific event or value.

e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$F$2" Then
       'do something
   End If

   If Target.Row = 4 Then
       'do something
   End If

   If Sheets("Some Other Sheet").Range("$B$2") = "Tom" then
      'do something
   End If

   'etc.
End Sub

One final note: A change caused by a calculation will not fire a Worksheet_Change macro. You would use the Worksheet_Calculate event if you want something to happen whenever the sheet calculates.

e.g. See here...

http://msdn.microsoft.com/en-us/lib...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 18, 2014 at 07:32:07
I'm still confused, i am not that familiar with macro's. I have 6 modules with macro's in a worksheet that require a different cell value in F154 for each one to run. Do i include a sheetchange in each module in order for them to function automatically.

Report •

#5
October 18, 2014 at 10:26:20
Rather than having me try to explain the different types of VBA Modules, I'll let a real expert do it.

http://www.cpearson.com/excel/codem...

The bottom line is that Worksheet event macros need to be stored in the Sheet Code Module for the sheet in which the event will happen. In other words, if you want a Worksheet_Change macro to run when something in Sheet1 changes, then that code must by stored in the Sheet Code Module for Sheet1.

An easy way to access the Sheet Code Module for any sheet is to right click the sheet tab and choose "View Code". This will open the VBA Editor and display the Sheet Code Module for that sheet. That's where you store the sheet event code for that sheet, and only for that sheet. Another way to access the Sheet Code Module for a given sheet is to double click it's name in the Project Viewer window within the VBA editor. The title bar at the top of the VBA Editor window will tell you which module you are currently viewing.

Once in a Sheet Code Module, click the drop down arrow next to General and choose Worksheet. Now click on the drop down arrow to the right of SelectionChange to view all of the sheet level events that can be monitored.

The ThisWorkbook module is an interesting module. Code stored in this module can be used to run macros when Workbook level events happen, such as Printing, Saving, Closing, etc. Similar to the drop downs in the Sheet Code Module, use the drop downs to view the various Workbook level events that can be used to trigger code.

BTW...if you want some tips on debugging VBA code, take a look at this tutorial:

http://www.computing.net/howtos/sho...

Finally, you should become familiar with the personal.xlsm workbook. If you create a workbook called personal.xlsm and store it in the XLSTART folder, you can make your macros available to any workbook that you open.

The typical method for using the personal.xlsm workbook is as follows:

1 - Create a workbook and store your macros in Standard Modules within the workbook
2 - Hide the Workbook
3 - Save it in your XLSTART folder, using the name personal.xlsm

Whenever you open Excel, the personal.xlsm workbook will open - hidden - and the macros stored in it will be available for use in all workbooks. I created icons to access my most commonly used macros and stored them on my Quick Access toolbar. A simple click of an icon and the macro runs. Note: this does not work for Sheet Level or Workbook Level events. That code must be stored in the Workbook module or sheet module for the specific workbook or sheet which you are working with.

Let's use an example of why you would want to create a personal.xlsm workbook to store your macros. Let's say you have a common task that you perform multiple times a day in multiple workbooks. For example, I have client worksheets where I have to manually update values in Column C, sometimes up to 30 different values. The reports that contain the updated values are never in the same order as the values in Column C so I can't update values in order. Since I don't want to miss any values, I needed a way to make sure I updated every value.

I wrote a macro that copies the original Column C values to Column P (an unused column) and sets up Conditional Formatting which compares Column C to Column P to turn the cell Yellow if the values differ. As I change each value in Column C, the cell turns Yellow. All I have to do is look at Column C and if all cells in my range are Yellow, I know that I have updated every value. Since this is a task that I do multiple times a day in multiple workbooks and worksheets, I stored the code in my personal.xlsm workbook so that it is available in every workbook I open, whether I need it or not. I have an icon on my Quick Access toolbar to launch the macro whenever I need it. (Note: The macro is written such that if Column P is empty, it copies the values and sets up the Conditional Formatting. If Column P already contains the copied values, then it clears that column and deletes the Conditional Formatting. Clicking the icon essentially "toggles" the macro.)

The point here is that if you find yourself performing the same task over and over again, you can probably write a macro to perform that task. If you will be performing that task in multiple workbooks, you might as well make the code available whenever Excel is open. I've lost count of how many macros I have stored in my personal.xlsm workbook, but I know I have at least 20 icons on my Quick Access tool bar. The trick is remembering what each icon does. ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 18, 2014 at 19:14:36
Thanks for all the information but its a bit too much for me to comprehend. The bottom line is that by me inserting a value"13" in cell F154 it will not automatically run the macro and give me a result in G154. It works well by manually running the macro so i will continue to do it that way.

Report •

#7
October 19, 2014 at 05:04:32
Why does the macro below show a Compile Error "Block IF without End If" when i attempt to run it.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Sheet1.Range("F1") = 9 Then
   With Range("D1:D5")
    Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
        tempnames = tempnames & "- " & Range("A" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
    If tempnames <> "" Then
        Range("G1") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G1") = "- No Jackpot Winners for this round"
    End If
End Sub


Report •

#8
October 19, 2014 at 05:33:03
Regarding Response #6...

Since you didn't post the exact code that you are trying to use, all I can do is guess at what the problem might be.

It works well by manually running the macro so i will continue to do it that way.

That statement may be the key to your issue. Generally, you can not run an event macro manually, so my guess is that you are not using an event macro. The first line of an event macro must be in this format, which is what tells VBA to run the code when the specific event occurs:

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Private Sub Worksheet_Activate()

Private Sub Worksheet_Calculate()

If your macro starts with Sub or Public Sub, then it is not an event macro and will not run automatically. It must start with

Private Sub Worksheet_the name of a specific event(parameters for that event)

If you'll post the exact code that will not run automatically, we'll see if we can help further.

Regarding Response # 7...

Since this appears to be a different question, unrelated to how to have a macro run automatically, it should be posted in its own thread with a subject line that is relevant to the question. Please repost your question in a new thread and we'll they to help out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
October 19, 2014 at 06:29:05
This is the code for the macro i'm attempting to run automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Sheet1.Range("F154") <= 8 Then
  If Application.WorksheetFunction.CountIfs _
    (Sheets(1).Range("D2:D151"), ">=0", Sheets(1).Range("D2:D151"), "<=8") _
  Then
     Sheets(1).Range("G154") = "- No Jackpot this week, less than 9 games played"
  Else: Sheets(1).Range("G154") = ""
  End If

'Use Find and FindNext to search for 9 within search range
    If Sheet1.Range("F154") = 9 Then
   With Range("D2:D151")
    Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
 'Build temp string for each 9 found
            tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
 'Strip off extra "; " and place result in G154
 'or place No Winners in G154
    If tempnames <> "" Then
        Range("G154") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G154") = "- No Jackpot Winners for this round"
    End If

'Use Find and FindNext to search for 10 within search range
    If Sheet1.Range("F154") = 10 Then
    With Range("D2:D151")
    Set num = .Find(10, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
 'Build temp string for each 10 found
            tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
 'Strip off extra "; " and place result in G154
 'or place No Winners in G154
    If tempnames <> "" Then
        Range("G154") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G154") = "- No Jackpot Winners for this round"
    End If

'Use Find and FindNext to search for 11 within search range
 If Sheet1.Range("F154") = 11 Then
   With Range("D2:D151")
    Set num = .Find(11, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
 'Build temp string for each 11 found
            tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
 'Strip off extra "; " and place result in G154
 'or place No Winners in G154
    If tempnames <> "" Then
        Range("G154") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G154") = "- No Jackpot Winners for this round"
    End If
    
'Use Find and FindNext to search for 12 within search range
    If Sheet1.Range("F154") = 12 Then
    With Range("D2:D151")
    Set num = .Find(12, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
 'Build temp string for each 12 found
            tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
 'Strip off extra "; " and place result in G154
 'or place No Winners in G154
    If tempnames <> "" Then
        Range("G154") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G154") = "- No Jackpot Winners for this round"
    End If
    
'Use Find and FindNext to search for 13 within search range
    If Sheet1.Range("F154") = 13 Then
    With Range("D2:D151")
    Set num = .Find(13, lookat:=xlWhole, LookIn:=xlValues)
      If Not num Is Nothing Then
        firstAddress = num.Address
          Do
            tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
            Set num = .FindNext(num)
          Loop While Not num Is Nothing And num.Address <> firstAddress
      End If
   End With
    If tempnames <> "" Then
        Range("G154") = Left(tempnames, Len(tempnames) - 2)
     Else: Range("G154") = "- No Jackpot Winners for this round"
    End If
End Sub


Report •

#10
October 19, 2014 at 07:56:00
✔ Best Answer
OK, I don't have a lot of time to play with this right now, but I will point out a few things so that you can move forward.

Feel free to read through this entire post, but please be aware that the First and Last points that I am going to make are the most important ones.

First, in an earlier post I suggested that you read this tutorial, which will give you some pointers on debugging VBA code. I can't imagine trying to write code without using at least some these debugging techniques. I use them all the time...in fact I just used a few of them to find some of the problems with your code.

http://www.computing.net/howtos/sho...

By using these techniques, specifically F8 to Single Step through your code, you can watch what the code is doing, instruction by instruction, so that you can see where your problems are. Once again, I can't imagine trying to write code without using at least some these debugging techniques.

Second, you said "The bottom line is that by me inserting a value"13" in cell F154 it will not automatically run the macro and give me a result in G154."

In my case, this statement is only partially true. The code will run automatically, but it will not produce a result in G154 because of the errors.

If I paste this code into the sheet module for Sheet1 and then change any cell in Sheet1, the code definitely runs automatically. How do I know this? I know this because as soon as I make a change to the worksheet the code displays the "Block If without End If" error and highlights the first line of the code. It would not display this error if it was not running automatically.

OK, so if we want to eliminate that error, we have to make sure that every If section has an associated End If. Your code appears to be missing several End If instructions.

For example:

'Use Find and FindNext to search for 9 within search range
    If Sheet1.Range("F154") = 9 Then
      With Range("D2:D151")
        Set num = .Find(9, lookat:=xlWhole, LookIn:=xlValues)
          If Not num Is Nothing Then
            firstAddress = num.Address
              Do
 'Build temp string for each 9 found
                tempnames = tempnames & "- " & Range("C" & num.Row) & "; "
                  Set num = .FindNext(num)
             Loop While Not num Is Nothing And num.Address <> firstAddress
         End If
      End With
'Strip off extra "; " and place result in G154

There is no End If for the primary If:

If Sheet1.Range("F154") = 9 Then

This issue is present though out your code. You are missing the End If instructions all of the If sections that look like the one above.

Last (and this is extremely important) once you fix the Block If issues and eliminate the error, you are going to run into another problem. Since this is a Worksheet_Change macro, it is going to fire whenever the worksheet is changed, either manually by the user, or automatically by the code. If the code puts a value in G154, then that will be a change to the worksheet and the code will fire again, putting a value in G154, changing the worksheet and firing again, putting a value in G154, changing the worksheet and firing again, and again and again. In some cases you may not notice this, but in other cases it will cause the code to fail.

The way to handle this is to have the code Disable Events, make the changes, and then - this is extremely important - have the code Enable Events again.

For example, this simple code shows how that is done:

Private Sub Worksheet_Change(ByVal Target As Range)
'Disable Events
  Application.EnableEvents = False
'Change the worksheet and display a message
    Range("A1") = 5
     MsgBox "I put 5 in A1"
'Enable Events
  Application.EnableEvents = True
End Sub

Without the Application.EnableEvents = False instruction, the code will change the worksheet, the VBA Event Handler will see the change and run the code again and again and again, continuously trying to put a 5 in A1 until it finally throws up an error (best case) or runs until you use the Break key to stop it.

There is one other very important point to be aware of when using the Application.EnableEvents = False technique:

If the code fails or if you stop the code after the Application.EnableEvents = False instruction has been processed, but before the Application.EnableEvents = True instruction is executed, Events will remain disabled and Event macros will no longer run. For this reason, I often keep a small piece of code handy to re-enable events if my event code fails. If you manually run this code at any time, it will ensure that events are enabled and that your event code will run when it is supposed to.

Sub TurnEventsOn()
  Application.EnableEvents = True
End Sub

I know that this is a lot to absorb, but once you understand these concepts they will make sense and you'll see how powerful these event macros can be. Once again, I can not stress enough how important it is to use the debugging techniques described in the tutorial. Without them, all you can do is run the code, (possibly) get an error and not have any idea what is wrong. What's even worse than getting an error is when your code runs to completion - with no errors - but does not give you the results you expect. For example, if you are expecting one result in G154 for a given input, but you get a different result, how are you going to find the problem? You'll find the problem by Single Stepping through the code and watching when it branches off in a direction that you didn't expect it go.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
October 19, 2014 at 18:34:57
That's all good and well to explain that and is much appreciated but with my very limited knowledge, knowing exactly where and how to apply these events in the code is very difficult given that just the smallest error renders the code inoperable

Report •

#12
October 19, 2014 at 19:43:27
re: knowing exactly where and how to apply these events

Apply what events?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
October 19, 2014 at 23:24:41
We are obviously talking on different levels and going no where fast. Best just forget about the whole idea and I'll persue other sources.

Report •


Ask Question