What's the best way to toggle between true and false in Excel? Hiall, My excel work involves a lot

[LunaMoon]

What's the best way to toggle between true and false in Excel?

Hi all,

My excel work involves a lot of toggling between true and false
(boolean types) ... and it's very repetitive...

Is there a way to select a bunch of cells, and press a key short-cut
so that they toggle all at once?

Thanks!

 

[Rick Rothstein \(MVP - VB\)]

I can't tell you whether this is a "best way" to do what you want or not,
but it is "a way" to do it. It doesn't use a key short-cut, rather it uses a
right mouse click. Go into the VB editor and double click on ThisWorkbook in
the Project window, then copy/paste this code into the code window that
appeared...

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim C As Range
Dim NotTorF As Range
On Error GoTo Whoops
Application.EnableEvents = False
For Each C In Target
If C.Text = "TRUE" Or C.Text = "FALSE" Then
C.Value = Not C.Value
ElseIf NotTorF Is Nothing Then
Set NotTorF = C
Else
Set NotTorF = Union(NotTorF, C)
End If
Next
If Not NotTorF Is Nothing Then
Cancel = False
NotTorF.Select
Else
Cancel = True
End If
Whoops:
Application.EnableEvents = True
End Sub

Now, go back to the sheet and select any combination of TRUE and FALSE cells
and right click in the selection (you do not have to restrict yourself to
processing all TRUEs first and then all FALSEs afterward... if the cell in
the selection contains either TRUE or FALSE, that value will be flipped to
its opposite). Note the the normal context menu is suppressed for the TRUE
and FALSE cells; however, if you select non TRUE or FALSE cells, they will
still pop up the context menu. Also, if you mix TRUE or FALSE cells with non
TRUE or FALSE cells, the TRUE and FALSE cells will be removed from the
selection and a context menu will popup for the remaining cells.

Rick

 

[Greg Wilson]

One way is this:

1. In a *standard* module paste:
Declare Function GetKeyState Lib "User32.dll" (ByVal NVirtKey As Long) As
Integer

2. In the worksheet's class module paste this:
Const VK_SHIFT = &H10

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim c As Range
If GetKeyState(VK_SHIFT) < 0 Then
Cancel = True
For Each c In Selection.Cells
If VarType(c.Value) = vbBoolean Then
c.Value = Not c.Value
End If
Next
Set c = Nothing
End If
End Sub

3. Select the cells and hold down the Shift key when right clicking to
toggle the boolean values.

Greg

 

[Ken Wright]

For a non code solution, simply select all the cells that will be toggled
and then firstly give them a name using Insert / Name / Define and call it
anything you like. That lets you select them all in one easy hit using the
drop down just above cell A1.

Next, simply type TRUE/FALSE or 1/0 depending on what you are using and then
hit CTRL+ENTER which will put the same value into every cell.

Regards
Ken.......................

 

[LunaMoon]

For a  non code solution, simply select all the cells that will be toggled
and then firstly give them a name using Insert / Name / Define and call it
anything you like.  That lets you select them all in one easy hit usingthe
drop down just above cell A1.

Next, simply type TRUE/FALSE or 1/0 depending on what you are using and then
hit CTRL+ENTER which will put the same value into every cell.

Regards
                 Ken.......................

thanks but this is not a toggle solution...

 

[LunaMoon]

I can't tell you whether this is a "best way" to do what you want or not,
but it is "a way" to do it. It doesn't use a key short-cut, rather it uses a
right mouse click. Go into the VB editor and double click on ThisWorkbookin
the Project window, then copy/paste this code into the code window that
appeared...

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
                     ByVal Target As Range, Cancel As Boolean)
  Dim C As Range
  Dim NotTorF As Range
  On Error GoTo Whoops
  Application.EnableEvents = False
  For Each C In Target
    If C.Text = "TRUE" Or C.Text = "FALSE" Then
      C.Value = Not C.Value
    ElseIf NotTorF Is Nothing Then
      Set NotTorF = C
    Else
      Set NotTorF = Union(NotTorF, C)
    End If
  Next
  If Not NotTorF Is Nothing Then
    Cancel = False
    NotTorF.Select
  Else
    Cancel = True
  End If
Whoops:
  Application.EnableEvents = True
End Sub

Now, go back to the sheet and select any combination of TRUE and FALSE cells
and right click in the selection (you do not have to restrict yourself to
processing all TRUEs first and then all FALSEs afterward... if the cell in
the selection contains either TRUE or FALSE, that value will be flipped to
its opposite). Note the the normal context menu is suppressed for the TRUE
and FALSE cells; however, if you select non TRUE or FALSE cells, they will
still pop up the context menu. Also, if you mix TRUE or FALSE cells with non
TRUE or FALSE cells, the TRUE and FALSE cells will be removed from the
selection and a context menu will popup for the remaining cells.

Rick

Thanks! Any pure key short-cut solution?
I think that's the fastest...

 

[LunaMoon]

One way is this:

1.  In a *standard* module paste:
Declare Function GetKeyState Lib "User32.dll" (ByVal NVirtKey As Long) As
Integer

2.  In the worksheet's class module paste this:
Const VK_SHIFT = &H10

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim c As Range
If GetKeyState(VK_SHIFT) < 0 Then
    Cancel = True
    For Each c In Selection.Cells
        If VarType(c.Value) = vbBoolean Then
            c.Value = Not c.Value
        End If
    Next
    Set c = Nothing
End If
End Sub

3.  Select the cells and hold down the Shift key when right clicking to
toggle the boolean values.

Greg

Thanks! Any pure key short-cut solution?
I think that's the fastest...

 

[Ken Wright]

Then you will need to use code. You could still use the range name idea and
then simply assign a bit of code to a keyboard shortcut, eg CTRL+SHIFT+T

Sub Toggle()
Dim Cel As Range
For Each Cel In Range("ToggleVals")
Cel.Value = Not Cel.Value
Next
End Sub

Hit the key combo and you'll change TRUE to FALSE and vice versa. Even
gives you the option of running with mixed values if you had to. If you add
or delete cells to your range of booleans, then all you need do is reset
your named range and no need to touch the code again.

Could also throw a button on and use that, but again just another option.

Regards
Ken................

For a non code solution, simply select all the cells that will be toggled
and then firstly give them a name using Insert / Name / Define and call it
anything you like. That lets you select them all in one easy hit using the
drop down just above cell A1.

Next, simply type TRUE/FALSE or 1/0 depending on what you are using and
then
hit CTRL+ENTER which will put the same value into every cell.

Regards
Ken.......................

thanks but this is not a toggle solution...

 

[Dave Mills]

What keyboard shortcut can be any faster that holding Shift and right clicking
the mouse.

 

[Earl Kiosterud]

Luna,

Here's a sub you can invoke with a keyboard shortcut, and it'll toggle all the selected
cells with booleans (TRUE and FALSE). It leaves other values, including text and numbers
and formulas, alone. It can handle multiple-cell selections, including separate areas (Ctrl
key used to select non-contiguous cells). It leaves your right-click alone.

It has a shortcoming that it thinks 0 and -1 are FALSE and TRUE, and will toggle these
values (including formulas that return these values, replacing them with 0 or 1). I don't
remember where you test the type (boolean) of the contents of a a cell, if it's even
possible.

Sub ToggleBoolean()
Dim thing As Range
For Each thing In Selection
If thing = True Or thing = False Then ' is it boolean?
thing = Not thing
End If
Next thing
End Sub

You could put it in a module in your workbook (or in Personal.xls, if you want it available
for all workbooks), then assign a keyboard shortcut to run it (Tools - Macro - Macros -
Options).

When you bottom-post to a reply that's top-posted, it gets messy.