VBA Excel - Change Cell between True and False

Asked

Viewed 1,118 times

2

I’m making a code but since I’m still very green in the VBA I don’t know how to make it work. It’s very simple, I want to press a cell and it will change a Boolean between True and False.

When I load it puts TRUE, but then if you press there again, or exit the cell and select again it does not change to false!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

        Dim mudancasAtivas As Boolean
        If Not Intersect(Target, Range("E1:E1")) Is Nothing Then
            With Target(1, 3)
                If mudancasAtivas = True Then
                     mudancasAtivas = False
                    .value = mudancasAtivas
                ElseIf mudancasAtivas = False Then
                     mudancasAtivas = True
                    .value = mudancasAtivas
                End If
            End With
        End If

        If Not Intersect(Target, Range("E2:E10000")) Is Nothing Then

            If mudancasAtivas = True Then
                Codexxx
            End If
        End If
  End If
 End Sub

Is there any easier or more viable way to do this and work?

excel vba

  • You just declared the variable mudancasAtivas, she’s not defined, so she’s not going to enter the If..

  • I even ran your code here, but could you describe better what you intend to do? I still can’t understand your goal.

  • I want when pressing E1 to change the variable to true if it is false or otherwise. Which is so if it’s true it runs the code below.. I can’t change the variable every time I press E1

1 answer

2


The problem is occurring because the code runs at two different times. As the variable changes is local, in the 2nd execution the value that was attributed to it, in the 1st execution, is no longer worth more.

So, what you could do to solve this is to declare this variable as global in the spreadsheet module.

See below the code:

Dim mudancasAtivas As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If

    If Not Intersect(Target, Range("E1:E1")) Is Nothing Then
        With Target(1, 3)
            If mudancasAtivas = True Then
                 mudancasAtivas = False
                .Value = mudancasAtivas
            ElseIf mudancasAtivas = False Then
                 mudancasAtivas = True
                .Value = mudancasAtivas
            End If
        End With
    End If

    If Not Intersect(Target, Range("E2:E10000")) Is Nothing Then

        If mudancasAtivas = True Then
            Debug.Print "OI"
        End If
    End If
End Sub

Below is a reduced version that does not need the variable changesAtivas, see:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Cells.Count > 1 Then
          Exit Sub
      End If

      If Not Intersect(Target, Range("E1:E1")) Is Nothing Then
          Target(1, 3).Value = Not Target(1, 3).Value
      End If

      If Not Intersect(Target, Range("E2:E10000")) Is Nothing Then
          If Cells(1, 7).Value = -1 Then
              Debug.Print "OI"
          End If
      End If
End Sub

The way it is, the values will be changing between 0 and -1. 0 is the constant that represents FALSE in Excel and -1 is the constant that represents TRUE.

If you want it to be exchanged between TRUE and FALSE, just write in the cell one of these two values.

  • So the only problem was because the variable was not Global? It worked! Thank you :)

  • 1

    Yes. The problem in this case is that you needed to keep the status between calls. For this, you can use a global variable of this module or do as shown in the 2nd code, that is, use the state itself maintained by the Excel cell.

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.