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