CODEWITHSUNDEEP
vbaexcel
Bart Vos
Bart Vos

Reputation: 13

macro based on changing of a cell

I have found various solutions to the problem I have, but they require too much autonomy on my part: I'm writing my first ever visual basic program so I don't know much. I have tried these solutions, but they don't work, presumably because I'm messing up the placement of the suggested code.

I want to run a macro based on when a cell changes to a certain value. I want a different macro to run when the cell changes from the aforementioned value to something else. So I have a dropdown list with values A, B and C, and I want to run macro_A when the value changes to A, and macro_notA when the value changes to something else than A.

That's the basic question. From here I'll provide some additional information on what I've tried so far, and where I encounter my problem. I'm currently using a sub with the following basic structure:

Sub worksheet_change(ByVal target As Range)
    If Range("TargetCell").Value = "C" Then
        If Switch = "0" Then
            Switch = "1"
            Call Macro_A
        End If
    End If
    If Range("TargetCell").Value <> "C" Then
        If Switch = "1" Then
            Switch = "0"
            Call Macro_notA
        End If
    End If
End Sub

Where Switch is a variable I have defined in the ThisWorkbook code window using:

Private Sub Workbook_Open()
    Dim Switch

    If Range("TargetCell").Value <> "A" Then
        Switch = "1"
    End If

    If Range("TargetCell").Value = "A" Then
        Switch = "0"
    End If
End Sub

The specific problem is that the sub I'm using appears to not recognize the Switch Variable. If anyone finds a more efficient way to solve the question I posed in the second paragraph that would be great as well. I'm sure the way I'm trying is not the most efficient one.

Upvotes: 1

Views: 71

Answers (2)

Vityata
Vityata

Reputation: 43595

  1. Remove Dim Switch.

  2. Create a new module.

  3. Name it modPublic.

  4. Write Public Switch as String there.

OR

Create a new Worksheet. Name it tblSettings. Write the value of switch in cell "A1".

In a new module, create the following function to get the switch:

Public Function TellMeTheSwitch() As Boolean
    TellMeTheSwitch = tblSettings.Range("A1")
End Function

Create the following Sub to set the switch:

Public Sub ChangeSwitch()
    tblSettings.Range("A1") = Not tblSettings.Range("A1")
End Sub

In general, do not use Switch for a variable name, it is used by the VBEditor. Consider mySwitch or SwitchMe or something else.

Upvotes: 1

braX
braX

Reputation: 11755

Remove Dim Switch and then add Public Switch as String outside of the scope of the subroutine if you want other subroutines to be able to "see" it.

Upvotes: 0

Related Questions