| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Toggle a cell
I've tried several methods but can't quite figure it out. How can I write
the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle a cell
Hi
use the Selection_Change event of your worksheet. Put the following code in your worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: I've tried several methods but can't quite figure it out. How can I write the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle a cell
Thanks Frank,
This is not quite what I was looking for. I am specifically trying to "toggle" the cell only when it is clicked on (or double-clicked), not just when the cell becomes active. Further, even when the cell is active I would like to be able to click on it again to toggle it again. Thanks, Brad "Frank Kabel" wrote: Hi use the Selection_Change event of your worksheet. Put the following code in your worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: I've tried several methods but can't quite figure it out. How can I write the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle a cell
Hi
only processing the click event is not possible. What you could do is using the Before_Doubleclick event instead of the selection_change event in the same manner -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: Thanks Frank, This is not quite what I was looking for. I am specifically trying to "toggle" the cell only when it is clicked on (or double-clicked), not just when the cell becomes active. Further, even when the cell is active I would like to be able to click on it again to toggle it again. Thanks, Brad "Frank Kabel" wrote: Hi use the Selection_Change event of your worksheet. Put the following code in your worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: I've tried several methods but can't quite figure it out. How can I write the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle a cell
Thanks Frank. I got this work. One more question. After Doubleclick I do
not want the cell left open for editing. I have been able to turn off cell editing at beginning of code but when I turn it on again at the end of the code it appears to negate turning it off at the start. Here is what I have: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EditDirectlyInCell = False If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True Application.EditDirectlyInCell = True End Sub "Frank Kabel" wrote: Hi only processing the click event is not possible. What you could do is using the Before_Doubleclick event instead of the selection_change event in the same manner -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: Thanks Frank, This is not quite what I was looking for. I am specifically trying to "toggle" the cell only when it is clicked on (or double-clicked), not just when the cell becomes active. Further, even when the cell is active I would like to be able to click on it again to toggle it again. Thanks, Brad "Frank Kabel" wrote: Hi use the Selection_Change event of your worksheet. Put the following code in your worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: I've tried several methods but can't quite figure it out. How can I write the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Toggle a cell
Hi
try: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EditDirectlyInCell = False If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If cancel=true End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: Thanks Frank. I got this work. One more question. After Doubleclick I do not want the cell left open for editing. I have been able to turn off cell editing at beginning of code but when I turn it on again at the end of the code it appears to negate turning it off at the start. Here is what I have: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.EditDirectlyInCell = False If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True Application.EditDirectlyInCell = True End Sub "Frank Kabel" wrote: Hi only processing the click event is not possible. What you could do is using the Before_Doubleclick event instead of the selection_change event in the same manner -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: Thanks Frank, This is not quite what I was looking for. I am specifically trying to "toggle" the cell only when it is clicked on (or double-clicked), not just when the cell becomes active. Further, even when the cell is active I would like to be able to click on it again to toggle it again. Thanks, Brad "Frank Kabel" wrote: Hi use the Selection_Change event of your worksheet. Put the following code in your worksheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column < 1 Then Exit Sub If Target.Cells.Count 1 Then Exit Sub Application.EnableEvents = False If Target.Value < "" Then Target.ClearContents Else Target.Value = "X" End If Application.EnableEvents = True End Sub -- Regards Frank Kabel Frankfurt, Germany Brad K. wrote: I've tried several methods but can't quite figure it out. How can I write the code so that when I click on a cell within a column it will toggle it (i.e. if blank, put an "X" value or if it has an "X" value it will leave it blank). I want this to happen whether I click on the active cell or on a new cell within the column. Also, how do I do this if it requires a double click. Thanks, Brad |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Toggle cell value | Excel Discussion (Misc queries) | |||
| Toggle Cell Colour Conditionally | Excel Worksheet Functions | |||
| Need a button to click to toggle cell color on/off | Excel Discussion (Misc queries) | |||
| Can I toggle the value of a cell by clicking directly on the cell? | Excel Discussion (Misc queries) | |||
| Toggle True/False in a cell | Excel Programming | |||
Linear Mode
