How do I: Cycle through selections by clicking on cell
 

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...

--
Message posted from http://www.ExcelForum.com

How do I: Cycle through selections by clicking on cell
 

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

--
Message posted from http://www.ExcelForum.com

How do I: Cycle through selections by clicking on cell
 

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....


---
Message posted from http://www.ExcelForum.com/

How do I: Cycle through selections by clicking on cell
 

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


---
Message posted from http://www.ExcelForum.com/