A little help with VBA Macro

[Moonbeam111]

Hi all. Thank you for your time. I have a new question. I'm trying to NOT call a macro when a value changes but ONLY for the first time the value changes. After that the macro should run normally. To make this clear, here is my code.

Sub macroSheet_Button390_Click()
Application.ScreenUpdating = True
Static oldval As Integer
Static numeral As Integer
Dim hasrun As Boolean

Range("D27") = Range("D27") - Range("A33").Value

If Range("A195") <> oldval Then
oldval = Range("A195").Value
numeral = numeral + 1
hasrun = True
End If


If numeral = Range("A195") And Range("G47").Value > Range("F47").Value and hasrun = true Then
GoTo D27
End If

If hasrun = False Then
Call Switch5p3
End If

D27:
If Range("D27").Value < 0 Then
Range("D27").Value = 0
End If

If Range("D27").Value = 0 Then
Range("D37:I37").ClearContents
ActiveWindow.ScrollRow = 129
End If

Rows("31:35").EntireRow.Hidden = True

hasrun = False
End Sub

As you can see I'm trying to use a combination of static integers and and a boolean to do this. Ultimate goal being when range("A195") changes at all, it adds +1 to the static numeral counter and skips over switch5p3. After that I DO want it to call switch5p3 but do not want it to add 1 to the numeral . Not UNTIL the value of range("A195") changes again, at which point it once again will add +1 to the numeral and not call switch 5p3. etc....

 

[rlv01]

What is your question?

 

[Moonbeam111]

My question is this: how do I skip the switch5p3 macro whenever a value in a195 changes? Right now my code doesn't work

 

[rlv01]

Please provide a more detailed description of what you mean by "doesn't work".

 

[Moonbeam111]

Its hard to explain. My code sometimes works, but sometimes it doesn't seem to follow the logic I prescribe it and I don't know why (probably has something to do with the static variable and/or boolean). All I'm really looking for is that when Range("A195") changes then it skips past the macro named "Switch5p3". But I only want it to skip past the macro called "Switch5p3" the first time range "A195" changes to any other value. When I click the 'macroSheet_Button390_Click()' again, I would like it to continue calling Switch5p3 as normal. Is that possible?

 

[rlv01]

What is the purpose of this?

If numeral = Range("A195") And Range("G47").Value > Range("F47").Value and hasrun = true Then
     GoTo D27
End If

 

[Moonbeam111]

that line of code is used to determine whether i should skip the macro switch5p3 or not. switch5p3 is the name of my macro that swaps some numbers of mine from one range to the other. If the static numeral value equals range "A195" and if range "G47" is greater than range "F47" and if my boolean is true then go straight to the line of the code named "d27". (meaning don't swap those particular numbers [since the switch5p3 wasn't called])

 

[rlv01]

Maybe something like this.

Option Explicit
Public OldVal As Long
Public ChangeCount As Long

Sub auto_open()
    'initialize when workbook opens
    OldVal = Range("A195").Value
    ChangeCount = 0
End Sub

'button code
Sub macroSheet_Button390_Click()
    Application.ScreenUpdating = True

    If Range("A195") <> OldVal Then
        OldVal = Range("A195").Value
        ChangeCount = ChangeCount + 1
    End If

    Range("D27") = Range("D27") - Range("A33").Value

    If Not (ChangeCount = Range("A195") And Range("G47").Value > Range("F47").Value And ChangeCount <> 1) Then
        If ChangeCount <> 1 Then
            Call Switch5p3
        End If
    End If

    If Range("D27").Value < 0 Then
        Range("D27").Value = 0
    End If

    If Range("D27").Value = 0 Then
        Range("D37:I37").ClearContents
        ActiveWindow.ScrollRow = 129
    End If

    Rows("31:35").EntireRow.Hidden = True
   Range("A194").Value = OldVal
End Sub

 

[Moonbeam111]

Thank you for the attempted fix. I pasted in the code, the only problem is when range A195 changes it doesn't call the switch5p3 anymore. I was hoping when range A195 changes that it would skip switch5p3 (which it does) but after that (after I click the macro button again) I was hoping it would continue to call switch5p3 again as normal through the process. Is there a fix to your code that can accommodate that? Maybe use a goto command or a minor change to your old val or change count? (Sorry I realize it might be a bit confusing)

 

[rlv01]

Try this:

Option Explicit
Public OldVal As Long           'Store old value
Public ChangeCount As Long      'Count A195 changes
Public SkipSub As Boolean       'skip Switch5p3 when TRUE

Sub auto_open()
'initialize variables when workbook opens. You many need to decide if this is the only time you want to re-initialize. 
    OldVal = Range("A195").Value
    ChangeCount = 0
    SkipSub = False
End Sub

'button code
Sub macroSheet_Button390_Click()
    Application.ScreenUpdating = True

    If Range("A195") <> OldVal Then
        OldVal = Range("A195").Value
        ChangeCount = ChangeCount + 1
        If ChangeCount = 1 Then
            SkipSub = True
        Else
            SkipSub = False
        End If
    End If

    Range("D27") = Range("D27") - Range("A33").Value

    If Not (ChangeCount = Range("A195") And Range("G47").Value > Range("F47").Value And ChangeCount <> 1) Then
        If Not SkipSub Then
            Call Switch5p3
        End If
    End If

    If Range("D27").Value < 0 Then
        Range("D27").Value = 0
    End If

    If Range("D27").Value = 0 Then
        Range("D37:I37").ClearContents
        ActiveWindow.ScrollRow = 129
    End If

    Rows("31:35").EntireRow.Hidden = True
End Sub