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