Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA - Want MACRO to only run once

  1. #1
    Join Date
    17th February 2017
    Posts
    4

    Exclamation VBA - Want MACRO to only run once

    Hello!

    I am somewhat new to the world of VBA/Macros and have mainly stumbled through using them by way of copying and pasting what I find online.

    I am trying to make it so that when I have values in every cell of a column of tasks I have for myself (F5:F23), a sound effect goes off and a message box pops up saying I've completed them all. Trouble is, if I update any value in F5:F23 a SECOND time, the sound effect goes off again which I don't want it to do. I only want the sound effect/message to go off the FIRST time I complete all tasks (ie. sometimes I complete a task twice and update that particular column twice with a new value).

    I have programmed sound effects and gotten them to work, such as this one:

    VB:
    Private Declare Function PlaySound Lib "winmm.dll" _ 
    Alias "PlaySoundA" (ByVal lpszName As String, _ 
    ByVal hModule As Long, ByVal dwFlags As Long) As Long 
     
     
    Const SND_SYNC = &H0 
    Const SND_ASYNC = &H1 
    Const SND_FILENAME = &H20000 
     
     
    Function StarWars() 
        Call PlaySound("C:\Windows\Media\tada.wav", _ 
        0, SND_ASYNC Or SND_FILENAME) 
        StarWars = "" 
        MsgBox "Well done. You have obtained Queen status." 
    End Function 
    
    
    How can I make it so that a sound effect and message box goes off only the FIRST time I get all my tasks in F5:F23 completed? This is as close as I've gotten, from copying from this thread: http://www.ozgrid.com/forum/showthread.php?t=182386

    VB:
    Function TaskListCompleted() 
         
        If Range("AM23") = "" Then 
             
            Call PlaySound("C:\Windows\Media\tada.wav", _ 
            0, SND_ASYNC Or SND_FILENAME) 
            TaskListCompleted = "" 
            MsgBox "You completed Combo #1!" 
             
        End If 
         
        Range("AM23") = "Completed!" 
         
    End Function 
    
    
    When I try the code above, it says "Compile Error: Sub or Function not defined." I don't use Subs because I don't want to have to click a button, I just want it to happen naturally as part of a Formula.

    Thank you in advance! I have searched ALL over for something similar.

    Jake

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd October 2016
    Posts
    81

    Re: VBA - Want MACRO to only run once

    If you want to delete the macro so it never shows again, no matter what : http://www.vbaexpress.com/kb/getarticle.php?kb_id=511

    But that doesn't sound like what you are seeking. So ...

    If your second function (above) is working as desired, move the last line up:

    VB:
    Function TaskListCompleted() 
         
        If Range("AM23") = "" Then 
             
            Call PlaySound("C:\Windows\Media\tada.wav", _ 
            0, SND_ASYNC Or SND_FILENAME) 
            TaskListCompleted = "" 
            MsgBox "You completed Combo #1!" 
             
            Range("AM23") = "Completed!" 
             
        End If 
         
    End Function 
    
    
    That way the sound effect fires the first time, but not the second time because that last line (Range("AM23") = "Completed!" ) will make AM23 not blank.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th February 2017
    Posts
    4

    Re: VBA - Want MACRO to only run once

    Thank you for the quick response Logit!

    So, the 1st code I put in my original post works - I have made several sound effects work the same way. It's the 2nd code that I can't get to work. I just tried your suggestion and this is the error I received:
    Error.JPG

    How bout this - what's the simplest code possible it would take to have a macro fire once, and never again? Say the sound effect weren't even a part of it, say it were only the "You completed Combo #1!" message? What would that code look like?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd October 2016
    Posts
    81

    Re: VBA - Want MACRO to only run once

    This would be one way ...

    VB:
    Option Explicit 
     
     
    Sub TaskListCompleted() 
         
        If Sheets("Sheet1").Range("A1") = "" Then 
             
             ' Run your code here ... like playing the sound
            Sheets("Sheet1").Range("A1") = "Completed!" 
            ActiveWorkbook.Save 
            MsgBox "Macro Fired" '<-- for testing purposes. remove in final project
        ElseIf Range("A1") <> "" Then 
            MsgBox "Macro Did Not Fire" '<-- for testing purposes. remove in final project
            Exit Sub 
             
        End If 
         
    End Sub 
    
    
    You will need to change the Sheet name and Range as applicable.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th February 2017
    Posts
    4

    Re: VBA - Want MACRO to only run once

    Ahh, so I may have discovered something.. I think the issue is I want this to be a Function. When I use your exact code, changing my Sheets and Ranges, AND I use a button/Sub format - it works.

    Thing is, I don't want to have to use a button. I want it to happen automatically, when I type a final digit into the last remaining task in a column of tasks. Problem is, when I tried simply turning your code into a Function and putting it under my Modules, it comes up with a #VALUE or #NAME error and nothing happens...

    Any thoughts? I feel like my I run into this a lot where I'm always trying to turn people's Sub codes into a Function and I'm probably missing some fundamental understanding of Functions and Modules vs Microsoft Excel Objects, etc.

    I do feel like we're close though! Thank you for all your help, thus far. Very exciting.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd October 2016
    Posts
    81

    Re: VBA - Want MACRO to only run once

    Paste this macro into the sheet level module:

    VB:
    Option Explicit 
     
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If Sheets("Sheet1").Range("F23") <> "" Then 
             
             
            If Range("AM23") <> "" Then 
                 
                Exit Sub 
                 
            ElseIf Sheets("Sheet1").Range("AM23") = "" Then 
                 
                 ' Run your code here ... like playing the sound
                Sheets("Sheet1").Range("AM23") = "Completed!" 
                ActiveWorkbook.Save 
                 
            End If 
             
        End If 
    End Sub 
    
    
    Explanation of Sub vs Function

    http://excelhints.com/2009/02/12/dif...-and-function/

    Using Sub

    The easiest way to think of a sub vs a function is that a function can return a value and a sub cannot.

    A sub can be thought of as a small program that performs some action that is contained within the program. Subs can be used to update a cell or perform an import and calculation, but the result can’t be returned to another sub or function. Another thing to note is that a sub (or macro) cannot be accessed directly by a cell reference. For example, when a cell is used to show today’s date, =Today(), that formula is also a built-in function, not a sub.


    Using Function

    A function is similar to a sub, except that a function can return a value.

    It may be easier to think of a function as similar to a formula in excel. You can provide the function the necessary inputs and the function returns the desired value. You can build a custom function for just about any action and then access that function from either a call from a macro, or a direct reference from a cell.

    Sub = small program

    Function = returns a value

    Another way to explain it:

    Sub = something that does the actual work. “Hey ! Employee .. come here. I want you to go over there and do this for me. Make certain its done correctly and completely.”

    Function = something that gets information or a value for you that can be used elsewhere. “Hey ! Employee .. come here. I want you to go over there – compare this information I’ve just given you with what you find over there, and bring me back the answer. I don’t want you to do anything with the stuff, just bring me back the information.”

    At least, that’s my screwy way of describing it.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th February 2017
    Posts
    4

    Re: VBA - Want MACRO to only run once

    Thank you for this information! I was under the misconception Sub's always used a button but I see, now, that that isn't the case.

    I think I'm just about there. One thing I miscommunicated to you was that the 'combo' event would only go off when I had checked off the last task. Logically, you took that for meaning F23, if F23 was the last task in a list of F5 through F23. However, sometimes I get all the way through the list, checking them off randomly, and say F7 could be the last task. So what I erally need the formula to do is to check for EACH of them to be <> "" rather than just the last cell..

    I tried taking care of this myself with the code below, but got an error. Is there something I'm missing for checking multiple cells before continuing? In a normal formula, I would go "=IF(AND(F5<>"",F6<>"",F7<>""..........F23<>"")) - you get the picture!

    This is the code I tried, using what you had already given me:
    VB:
    Option Explicit 
     
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
        If Sheets("Jill").Range("F5:F23") <> "" Then 
             
            If Range("AM23") <> "" Then 
                 
                Exit Sub 
                 
            ElseIf Sheets("Jill").Range("AM23") = "" Then 
                 
                MsgBox "You completed Combo #1" 
                Sheets("Jill").Range("AM23") = "Completed!" 
                ActiveWorkbook.Save 
                 
            End If 
             
        End If 
    End Sub 
    
    
    Thank you SO much for your help, thus far!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: August 29th, 2013, 15:45
  2. Replies: 6
    Last Post: April 16th, 2011, 22:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno