Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
|
|||
|
|||
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:
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.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Toggle Cell Colour Conditionally | Excel Worksheet Functions | |||
Need a button to click to toggle cell color on/off | Excel Discussion (Misc queries) | |||
Need a button to click to toggle cell color on/off | Excel Discussion (Misc queries) | |||
Can I toggle the value of a cell by clicking directly on the cell? | Excel Discussion (Misc queries) | |||
Toggle multiple values in single cell | Excel Worksheet Functions |