Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Each Click of the Mouse
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. |
#2
|
|||
|
|||
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.Parker" wrote in message ... 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. |
#3
|
|||
|
|||
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 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... 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.Parker" wrote in message ... 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. |
#4
|
|||
|
|||
Where the hell did you drag that up from :-)?
Bob "Dana DeLouis" wrote in message ... 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 -- Dana DeLouis Win XP & Office 2003 "Bob Phillips" wrote in message ... 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.Parker" wrote in message ... 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. |
#5
|
|||
|
|||
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) 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.Parker" wrote in message ... 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. |
#6
|
|||
|
|||
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" wrote in message ... 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) 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.Parker" wrote in message ... 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. |
#7
|
|||
|
|||
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" wrote in message ... 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) 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.Parker" wrote in message ... 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. |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#10
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#11
|
|||
|
|||
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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#12
|
|||
|
|||
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.Parker" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#13
|
|||
|
|||
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 "Bob Phillips" wrote: 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.Parker" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
#14
|
|||
|
|||
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) "D.Parker" wrote in message ... 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 "Bob Phillips" wrote: 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.Parker" wrote in message ... 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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 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 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 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.Parker" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mouse continues to select after clicking on a cell in excel. | Excel Discussion (Misc queries) | |||
Change cell back color on click | Excel Discussion (Misc queries) | |||
moving mouse lighlights cells | Excel Discussion (Misc queries) | |||
moving mouse highlights cells. why? | Excel Discussion (Misc queries) | |||
Undoing LINKS in Excel 2000 | New Users to Excel |