1

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.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Bart Vos
  • 13
  • 1
  • 4
  • Hmm, I think you're overcomplicating things here a bit. You probably one need 1 macro, and that's the `Worksheet_Change` event. – dwirony Jun 19 '17 at 13:25
  • 2
    You have 2 options. create a hidden sheet (xlveryhidden) and store the switch value in there OR declare a global public variable called switch. You should look up about "scope of variables" to understand why your switch is not reackonised in other subs – 99moorem Jun 19 '17 at 13:25
  • @99moorem I did look up information about the scope of variables, but I didn't understand the article. As I said, I have never before written a vba program or taken a course in vba. How do I declare a global public variable called switch? Because it sounds like that's what I've been trying to do. – Bart Vos Jun 19 '17 at 13:30
  • @BartVos if you do Dim switch inside the code from sub to end sub then at end sub that variable disappears (or in code talk it gets deconstructed). if you write public switch as string above the sub (so outside of the procedure) you then create a public global variable and should be able to see it from both parts of code see https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba – 99moorem Jun 19 '17 at 13:37

2 Answers2

1

  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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
0

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.

braX
  • 11,506
  • 5
  • 20
  • 33