Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default VBA
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 489
Default VBA
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default VBA
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("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 If
End If

End Sub


Thanks

Mark

"Ryan H" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 489
Default VBA
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("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
End If
End If

End Sub

--
Cheers,
Ryan


"terilad" wrote:

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("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 If
End If

End Sub


Thanks

Mark

"Ryan H" wrote:

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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 141
Default VBA
Many thanks for your help ryan.

Regards

Mark

"Ryan H" wrote:

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("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
End If
End If

End Sub

--
Cheers,
Ryan


"terilad" wrote:

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("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 If
End If

End Sub


Thanks

Mark

"Ryan H" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


All times are GMT +1. The time now is 01:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement