Toggle cell value

[Ken G.]

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?

 

[Gary''s Student]

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

 

[RonaldoOneNil]

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

 

[Stefi]

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:

 

[Ken G.]

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]

In my solution the macro itself clicks off the cell.

You are welcome! Thanks for the feedback!
Stefi

„Ken G.†ezt írta:

 

[Ken G.]

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]

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:

 

[Chip Pearson]

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)