How do I: Cycle through selections by clicking on cell

[jasonsweeney]

I need a user interface that does the following:

Click on cell A1 once and it produces a "1" in cell B1
Click on cell A1 a second time, and it products a "2" in cell B1
Click on cell A1 a third time, and it produces a "3" in cell B1
Click on cell A1 a fourth time, and it produces a "4" in cell B1
*** Click on cell A1 a fifth time, and it resets the cell to "". an
the process can be repeated.

Thus, a person can cycle through the numbers 1-4 in cell B1 by simpl
clicking on cell A1 four times....I think I have to select a differen
cell after each cycle so the person has to click BACK on cell A1 t
trigger....thus the last code line will select a different cell tha
the A1 cell.

Below is the code I have so far, but it does not work. It cycle
through the 4 numbers instantly without stopping. Thus I need t
arrest the cycle after each mouse “click”. Any help would be greatl
appreciated.

[In cells B1:B12 I entered the months of the year]
_____________________________________

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A12")) Is Nothing Then

If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1).Value = 1
Target.Offset(0, 2).Select

If Target.Offset(0, 1) = 1 Then
Target.Offset(0, 1).Value = 2
Target.Offset(0, 2).Select

If Target.Offset(0, 1) = 2 Then
Target.Offset(0, 1).Value = 3
Target.Offset(0, 2).Select

If Target.Offset(0, 1) = 3 Then
Target.Offset(0, 1).Value = 4
Target.Offset(0, 2).Select

If Target.Offset(0, 1) = 4 Then
Target.Offset(0, 1).Value = ""
Target.Offset(0, 2).Select


End If
End If
End If
End If
End If
End If

End Sub

*** Extra help...this code also produces an error if the user tries t
select more than one cell at a time...

 

[mudraker]

Try this for starters

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Count > 1 Then
Exit Sub
ElseIf Target.Address <> "$A$1" Then
Exit Sub
End If
If Range("b1").Value > 3 Then
Range("b1").Value = 0
Else
Range("b1").Value = Range("b1") + 1
End If
Range("c1").Activate
End Su

 

[jasonsweeney]

Thanks. That solved several problems.

Here is the question. Is there anyway around having to select another
cell between cycles?

The reason is when I try and quickly click on the cell to cycle
through, excel thinks I want to type text in the "selection" cell.

In short, I want to quickly cycle through the numbers....

 

[jasonsweeney]

Ok.....

My boss wants two sections of a worksheet that, when a person clicks on
one cell, the cell directly next to it produces a numerical value that
cycles between 4 numbers as you continue to click on the cell.

Example:
Envision cells A1:12 displaying the months of the year. If you click
on March (A3) one time you get a "1" in cell B3. Click March a second
time you get a "2" in cell B3, etc. (a "Cycle".)

In Cells C1:C12 you have a list of names. Clicking on a name in C6
starts a Cycle in cell D6.

I have the sub-routine that causes the cell to cycle through 4 numbers
by clicking on the cell (see below).

HELP: How do I implement BOTH of subroutines at the same time. This
is what I tried and it produces all sorts of errors:
_______________________________________

Private Sub Worksheet_SelectionChange(ByVal target As Range)
'
If Not Intersect(target, Range("A1:A12")) Is Nothing Then
Call firstselectionsub
'
'
Else
If Not Intersect(target, Range("C1:C12")) Is Nothing Then
Call secondselectionsub
'
End If
End If

End Sub

Sub firstselectionsub()

If target.Count > 1 Then
Exit Sub
Else
If target.Offset(0, 1) = "" Then
target.Offset(0, 1).Value = 1
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = "1" Then
target.Offset(0, 1).Value = 2
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = 2 Then
target.Offset(0, 1).Value = 3
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = 3 Then
target.Offset(0, 1).Value = ""
target.Offset(0, -1).Select

End If
End If
End If
End If
End If
End If


End Sub

secondsectionsub
If target.Count > 1 Then
Exit Sub
Else
If target.Offset(0, 1) = "" Then
target.Offset(0, 1).Value = 4
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = "4" Then
target.Offset(0, 1).Value = 5
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = 5 Then
target.Offset(0, 1).Value = 6
target.Offset(0, -1).Select
'
Else
If target.Offset(0, 1) = 6 Then
target.Offset(0, 1).Value = ""
target.Offset(0, -1).Select

End If
End If
End If
End If
End If
End If

End sub