Drop down list

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I created a drop down list using the data, validation menu. It works well.
However, I only have two items in the list and I am wondering if there is a
better way to alternate between them. With the drop down list it takes a
couple of clicks. First you select the cell, then you click to drop down the
list, then you highlight the item, then you click on it.

Is there a way to select the alternate item with fewer clicks?
 
Don,

You need only click the cell, click the dropdown button, then click the desired item. Three
clicks.

This event-fired sub will alternate between "Choice a" and Choice b" with one double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const a = "Choice a"
Const b = "Choice b"
If Not Intersect(Target, Range("H1:H4")) Is Nothing Then
If LCase(Target.Value) = LCase(a) Then
Target.Value = b
ElseIf LCase(Target.Value) = LCase(b) Then
Target.Value = a
End If
Cancel = True
End If
End Sub

Just paste it from here into the sheet module. Change the range as desired for the cells
applicable. And the "choices". You don't need the Data - Validation.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Thanks,

I'm trying to make it work now.

Don

Earl Kiosterud said:
Don,

You need only click the cell, click the dropdown button, then click the
desired item. Three clicks.

This event-fired sub will alternate between "Choice a" and Choice b" with
one double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Const a = "Choice a"
Const b = "Choice b"
If Not Intersect(Target, Range("H1:H4")) Is Nothing Then
If LCase(Target.Value) = LCase(a) Then
Target.Value = b
ElseIf LCase(Target.Value) = LCase(b) Then
Target.Value = a
End If
Cancel = True
End If
End Sub

Just paste it from here into the sheet module. Change the range as
desired for the cells applicable. And the "choices". You don't need the
Data - Validation.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Back
Top