|
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? |
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? |
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com