VBA

[terilad]

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("A716").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("A716")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub


Many thanks.

Mark

 

[Ryan H]

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.

 

[terilad]

Hi,

I have tried this code and I am getting compile error message,

End If without Block If.

Any ideas where I am going wrong, the code I have in place is:

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
Range("A716").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("A716")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End If
End If

End Sub


Thanks

Mark

 

[Ryan H]

You have forgotten to use the End With. When you use the With statement it
must be used like this:

With Objects
'statements
End With

Try this. It worked for me. Hope this helps! If so, let me know, click
"YES" below.

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
Range("A716").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("A716")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End If

End Sub

 

[terilad]

Many thanks for your help ryan.

Regards

Mark

You have forgotten to use the End With. When you use the With statement it
must be used like this:

With Objects
'statements
End With

Try this. It worked for me. Hope this helps! If so, let me know, click
"YES" below.

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
Range("A716").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("A716")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End If

End Sub