Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
This code will set a colour on selecting a cell, and change it on
re-selection or double-click. Option Explicit Const WS_RANGE As String = "H1:H10" '<=== change to suit Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean) SetColour target End Sub Private Sub Worksheet_SelectionChange(ByVal target As Range) SetColour target End Sub Private Sub SetColour(ByVal target As Range) Dim idx As Long If Not Intersect(target, Me.Range(WS_RANGE)) Is Nothing Then With target idx = Int((56 * Rnd) + 1) .Interior.ColorIndex = idx End With End If 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 Bob Phillips (replace somewhere in email address with gmail if mailing direct) "sadsfan" wrote in message ... I have an interactive whiteboard, where if you touch it once this equals 1 click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
Hi
This will make one click be yellow and double click green on sheet 1. In the VBA editor double click sheet 1 in the Project Window. Paste in the following: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Target.Interior.ColorIndex = 10 Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 6 End Sub Note that the selectionChange only kicks in when you change cells, so clicking the cell you are already on might not do anything. You can also restrict the colour changes to a specific area on your sheet, and not on others. you can also make colour changes apply to all sheets in a workbook. Post back if you need things refined a little. regards Paul sadsfan wrote: I have an interactive whiteboard, where if you touch it once this equals 1 click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
You would use the selectionchange and beforedoubleclick events to run code
that changes the color. These would be found in the sheet module of the sheet. Right click on the sheet tab and select view code. Then in the resulting module, in the left dropdown at the top, select Worksheet. In the right dropdown select SelectionChange. You will get an event declaration like: Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub put you code there Private Sub Worksheet_SelectionChange(ByVal Target As Range) If not Intersect(Target,Range("B2:Z26")) is nothing then Target.Interior.ColorIndex = 5 End if End Sub Likewise for the before double click Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If not Intersect(Target,Range("B2:Z26")) is nothing then Target.Interior.ColorIndex = 3 End if Cancel = True End Sub To see the colors corresponding to ColorIndex you can run this macro on a blank sheet Sub ShowColors() Dim i As Long For i = 0 To 56 Cells(i + 1, 1).Value = i Cells(i + 1, 2).Interior.ColorIndex = i Next End Sub in the VBE (alt+F11) select Insert Module. Put this code in that module (a general/standard module) then go back to Excel (Alt+F11) and go to Tools=Macro=Macros and select Showcolors, hit run Chip Pearson has an overview of Events at his site: http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "sadsfan" wrote in message ... I have an interactive whiteboard, where if you touch it once this equals 1 click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
Hi paul, your code workde fine except for the fact that I have a command
button in sheet 1 which is clicked to randomize the numbers, when I do this the cells turn yellow (equal to 1 click of the mouse). I've tried pasting the command button code elswher (in a module) but then it doesn't work. any ideas? " wrote: Hi This will make one click be yellow and double click green on sheet 1. In the VBA editor double click sheet 1 in the Project Window. Paste in the following: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Target.Interior.ColorIndex = 10 Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 6 End Sub Note that the selectionChange only kicks in when you change cells, so clicking the cell you are already on might not do anything. You can also restrict the colour changes to a specific area on your sheet, and not on others. you can also make colour changes apply to all sheets in a workbook. Post back if you need things refined a little. regards Paul sadsfan wrote: I have an interactive whiteboard, where if you touch it once this equals 1 click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
Hi
In your command button code, try to remove any activates or selections (which trigger the change event) e.g you can replace Range("A1").Select Selection.Value = 3 with Range("A1").Value = 3 Post your button code if you are not sure what to replace. regards Paul sadsfan wrote: Hi paul, your code workde fine except for the fact that I have a command button in sheet 1 which is clicked to randomize the numbers, when I do this the cells turn yellow (equal to 1 click of the mouse). I've tried pasting the command button code elswher (in a module) but then it doesn't work. any ideas? " wrote: Hi This will make one click be yellow and double click green on sheet 1. In the VBA editor double click sheet 1 in the Project Window. Paste in the following: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Target.Interior.ColorIndex = 10 Cancel = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 6 End Sub Note that the selectionChange only kicks in when you change cells, so clicking the cell you are already on might not do anything. You can also restrict the colour changes to a specific area on your sheet, and not on others. you can also make colour changes apply to all sheets in a workbook. Post back if you need things refined a little. regards Paul sadsfan wrote: I have an interactive whiteboard, where if you touch it once this equals 1 click of the mouse and touch twice for a double click. I play a game with my class of children which uses the mrand function from the morefunc add-in. In the game i want to change a cell colour with one click, and change to a different colour with a double click. Does anyone know if it is possible to do this (the cells i want to change already have the mrand function in them) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
" wrote: Hi In your command button code, try to remove any activates or selections (which trigger the change event) e.g you can replace Range("A1").Select Selection.Value = 3 with Range("A1").Value = 3 Post your button code if you are not sure what to replace. regards Paul Thanks Paul here is my button code: Sub random_numbers1() Range("A1").Select Range("A1:F7").Select FormulaR1C1 = "=MRAND(42,1,42)" Selection.FormulaArray = "=MRAND(42,1,42)" Range("H5").Select Range("H5:I6").Select FormulaR1C1 = "=MRAND(4,1,12)" Selection.FormulaArray = "=MRAND(4,1,12)" End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excell and changing cell colour
Hi
Try this (untested) Sub random_numbers1() Range("A1:F7").FormulaArray = "=MRAND(42,1,42)" Range("H5:I6").FormulaArray = "=MRAND(4,1,12)" End Sub Regards Paul sadsfan wrote: " wrote: Hi In your command button code, try to remove any activates or selections (which trigger the change event) e.g you can replace Range("A1").Select Selection.Value = 3 with Range("A1").Value = 3 Post your button code if you are not sure what to replace. regards Paul Thanks Paul here is my button code: Sub random_numbers1() Range("A1").Select Range("A1:F7").Select FormulaR1C1 = "=MRAND(42,1,42)" Selection.FormulaArray = "=MRAND(42,1,42)" Range("H5").Select Range("H5:I6").Select FormulaR1C1 = "=MRAND(4,1,12)" Selection.FormulaArray = "=MRAND(4,1,12)" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing background colour when changing data in a cell | Excel Discussion (Misc queries) | |||
Changing colour intensity in Excell 2007 | Excel Discussion (Misc queries) | |||
Changing a cell colour | Excel Discussion (Misc queries) | |||
changing cell colour on click | Excel Discussion (Misc queries) | |||
Changing Cell Colour | Excel Programming |