Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA - Want MACRO to only run once

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

    Code:
    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

    Code:
    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

  • #2
    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:

    Code:
    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.

    Comment


    • #3
      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:
      Click image for larger version

Name:	Error.JPG
Views:	1
Size:	39.4 KB
ID:	1128197

      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?

      Comment


      • #4
        Re: VBA - Want MACRO to only run once

        This would be one way ...

        Code:
        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

        Comment


        • #5
          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.

          Comment


          • #6
            Re: VBA - Want MACRO to only run once

            Paste this macro into the sheet level module:

            Code:
            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

            Comment


            • #7
              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:
              Code:
              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!

              Comment

              Trending

              Collapse

              • claudia80
                create hyperlink
                claudia80
                In the "GEONAMES" file, I would like to insert a macro (with the start button in cell "1I", with the text in the file in this cell), the hyperlink to cells in column "D" with the files excel in the "GEONAMES - FILE EXCEL" folder.

                https://www.dropbox.com/s/y4b48fv3ab...rlink.rar?dl=0
                1 day ago
              • ebracky
                How to force cells to return n/a if left blank?
                ebracky
                How do I create a code that forces specific cells to show or return N/A is they are left blank or empty?

                This is the code that I created but it only works if all cells are left empty. I want them to work in such a way that if one, or two, or three or all of the cells are empty it will return/show N/A in it.


                -----------------------------------------------
                Sub CopyEvenEmptyCell()

                ''''''''''''''''''''''''''''''''''''''''''''
                'Forces cell to show N/A if blank
                ''''''''''''''''''''''''''''''''''''''''''''

                Dim isMyCellEmpty As Boolean
                isMyCellEmpty = IsEmpty(Range("C3,E3,C4,E4"))

                If isMyCellEmpty = True Then
                Range("C3,E3,C4,E4").Value = "N/A"

                End If
                ...
                1 week ago
              • GillG
                Range find pulling value from the wrong cell
                GillG
                Thanks for your help the other day...my first user form is working great, but I have hit another snag.

                I have a second user form and I want it to populate with certain cell values when I double click my list box, the same as with my first user form. I have used the same code as I did in the first user form, but change the range of cells that I want some of the user form to be populated with.

                The code I have for the first user form is:

                Code:
                Private Sub lstLookup_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
                'declre the variables
                Dim cRef As String
                Dim I As Integer
                Dim findvalue As Range
                
                'error block
                On Error GoTo errHandler:
                'get the value from the listbox
                For I = 0 To lstLookup.ListCount -
                ...
                1 day ago
              • Ace.McCloud
                Can someone help me build a web scraping tool?
                Ace.McCloud
                Here's the data
                1 day ago
              • darth_chunk
                Auto Hide Columns and/or Rows using VBA based on cell value (validation list)
                darth_chunk
                Hi,

                I'm wondering if anyone can help me with this VBA problem I'm having.

                To lay the backdrop; my understanding of VBA is limited and I've been reading tutorials to implement the functions I want. However, when trying to stack multiple 'If' arguments the code isn't behaving the way I expected it to.

                In short, I am using VBA to auto hide columns and or rows depending on the value of a cell which has a pre-determined list using a validation drop down box. The aim of this is to hide a range of columns, or multiple ranges of columns.

                So in the attached example, "SuperHero Patrol" when selecting 'Jan' from the drop down in Cell A1, I want the sheet to auto hide all columns where the date isn't January. When selecting 'Feb' it auto hides...
                3 days ago
              Working...
              X