Toggle cell value
 

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Answer: Toggle cell value
 

Yes, there is a way to toggle between two values in a cell by using a simple macro in Microsoft Excel.

Here are the steps to create a toggle button:

1. Open the Excel workbook and press Alt + F11 to open the Visual Basic Editor.
2. In the Visual Basic Editor, click on Insert Module to create a new module.
3. In the module, paste the following code:
   
   
   Formula:

   ---

   Sub ToggleCell()
    If ActiveCell.Value = "apples" Then
        ActiveCell.Value = "oranges"
    Else
        ActiveCell.Value = "apples"
    End If
End Sub 


   ---

4. Save the module and close the Visual Basic Editor.
5. Go back to the Excel workbook and select the cell where you want to toggle between two values.
6. Right-click on the cell and select "Assign Macro" from the context menu.
7. In the "Assign Macro" dialog box, select the "ToggleCell" macro and click on "OK".
8. Now, whenever you click on the cell, it will toggle between "apples" and "oranges".

That's it! You have successfully created a toggle button in Excel. You can modify the code to toggle between any two values you want.

Toggle cell value
 

You can have almost the same thing with a Data Validation pull-down.

One click on the cell will allow you to pick from a list of value.

See:

http://www.contextures.com/xlDataVal01.html
--
Gary''s Student - gsnu200835


"Ken G." wrote:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

One way is with a bit of VBA in the relevant sheet as below. Substitute cell
C30 for your Cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell = Range("C30") Then
If ActiveCell.Value = "Apples" Then
ActiveCell.Value = "Oranges"
Else
ActiveCell.Value = "Apples"
End If
End If
End Sub

"Ken G." wrote:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

The closest solution I could achieve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Target = IIf(Target = "apples", "oranges", "apples")
Application.EnableEvents = False
Range("A2").Select
Application.EnableEvents = True
End If
End Sub

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

Thanks Steffi and Roanaldo. Both these solutions do what I wanted although
you can't toggle back and forth without first clicking off the cell. I can
put up with that.

"Stefi" wrote:

The closest solution I could achieve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Target = IIf(Target = "apples", "oranges", "apples")
Application.EnableEvents = False
Range("A2").Select
Application.EnableEvents = True
End If
End Sub

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

In my solution the macro itself clicks off the cell.

You are welcome! Thanks for the feedback!
Stefi

€žKen G.€� ezt Ã*rta:

Thanks Steffi and Roanaldo. Both these solutions do what I wanted although
you can't toggle back and forth without first clicking off the cell. I can
put up with that.

"Stefi" wrote:

The closest solution I could achieve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Target = IIf(Target = "apples", "oranges", "apples")
Application.EnableEvents = False
Range("A2").Select
Application.EnableEvents = True
End If
End Sub

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

Thanks Stefi. I didn't notice the instruction to move off the cell. When I
put it into my workbook I didn't see that and just typed my active cell in
both places so I missed that clever little move.

BTW, what is the double "II" in the line "Target = IIf(Target = "apples",
"oranges", "apples")? I thought it was a typo and took one out and of course
it threw up an error message.

"Stefi" wrote:

In my solution the macro itself clicks off the cell.

You are welcome! Thanks for the feedback!
Stefi

€žKen G.€� ezt Ã*rta:

Thanks Steffi and Roanaldo. Both these solutions do what I wanted although
you can't toggle back and forth without first clicking off the cell. I can
put up with that.

"Stefi" wrote:

The closest solution I could achieve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Target = IIf(Target = "apples", "oranges", "apples")
Application.EnableEvents = False
Range("A2").Select
Application.EnableEvents = True
End If
End Sub

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

It's advisable to test codes in answers as they are sent and ask again if
something is not clear, just alike you did it now in relation to "Iif": it is
the syntax of VBA IF function, see VBA Help!

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Thanks Stefi. I didn't notice the instruction to move off the cell. When I
put it into my workbook I didn't see that and just typed my active cell in
both places so I missed that clever little move.

BTW, what is the double "II" in the line "Target = IIf(Target = "apples",
"oranges", "apples")? I thought it was a typo and took one out and of course
it threw up an error message.

"Stefi" wrote:

In my solution the macro itself clicks off the cell.

You are welcome! Thanks for the feedback!
Stefi

€žKen G.€� ezt Ã*rta:

Thanks Steffi and Roanaldo. Both these solutions do what I wanted although
you can't toggle back and forth without first clicking off the cell. I can
put up with that.

"Stefi" wrote:

The closest solution I could achieve:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Target = IIf(Target = "apples", "oranges", "apples")
Application.EnableEvents = False
Range("A2").Select
Application.EnableEvents = True
End If
End Sub

Regards,
Stefi

€žKen G.€� ezt Ã*rta:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?

Toggle cell value
 

Right-click the sheet tab and choose View Code. In that code module,
paste the following code. Change the "$A$1" value to the address of
the cell you want to toggle.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Address = "$A$1" Then '<<< CHANGE ADDRESS
Application.EnableEvents = False
Cancel = True
If StrComp(Target.Text, "apples", vbTextCompare) = 0 Then
Target.Value = "oranges"
Else
Target.Value = "apples"
End If
Application.EnableEvents = True
End If
End Sub

Now, when you double-click on A1, it will toggle between "apples" and
"oranges".


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 20 Feb 2009 00:00:02 -0800, Ken G.
wrote:

Is there a way of making a cell active so that it can have two values - say
"apples" and "oranges", and simply clicking on the cell will toggle between
the two values?