Each Click of the Mouse

D

D.Parker

Hello:

Is there a way to change the ColorIndex of a given cell each time you
perform a left click with the mouse, within that same cell? I was going to
setup a loop(s) to cycle through the 4 colors red, blue, yellow, and green
and start the sequence over if the user continues to click. Thank you.
 
B

Bob Phillips

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iCellColour As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
Select Case .Interior.ColorIndex
Case 3: .Interior.ColorIndex = 5
Case 5: .Interior.ColorIndex = 6
Case 6: .Interior.ColorIndex = 10
Case Else: .Interior.ColorIndex = 3
End Select
End With
Me.Range("A1").Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dana DeLouis

Here's a slightly different twist to Bob excellent idea:

With Target.Interior
Select Case .ColorIndex
Case 3, 5, 6, 10
.ColorIndex = 59727 Mod (2 * .ColorIndex + 7)
Case Else
.ColorIndex = 3
End Select
End With
 
D

D.Parker

Thank you very much. If I reselect the cell at some other time will the past
color be retained, such that when a user clicks it will start from the last
know color in the case condition?

D.Parker
 
B

Bob Phillips

Try it and see :).

What will happen is that each time you select a cell it will cycle through
the colours, as it picks up the current colour and works out the next from
this.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

D.Parker

Bob:

Thank you very much. Being that I am a beginner/novice user I was unable to
find any information on the syntax "Me.Range". How is this used? Also the
"Case 3:", does that imply if red then go to blue. The code works well, but
I don't understand all the variable names and code movement (i.e. Not
Intersect...Is Nothing). Or is there a beginner version of this code you
have written (meaning all items will either be mentioned in the Help menu or
a textbook). Thank you.

Kind regards,

D.Parker
 
B

Bob Phillips

I'll dissect the code with comments to see if that helps, but this is not
real beginner stuff, but it is also not that complex.

Afraid it cannot be simplified in the way you describe, but you should find
all of the bits in Help.

D.Parker said:
Bob:

Thank you very much. Being that I am a beginner/novice user I was unable to
find any information on the syntax "Me.Range". How is this used? Also the
"Case 3:", does that imply if red then go to blue. The code works well, but
I don't understand all the variable names and code movement (i.e. Not
Intersect...Is Nothing). Or is there a beginner version of this code you
have written (meaning all items will either be mentioned in the Help menu or
a textbook). Thank you.

Kind regards,

D.Parker
Click to expand...

This is worksheet event code that will be triggered every time a cell, or
cells, is selected. The selected cell(s) is passed to the event macro as the
Target argument

Not necessary, throw-back to some other code.

Routine error handling, to force us out on an error

Disabel events so that our code doe not trigger other events.

This tests whether the range that we are monitoring, A1:H10 intersects with
the Target range passed as the argument to the event macro. This is a way of
determining whether the range we are monitorintg has been selected. If not,
we just bypass the next set of code.

Sets a reference to an object so that all subsequent . (dot) references
implicitly refer to this object type, cuts down on typingt, is more
efficient and more readable

Initiate a Case statement on the colorindex value of the Target cell, that
is the selected cell. This is equivalent to a nested If ... ElseIf ... End
If statement

If the current cell colorindex is 3 (red) set it to 5 (blue)

If the current cell colorindex is 5 (blue) set it to 6 (yellow)


If the current cell colorindex is 6 (yellow) set it to 10 (green)

Any other value, including no colour set to 3 (red)

Tidy up tand end he Select and With statements

Select A1, so that we can re-select the same cell again.

Reset events. This is i the error clause, so that if we get an error, we
always divert here, and always reset events.
 
D

D.Parker

I look forward to reading your comments. I come across information in the
Help but not specific to things like using "Is Nothing" or "Me.Range" for
example. I cannot find any descriptions.

Your codes works very well and I would like do use it in other parts of my
build but I want to make sure I know the operations first.

Also, if I select an entire row (i.e. row 10) and it intersects my
Me.Range("J5:J70"), the entire row goes through the color sequence changes,
is there a way to avoid that? I'm assuming it is because they intersect.

I appreciate your assistance.

Kind regards,

D.Parker
 
B

Bob Phillips

My last post included those comments interspersed in the code at the bottom.

You can avoid them all doing it by either doing nothing, just the first cell
in the selected range, or just the activecell. Which would you prefer?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

D.Parker

The activecell, hoping that is the best route. Meaning that would stop an
entire row from going through the color sequence? Thanks for you
explanations, I'm still a little lost on the "Me.Range", what does Me imply?
Thanks again for lending you assistance and expertise to a beginner.

Kind regards,

D.Parker
 
B

Bob Phillips

If you just want teh actrivecell then just use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static iCellColour As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(ActiveCell, Me.Range("A1:H10")) Is Nothing Then
With ActiveCell
Select Case .Interior.ColorIndex
Case 3: .Interior.ColorIndex = 5
Case 5: .Interior.ColorIndex = 6
Case 6: .Interior.ColorIndex = 10
Case Else: .Interior.ColorIndex = 3
End Select
End With
Me.Range("A1").Select
End If

ws_exit:
Application.EnableEvents = True
End Sub


Me refers to the containing object. In userform code, it refers to the
userform, in worksheet event code like this, it refres to the worksheet. I
use this to explicitly reference the correct sheet.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

D.Parker

Bob:

The activecell works perfectly! Thanks a million! So, Me.Range is the same
as Worksheets("Sheetname").Range. Thanks for that information also.

If I am at L65 and I click the mouse, after the click I return to L2, before
I can click to the next color. I commented out Me.Range("L2").Select and the
cursor stays on L65, but I have to move to another cell and come back to L65
to cycle through the color sequence. Can I make the cursor stay on the
current cell and cycle thhrough the sequence without returning to L2? The
code follows:

If Not Intersect(ActiveCell, Me.Range("L9:L65")) Is Nothing Then
With ActiveCell
Select Case .Interior.ColorIndex
Case 6:
.Interior.ColorIndex = 4
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Value = "P"
Case 4:
.Interior.ColorIndex = 3
.HorizontalAlignment = xlCenter
.Font.Bold = True
.Value = "F"
Case Else:
.Interior.ColorIndex = 6
.Value = ""
End Select
End With
'Me.Range("L2").Select
End If

Kind regards,

D.Parker
 
B

Bob Phillips

The problem is that if you do not select off of L65 (to say L2), then you
cannot select back onto it to force the event macro to fire as the click on
the activecell has no effect.. Selecting off is not good I agree, but I have
never found a better way.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top