Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
This is an attempt to provide an attendance form for completion at a
meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
Try this event macro:
Assume the range of cells where you want this to happen is B1:B100. Since you want the default value to be "Absent" you'd need to already have "Absent" entered in those cells. When you select a cell in the range B1:B100 it will change from "Absent" to "Present". If you select that cell again it will change back to "Absent". Here's the macro: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Range("B1:B100")) Is Nothing Then With Target If .Value = "Absent" Then .Value = "Present" Else .Value = "Absent" End If End With End If sub_exit: Application.EnableEvents = True End Sub To use this macro, in the sheet where you want this to happen, right click the sheet tab and select View Code. This will open the VBA editor. Paste the macro in the window that opens. Then just close that window to return to the Excel worksheet. Adjust the range to suit. Biff "Robin Chapple" wrote in message ... This is an attempt to provide an attendance form for completion at a meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
Many thanks. That works a treat.
Is there a way to count the cells with "Present in them? Cheers, Robin On Fri, 26 Jan 2007 22:30:00 -0500, "T. Valko" wrote: Try this event macro: Assume the range of cells where you want this to happen is B1:B100. Since you want the default value to be "Absent" you'd need to already have "Absent" entered in those cells. When you select a cell in the range B1:B100 it will change from "Absent" to "Present". If you select that cell again it will change back to "Absent". Here's the macro: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Range("B1:B100")) Is Nothing Then With Target If .Value = "Absent" Then .Value = "Present" Else .Value = "Absent" End If End With End If sub_exit: Application.EnableEvents = True End Sub To use this macro, in the sheet where you want this to happen, right click the sheet tab and select View Code. This will open the VBA editor. Paste the macro in the window that opens. Then just close that window to return to the Excel worksheet. Adjust the range to suit. Biff "Robin Chapple" wrote in message .. . This is an attempt to provide an attendance form for completion at a meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
Try this:
=COUNTIF(B1:B100,"present") Adjust the range to suit. Biff "Robin Chapple" wrote in message ... Many thanks. That works a treat. Is there a way to count the cells with "Present in them? Cheers, Robin On Fri, 26 Jan 2007 22:30:00 -0500, "T. Valko" wrote: Try this event macro: Assume the range of cells where you want this to happen is B1:B100. Since you want the default value to be "Absent" you'd need to already have "Absent" entered in those cells. When you select a cell in the range B1:B100 it will change from "Absent" to "Present". If you select that cell again it will change back to "Absent". Here's the macro: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Range("B1:B100")) Is Nothing Then With Target If .Value = "Absent" Then .Value = "Present" Else .Value = "Absent" End If End With End If sub_exit: Application.EnableEvents = True End Sub To use this macro, in the sheet where you want this to happen, right click the sheet tab and select View Code. This will open the VBA editor. Paste the macro in the window that opens. Then just close that window to return to the Excel worksheet. Adjust the range to suit. Biff "Robin Chapple" wrote in message . .. This is an attempt to provide an attendance form for completion at a meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
Again, Many thanks. I have learned a lot. I will apply the techniques
to other projects. Robin On Sun, 28 Jan 2007 02:03:46 -0500, "T. Valko" wrote: Try this: =COUNTIF(B1:B100,"present") Adjust the range to suit. Biff "Robin Chapple" wrote in message .. . Many thanks. That works a treat. Is there a way to count the cells with "Present in them? Cheers, Robin On Fri, 26 Jan 2007 22:30:00 -0500, "T. Valko" wrote: Try this event macro: Assume the range of cells where you want this to happen is B1:B100. Since you want the default value to be "Absent" you'd need to already have "Absent" entered in those cells. When you select a cell in the range B1:B100 it will change from "Absent" to "Present". If you select that cell again it will change back to "Absent". Here's the macro: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Range("B1:B100")) Is Nothing Then With Target If .Value = "Absent" Then .Value = "Present" Else .Value = "Absent" End If End With End If sub_exit: Application.EnableEvents = True End Sub To use this macro, in the sheet where you want this to happen, right click the sheet tab and select View Code. This will open the VBA editor. Paste the macro in the window that opens. Then just close that window to return to the Excel worksheet. Adjust the range to suit. Biff "Robin Chapple" wrote in message ... This is an attempt to provide an attendance form for completion at a meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional format for cells.
You're welcome. Thanks for the feedback!
Biff "Robin Chapple" wrote in message ... Again, Many thanks. I have learned a lot. I will apply the techniques to other projects. Robin On Sun, 28 Jan 2007 02:03:46 -0500, "T. Valko" wrote: Try this: =COUNTIF(B1:B100,"present") Adjust the range to suit. Biff "Robin Chapple" wrote in message . .. Many thanks. That works a treat. Is there a way to count the cells with "Present in them? Cheers, Robin On Fri, 26 Jan 2007 22:30:00 -0500, "T. Valko" wrote: Try this event macro: Assume the range of cells where you want this to happen is B1:B100. Since you want the default value to be "Absent" you'd need to already have "Absent" entered in those cells. When you select a cell in the range B1:B100 it will change from "Absent" to "Present". If you select that cell again it will change back to "Absent". Here's the macro: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False On Error GoTo sub_exit If Not Intersect(Target, Range("B1:B100")) Is Nothing Then With Target If .Value = "Absent" Then .Value = "Present" Else .Value = "Absent" End If End With End If sub_exit: Application.EnableEvents = True End Sub To use this macro, in the sheet where you want this to happen, right click the sheet tab and select View Code. This will open the VBA editor. Paste the macro in the window that opens. Then just close that window to return to the Excel worksheet. Adjust the range to suit. Biff "Robin Chapple" wrote in message m... This is an attempt to provide an attendance form for completion at a meeting. Currently a hard copy is printed, the record is made by pen or pencil, a mark to show who is present, and then I have to enter the results into my spreadsheet. Is it possible to apply conditional formatting to the cells so that a click changes the content of the cell between two states? One state being default "Absent", (by colour if needed), and if the cell is clicked the member is shown as being present, (by showing a different colour), another click returns the cell to the default state. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to copy a conditional format formula as paste value | Excel Discussion (Misc queries) | |||
Conditional Format | Excel Discussion (Misc queries) | |||
Conditional Formating. Remove condition, keep format? | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Cell Format Changes When Data Is Entered - Not Conditional Formatt | Excel Worksheet Functions |