Hi

Is ther a way to check selection before I sort a sheet, I need to do
it on the fly


Thank

Yngve

Sub setselect()
Application.InputBox(prompt:="Select Something", Type:=8).Select
End Sub

This is like a pause that allows the user to select a range (either mouse or
typed).

You can then sort the Selected range.
--
Gary''s Student - gsnu200786


"Yngve" wrote:

Hi

Is ther a way to check selection before I sort a sheet, I need to do
it on the fly


Thank

Yngve

Hi Yngve,

============
Is ther a way to check selection before I sort a sheet, I need to do
it on the fly
============

I am not sure that I have understood your
question, but

Activecell

returns the currently selected cell and

Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

MsgBox Prompt:="Selected range = " _
& Selection.Address(External:=True) _
& vbNewLine & "Active cell = " _
& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.


---
Regards.
Norman

On 14 Mai, 12:01, "Norman Jones"
wrote:
Hi Yngve,

============ Is ther a way to check selection before I sort a sheet, I need to do
it on the fly

============

I am not sure that I have understood your
question, but

* * * * Activecell

returns the currently selected cell and

* * * * Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
* * Dim Rng As Range
* * Dim rCell As Range

* * Set Rng = Selection
* * Set rCell = ActiveCell

* * MsgBox Prompt:="Selected range = " _
* * * * * * * * *& Selection.Address(External:=True) _
* * * * * * * * *& vbNewLine & "Active cell = " _
* * * * * * * * *& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.

---
Regards.
Norman
thanks for replaying
Norman
I need to do the cheek to bee sure that the user has select proper
before sorting.
The range I have to select is Range("A5:H"&....)

Regards Yngve

Hi Yngve,

It is usually unnecessary, and inefficient, to
make selections. More normally, a range
variable would be declared and the variable
would be used for subsequent manipulation.

So, for example, your sort code might
resemble:

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls")
Set SH = WB.Sheets("Sheet2")
Set Rng = SH.Range("A1:C20")

With Rng
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, _
Key2:=.Cells(1, 2), _
Order2:=xlAscending, _
Key3:=.Cells(1, 2), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub
'<<===========

If, however, you do have an imperative
for the user to select a range, Gary's
Student has shown you how to return the
requisite range using the Application.InputBox
method.

If you are experiencing probleme in
implementing either suggestion, post back
with the specific problems encountered.



---
Regards.
Norman


"Yngve" wrote in message
...
On 14 Mai, 12:01, "Norman Jones"
wrote:
Hi Yngve,

============ Is ther a way to check selection before I sort a sheet, I
need to do
it on the fly

============

I am not sure that I have understood your
question, but

Activecell

returns the currently selected cell and

Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

MsgBox Prompt:="Selected range = " _
& Selection.Address(External:=True) _
& vbNewLine & "Active cell = " _
& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.

---
Regards.
Norman
thanks for replaying
Norman
I need to do the cheek to bee sure that the user has select proper
before sorting.
The range I have to select is Range("A5:H"&....)

Regards Yngve

On 14 Mai, 13:39, "Norman Jones"
wrote:
Hi Yngve,

It is usually unnecessary, and inefficient, to
make selections. More normally, a range
variable would be declared and the variable
would be used for subsequent manipulation.

So, for example, your sort code might
resemble:

'==========
Public Sub Tester()
* * Dim WB As Workbook
* * Dim SH As Worksheet
* * Dim Rng As Range

* * Set WB = Workbooks("myBook.xls")
* * Set SH = WB.Sheets("Sheet2")
* * Set Rng = SH.Range("A1:C20")

* * With Rng
* * * * .Sort Key1:=.Cells(1, 1), _
* * * * * * * Order1:=xlAscending, _
* * * * * * * Key2:=.Cells(1, 2), _
* * * * * * * Order2:=xlAscending, _
* * * * * * * Key3:=.Cells(1, 2), _
* * * * * * * Order3:=xlAscending, _
* * * * * * * Header:=xlGuess, _
* * * * * * * OrderCustom:=1, _
* * * * * * * MatchCase:=False, _
* * * * * * * Orientation:=xlTopToBottom
* * End With

End Sub
'<<===========

If, however, you do have an imperative
for the user to select a range, Gary's
Student has shown you how to return the
requisite range using the Application.InputBox
method.

If you are experiencing probleme in
implementing either suggestion, post back
with the specific problems encountered.

---
Regards.
Norman

"Yngve" wrote in message

...
On 14 Mai, 12:01, "Norman Jones"
wrote:



Hi Yngve,

============ Is ther a way to check selection before I sort a sheet, I
need to do
it on the fly

============

I am not sure that I have understood your
question, but

Activecell

returns the currently selected cell and

Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

MsgBox Prompt:="Selected range = " _
& Selection.Address(External:=True) _
& vbNewLine & "Active cell = " _
& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.

---
Regards.
Norman

thanks *for replaying
Norman
I need to do the cheek to bee sure that the user has select *proper
before sorting.
The range I have to select is Range("A5:H"&....)

Regards Yngve– Skjul sitert tekst –

– Vis sitert tekst –

Thangs for replaying Norman

Somtimes end user select wrong Range("A5:F100"), it shuld have been
Range("A5:H100") and when hi start sorting, the sorting will end up
wrong, information in Range("H..) will not bee sorted.

This is way I need to check selection before starting sorting the
sheet.

If I could combine the prosedyre with Worksheet.Selection.Chang Event
it wold bee super.

Regard Yngve

Hi Yngve,

When you say:

============
Somtimes end user select wrong Range("A5:F100"), it shuld have been
Range("A5:H100") and when hi start sorting, the sorting will end up
wrong, information in Range("H..) will not bee sorted.

This is way I need to check selection before starting sorting the
sheet.
===========

How does the user select the "wrong" range?

If *you*, the developer, know the correct
range, why involve the user at all?

If, however, the range to be sorted *must*
be selected by the user, how do you know
that the user has selected a wrong range?

Perhaps it would be best if you were to
explain:

- what you wish to sort

- why you wish to sort

- when you wish to sort


---
Regards,
Norman


"Yngve" wrote in message
...
On 14 Mai, 13:39, "Norman Jones"
wrote:
Hi Yngve,

It is usually unnecessary, and inefficient, to
make selections. More normally, a range
variable would be declared and the variable
would be used for subsequent manipulation.

So, for example, your sort code might
resemble:

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls")
Set SH = WB.Sheets("Sheet2")
Set Rng = SH.Range("A1:C20")

With Rng
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, _
Key2:=.Cells(1, 2), _
Order2:=xlAscending, _
Key3:=.Cells(1, 2), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub
'<<===========

If, however, you do have an imperative
for the user to select a range, Gary's
Student has shown you how to return the
requisite range using the Application.InputBox
method.

If you are experiencing probleme in
implementing either suggestion, post back
with the specific problems encountered.

---
Regards.
Norman

"Yngve" wrote in message

...
On 14 Mai, 12:01, "Norman Jones"
wrote:



Hi Yngve,

============ Is ther a way to check selection before I sort a sheet, I
need to do
it on the fly

============

I am not sure that I have understood your
question, but

Activecell

returns the currently selected cell and

Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

MsgBox Prompt:="Selected range = " _
& Selection.Address(External:=True) _
& vbNewLine & "Active cell = " _
& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.

---
Regards.
Norman

thanks for replaying
Norman
I need to do the cheek to bee sure that the user has select proper
before sorting.
The range I have to select is Range("A5:H"&....)

Regards Yngve– Skjul sitert tekst –

– Vis sitert tekst –

Thangs for replaying Norman

Somtimes end user select wrong Range("A5:F100"), it shuld have been
Range("A5:H100") and when hi start sorting, the sorting will end up
wrong, information in Range("H..) will not bee sorted.

This is way I need to check selection before starting sorting the
sheet.

If I could combine the prosedyre with Worksheet.Selection.Chang Event
it wold bee super.

Regard Yngve

On 14 Mai, 15:05, "Norman Jones"
wrote:
Hi Yngve,

When you say:

============
Somtimes end user select wrong Range("A5:F100"), it shuld have been
Range("A5:H100") and when hi start sorting, the sorting will end up
wrong, information in Range("H..) will not bee sorted.

This is way I need to check selection before starting sorting the
sheet.
===========

How does the user select the "wrong" range?

If *you*, the developer, know the correct
range, why involve the user at all?

If, however, the range to be sorted *must*
be selected by the user, how do you know
that *the user has selected a wrong range?

Perhaps it would be best if you were to
explain:

* * - what you wish to sort

* * - why you wish to sort

* * - when you wish to sort

---
Regards,
Norman

"Yngve" wrote in message

...
On 14 Mai, 13:39, "Norman Jones"
wrote:





Hi Yngve,

It is usually unnecessary, and inefficient, to
make selections. More normally, a range
variable would be declared and the variable
would be used for subsequent manipulation.

So, for example, your sort code might
resemble:

'==========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls")
Set SH = WB.Sheets("Sheet2")
Set Rng = SH.Range("A1:C20")

With Rng
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, _
Key2:=.Cells(1, 2), _
Order2:=xlAscending, _
Key3:=.Cells(1, 2), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub
'<<===========

If, however, you do have an imperative
for the user to select a range, Gary's
Student has shown you how to return the
requisite range using the Application.InputBox
method.

If you are experiencing probleme in
implementing either suggestion, post back
with the specific problems encountered.

---
Regards.
Norman

"Yngve" wrote in message

...
On 14 Mai, 12:01, "Norman Jones"
wrote:

Hi Yngve,

============ Is ther a way to check selection before I sort a sheet, I
need to do
it on the fly

============

I am not sure that I have understood your
question, but

Activecell

returns the currently selected cell and

Selection

returns the selected range.

If the selected range is a single cell, Activecell
and will return the same cell.

T|herefore, adapt something like:

'==========
Public Sub Tester()
Dim Rng As Range
Dim rCell As Range

Set Rng = Selection
Set rCell = ActiveCell

MsgBox Prompt:="Selected range = " _
& Selection.Address(External:=True) _
& vbNewLine & "Active cell = " _
& ActiveCell.Address(External:=True)
End Sub
'<<==========

However, a more interesting quesion
might be *why* you need to check the
selection; armed with that information,
it might well be possible to provide a
more useful response.

---
Regards.
Norman

thanks for replaying
Norman
I need to do the cheek to bee sure that the user has select proper
before sorting.
The range I have to select is Range("A5:H"&....)

Regards Yngve– Skjul sitert tekst –

– Vis sitert tekst –

Thangs for replaying Norman

Somtimes end user select wrong Range("A5:F100"), it shuld have been
Range("A5:H100") and when hi start sorting, the sorting will end up
wrong, information in Range("H..) will not bee sorted.

This is way I need to check selection before starting sorting the
sheet.

If I could combine the prosedyre with Worksheet.Selection.Chang Event
it wold bee super.

Regard Yngve– Skjul sitert tekst –

– Vis sitert tekst –

Hi Norman

I try agine.

The sheet contine workordrers, one columns has date (column F). User
chage priority on workorders (change date) and after that he have to
sort the sheet. Date column is not at the end of the row, so somtimes
user select from Range("A5:F100") (F=datecolumn) but he shuld have
selcet Range("A5:H100") because column H is et the end of row.

if I can check the selection I can stop sorting if the selection is
wrong.


sorry abote my bad language.

regards Yngve