There is not a cell Single Click Event. But there are two events that may
work for you.

1.) Selection Change Event. This event will only fire when you click a cell
and the focus changes from a cell to the cell you clicked. But this will not
fire if the focus is already on K2 and you click K2.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String

If Target.Address = Range("K2").Address Then
strPrompt = "Do you want Put Staff into OT Order?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Operational Resources © MN "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
'do stuff
End If
End If

End Sub

2.) Before Double Click Event. This event will fire if you double click
any cell. You message box will only show if K2 is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim strPrompt As String
Dim intbuttons As Integer
Dim strTitle As String

If Target.Address = Range("K2").Address Then
strPrompt = "Do you want Put Staff into OT Order?"
intbuttons = vbYesNo + vbInformation
strTitle = "Galashiels Operational Resources © MN "
If MsgBox(strPrompt, intbuttons, strTitle) = vbYes Then
'do stuff
End If
End If

End Sub

You choose which you prefer. Hope this helps! If so, click "YES" below.
--
Cheers,
Ryan

http://www.microsoft.com/wn3/aspx/po...loc=en-US&tt=2
"terilad" wrote:

Hi,

I am looking for some help with a code.

I am looking to sort a range of cells in order smallest to largest by a
click of a cell but the code I have written does not work with the click of
the cell can anyone help.

Here is the code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Address = Range("K2").Address Then
If MsgBox("Do you want Put Staff into OT Order", vbYesNo + vbInformation,
"Galashiels Operational Resources © MN ") < _
vbYes Then Exit Sub
Sub OTOrder()
Range("A7:D16").Select
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Staff OT").Sort.SortFields.Add
Key:=Range("C7:C16" _
), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Staff OT").Sort
.SetRange Range("A7:D16")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub


Many thanks.

Mark