Re: Each Click of the Mouse

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.Parker" <DParker@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:502779B0-19B6-4447-BF42-E127C656095B@xxxxxxxxxxxxxxxx
> 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
>
> "Bob Phillips" wrote:
>
> > 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" <DParker@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:2E700FEE-4CB8-49FB-8086-881D3BB63772@xxxxxxxxxxxxxxxx
> > > 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
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > 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.Parker" <DParker@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:96C20E60-78BD-44E1-B406-0F5B2BF83208@xxxxxxxxxxxxxxxx
> > > > > 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
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > This is work*** 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
> >
> > > > > > Static iCellColour As Long
> >
> > Not necessary, throw-back to some other code.
> >
> > > > > > On Error GoTo ws_exit:
> >
> > Routine error handling, to force us out on an error
> >
> > > > > > Application.EnableEvents = False
> >
> > Disabel events so that our code doe not trigger other events.
> >
> > > > > > If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
> >
> > 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.
> >
> > > > > > With Target
> >
> > 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
> >
> > > > > > Select Case .Interior.ColorIndex
> >
> > 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
> >
> > > > > > Case 3: .Interior.ColorIndex = 5
> >
> > If the current cell colorindex is 3 (red) set it to 5 (blue)
> >
> > > > > > Case 5: .Interior.ColorIndex = 6
> >
> > If the current cell colorindex is 5 (blue) set it to 6 (yellow)
> >
> >
> > > > > > Case 6: .Interior.ColorIndex = 10
> >
> > If the current cell colorindex is 6 (yellow) set it to 10 (green)
> >
> > > > > > Case Else: .Interior.ColorIndex = 3
> >
> > Any other value, including no colour set to 3 (red)
> >
> > > > > > End Select
> > > > > > End With
> >
> > Tidy up tand end he Select and With statements
> >
> > > > > > Me.Range("A1").Select
> >
> > Select A1, so that we can re-select the same cell again.
> >
> > > > > > End If
> > > > > >
> > > > > > ws_exit:
> > > > > > Application.EnableEvents = True
> >
> > Reset events. This is i the error clause, so that if we get an error, we
> > always divert here, and always reset events.
> >
> > > > > > End Sub
> > > > > >
> > > > > > 'This is work*** event code, which means that it needs to be
> > > > > > 'placed in the appropriate work*** code module, not a standard
> > > > > > 'code module. To do this, right-click on the *** 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.Parker" <DParker@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:9CA590A5-89E5-4675-94C1-150164A4F374@xxxxxxxxxxxxxxxx
> > > > > > > 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.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Quantcast