Excel VBA - Change cell between True and False

Question

Excel VBA - Change cell between True and False

Navigation

#1 by (2 votes)

2

I am making a code but as I am still very green in VBA I do not know how to get this working. It's very simple, I want to load a cell and it will change a boolean between True and False.

When I load it, it gets TRUE, but after pressing it again, or leaving the cell and selecting it 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 feasible way to do this and work?

excel vba

asked by anonymous 04.08.2015 / 16:12

1 answer

How to check for javascript if a new li tag entered through setInterval? Angular.js $ setValidity onLoad Page

score 2 · Accepted Answer

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

So what you could do to resolve this is to declare this variable as global in the worksheet module.

See the code below:

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 changeActive, 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

As it stands, values will change between 0 and -1. 0 is the constant that represents the FALSE in Excel and -1 the constant that represents the TRUE.

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