Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
Try this event sub:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
Thanks Stefi. I need help.
Thanks again Julie "Stefi" wrote: Try this event sub: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
This shorter routine will do the same thing your code does...
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 3 - Target.Interior.ColorIndex End Sub -- Rick (MVP - Excel) "Stefi" wrote in message ... Try this event sub: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
Rick, it's nice and tricky!
Julene, Open VBA (Alt+F11) Right click on your worksheet name in the Project window Select View code from the local menu Copy and paste Rick's macro in the code window Regards, Stefi €˛Rick Rothstein€¯ ezt Ć*rta: This shorter routine will do the same thing your code does... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 3 - Target.Interior.ColorIndex End Sub -- Rick (MVP - Excel) "Stefi" wrote in message ... Try this event sub: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
For future reference, you can make a single line toggle between any two
values. Here is the general format of such a statement... Variable = (Value1 + Value2) - Variable So, if the values to be toggled between are 3 and 7, then the statement becomes... Variable = 10 - Variable Of course, Variable must be first set to one or the other of Value1. Assuming it is 3, then 10 - 3 gives 7; and if it is 7, then 10 - 7 is 3. -- Rick (MVP - Excel) "Stefi" wrote in message ... Rick, it's nice and tricky! Julene, Open VBA (Alt+F11) Right click on your worksheet name in the Project window Select View code from the local menu Copy and paste Rick's macro in the code window Regards, Stefi €˛Rick Rothstein€¯ ezt Ć*rta: This shorter routine will do the same thing your code does... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 3 - Target.Interior.ColorIndex End Sub -- Rick (MVP - Excel) "Stefi" wrote in message ... Try this event sub: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make a Yes/no cell using a single click
Thanks, Rick, how simple it is after someone invented it!
Stefi €˛Rick Rothstein€¯ ezt Ć*rta: For future reference, you can make a single line toggle between any two values. Here is the general format of such a statement... Variable = (Value1 + Value2) - Variable So, if the values to be toggled between are 3 and 7, then the statement becomes... Variable = 10 - Variable Of course, Variable must be first set to one or the other of Value1. Assuming it is 3, then 10 - 3 gives 7; and if it is 7, then 10 - 7 is 3. -- Rick (MVP - Excel) "Stefi" wrote in message ... Rick, it's nice and tricky! Julene, Open VBA (Alt+F11) Right click on your worksheet name in the Project window Select View code from the local menu Copy and paste Rick's macro in the code window Regards, Stefi €˛Rick Rothstein€¯ ezt Ć*rta: This shorter routine will do the same thing your code does... Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Interior.ColorIndex = 3 - Target.Interior.ColorIndex End Sub -- Rick (MVP - Excel) "Stefi" wrote in message ... Try this event sub: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Interior.ColorIndex = 3 Then Target.Interior.ColorIndex = 0 Else Target.Interior.ColorIndex = 3 End If End Sub Post if you need help to install it! Regards, Stefi €˛julene€¯ ezt Ć*rta: I am using Excell 2007. I am making a spreadsheet that is going to be used by a number of people. I would like some of the cells to change colour when clicked on, without having to enter any data, e.g. if the heading is 'native', the user would be able to click on cells in the column and the cell would change colour to indicate a 'yes' answer. If the same cell was then changed later to 'no' it could be clicked on to return to the original colour. I can change the colour if I enter a Y using conditional formatting, but don't know how to change it if the cell is just clicked on. Can you help please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Click on single cell, multiple cells are selected. | Excel Discussion (Misc queries) | |||
Excell is not allowing me to click onto a single cell. WHY? | Excel Worksheet Functions | |||
Changing row content with single cell click | Excel Discussion (Misc queries) | |||
How do i insert a image to a single cell then click on the cell & | Setting up and Configuration of Excel | |||
IN EXCEL, WHEN I CLICK ONA SINGLE CELL It HIGHLIGHTS WHOLE Page | Excel Discussion (Misc queries) |