are default values possible for "Application.Dialogs(xlDialogSortSpecial).show "?

[broro183]

Hi all,

Is it possible to provide default values for a user when usin
"Application.Dialogs(xlDialogSortSpecial).Show" on a "user interfac
only" protected sheet?

I'd like to use the following recorded code* to provide default value
in the "SortSheet" macro.

*Selection.Sort Key1:=Range("B12"), Order1:=xlDescending, Key2:=Range
_
"D12"), Order2:=xlDescending, Key3:=Range("E12")
Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False
Orientation:= xlTopToBottom, DataOption1:=xlSortNormal
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

Sub SortSheet()
'to allow sheet sorting on the "user interface only" protected sheet
requires use of a _
named range "Sort_Area"
Range("'" & ActiveWorkbook.Name & "'!Sort_Area").Select
Application.Dialogs(xlDialogSortSpecial).Show
'optional MsgBox "Page is sorted as requested."
End Sub

btw, I have offered the above macro as a suggestion i
http://www.excelforum.com/showthread.php?t=501367.


thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Jim Cone]

Rob,

The "Built-In Dialog Box Argument Lists" help file topic in XL 97 shows
several options that can be applied...

"xlDialogSortSpecial sort_by, method, key1, order1, key2, order2, key3, order3, header, order, case"

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"broro183"
wrote in message
Hi all,
Is it possible to provide default values for a user when using
"Application.Dialogs(xlDialogSortSpecial).Show" on a "user interface
only" protected sheet?

I'd like to use the following recorded code* to provide default values
in the "SortSheet" macro.

*Selection.Sort Key1:=Range("B12"), Order1:=xlDescending, Key2:=Range(
_
"D12"), Order2:=xlDescending, Key3:=Range("E12"),
Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:= xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

Sub SortSheet()
'to allow sheet sorting on the "user interface only" protected sheet &
requires use of a _
named range "Sort_Area"
Range("'" & ActiveWorkbook.Name & "'!Sort_Area").Select
Application.Dialogs(xlDialogSortSpecial).Show
'optional MsgBox "Page is sorted as requested."
End Sub

thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[broro183]

Hi Jim,
Thanks for the response but either you've misunderstood my question o
I have misunderstood your answer...

The info shown next to the "*" uses the options provided in th
"Built-In Dialog Box Argument Lists" help file but this doesn't hel
answer my question of how do I incorporate this code with a ".show
method.
The reason I want to use the ".show" method is so that end users ca
see the default values that are offered (shown by *, which was recorde
via the macro recorder) but still have the ability to change these i
the popup dialog box if they want to.

Is it possible to use "Application.Dialogs(xlDialogSortSpecial).Show"
make it default to my values (shown by *)?
If so, can someone please post the line of code as I can not figure ou
any syntax which will allow this?

*Selection.Sort Key1:=Range("B12"), Order1:=xlDescending, Key2:=Range
_
"D12"), Order2:=xlDescending, Key3:=Range("E12"), Order3:=xlAscending
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
DataOption3:=xlSortNormal


Many thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Jim Cone]

Hello Rob,

The Excel sort utility does not allow the user to change the selection
once the form is shown. It sorts the selection chosen before the
form is shown or uses the current region to sort... one or the other.
You as the code writer, of course, can select any range you want
before showing the form.
As far as the other options go...shown below is a crude example.
Experiment with it...substitute various values and see what you get...
'----------------------
'From the help file
'xlDialogSortSpecial sort_by, method, key1, order1, key2, order2, key3, order3, header, order, case

Application.Dialogs(xlDialogSortSpecial).Show _
, , Range("B12"), xlDescending, Range("D12"), xlDescending, Range("E12"), xlAscending, xlYes, xlTopToBottom, False
'----------------------
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



in message
Hi Jim,
Thanks for the response but either you've misunderstood my question or
I have misunderstood your answer...

The info shown next to the "*" uses the options provided in the
"Built-In Dialog Box Argument Lists" help file but this doesn't help
answer my question of how do I incorporate this code with a ".show"
method.
The reason I want to use the ".show" method is so that end users can
see the default values that are offered (shown by *, which was recorded
via the macro recorder) but still have the ability to change these in
the popup dialog box if they want to.

Is it possible to use "Application.Dialogs(xlDialogSortSpecial).Show" &
make it default to my values (shown by *)?
If so, can someone please post the line of code as I can not figure out
any syntax which will allow this?

*Selection.Sort Key1:=Range("B12"), Order1:=xlDescending, Key2:=Range(
_
"D12"), Order2:=xlDescending, Key3:=Range("E12"), Order3:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal


Many thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[broro183]

Hi Jim,

Thank you very much, yes, I am preselecting an area based on a dynamic
named range which is also used to set the print area.
I think my problem with the ".show" was when I tried listing the
arguments was that I attempted to fill in the "sort_by, method" & not
just use a ", , " as spacers. I won't have a chance to experiment until
tomorrow but it looks like it will help tremendously
Once I understand how to make the row headers appear in the form
(rather than the absolute addresses for the sort ranges). If I can't
figure it out I'll be in touch...

Once again thanks for your guidance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...