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!

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



"LunaMoon" wrote in message
...
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!

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

"LunaMoon" wrote:

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!

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" wrote in message
...
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!

On Jul 28, 1:58*pm, "Ken Wright"
wrote:
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" wrote in message

...

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!

thanks but this is not a toggle solution...

On Jul 28, 12:07*am, "Rick Rothstein \(MVP - VB\)"
wrote:
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

"LunaMoon" wrote in message

...

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!

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

On Jul 28, 1:28*am, Greg Wilson
wrote:
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

"LunaMoon" wrote:
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!

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

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................

"LunaMoon" wrote in message
...
On Jul 28, 1:58 pm, "Ken Wright"
wrote:
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" wrote in message

...

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!

thanks but this is not a toggle solution...

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

On Mon, 28 Jul 2008 11:19:17 -0700 (PDT), LunaMoon
wrote:

On Jul 28, 1:28*am, Greg Wilson
wrote:
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

"LunaMoon" wrote:
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!

Thanks! Any pure key short-cut solution?
I think that's the fastest...
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

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.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"LunaMoon" wrote in message
...
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!