Hey all,

I'm wanting to just click on a cell and it automatically input "P", kind of like a option button. I have a column set up so that if there is a "P" in the cell I can sort the sheet to show only those rows with "P". I'm wanting to just click on a cell in that column and it input "P" so I don't have to type it in. I hope that's explaining it clearly enough.

Any ideas?

Keyrookie

Put in sheet's code module
if u double click a P is inserted but only in column B change B:B if u want
another

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target = Empty Then Target = "P"
End Sub



"Keyrookie" skrev:


Hey all,

I'm wanting to just click on a cell and it automatically input "P",
kind of like a option button. I have a column set up so that if there
is a "P" in the cell I can sort the sheet to show only those rows with
"P". I'm wanting to just click on a cell in that column and it input
"P" so I don't have to type it in. I hope that's explaining it clearly
enough.

Any ideas?

Keyrookie




--
Keyrookie

How about a double-click?

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Const MY_RANGE As String = "A1:A100"
Dim cell As Range
On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(MY_RANGE)) Is Nothing Then
Target.Value = "P"
End If
Cancel = True
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Adjust MY_RANGE to suit.


Gord Dibben MS Excel MVP

On Sat, 22 Sep 2007 17:45:38 +0100, Keyrookie
wrote:


Hey all,

I'm wanting to just click on a cell and it automatically input "P",
kind of like a option button. I have a column set up so that if there
is a "P" in the cell I can sort the sheet to show only those rows with
"P". I'm wanting to just click on a cell in that column and it input
"P" so I don't have to type it in. I hope that's explaining it clearly
enough.

Any ideas?

Keyrookie

Thank you Gord, it worked perfectly! Can I do it for more than 1 column on the same sheet?

Keyrookie

Const MY_RANGE As String = "A:A,D:D"

or whatever columns or ramges in those columns you wish.

Const MY_RANGE As String = "A1:A100, D23:D97"


Gord

On Sun, 23 Sep 2007 04:43:38 +0100, Keyrookie
wrote:


Thank you Gord, it worked perfectly! Can I do it for more than 1
column on the same sheet?

Keyrookie


Gord Dibben;559497 Wrote:
How about a double-click?

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Const MY_RANGE As String = "A1:A100"
Dim cell As Range
On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(MY_RANGE)) Is Nothing Then
Target.Value = "P"
End If
Cancel = True
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View
Code".

Copy/paste into that sheet module.

Adjust MY_RANGE to suit.


Gord Dibben MS Excel MVP

On Sat, 22 Sep 2007 17:45:38 +0100, Keyrookie
wrote:
-

Hey all,

I'm wanting to just click on a cell and it automatically input "P",
kind of like a option button. I have a column set up so that if
there
is a "P" in the cell I can sort the sheet to show only those rows
with
"P". I'm wanting to just click on a cell in that column and it input
"P" so I don't have to type it in. I hope that's explaining it
clearly
enough.

Any ideas?

Keyrookie-