PDA

View Full Version : Solved: Setting Paramaters for Builtin Dialogs



Paul_Hossler
03-12-2010, 08:20 AM
I have to intercept any user Sorts to unprotect a worksheet, perform their requested sort, and then re-protect the sheet

I'm been trying to always pre-check "Has Headers" in the dialog, but for some reason known only to Excel --- and the VBX gurus (I hope) -- the parameter is not being set by VBA, but seems to be persistant from whatever was manually selected the last time.



'You can use a single dialog box to change many properties at the same time.
'For example, you can use the Format Cells dialog box to change all the properties of the Font object.
'For some built-in dialog boxes (the Open dialog box, for example), you can set initial values
'using arg1, arg2, ..., arg30. To find the arguments to set, locate the corresponding dialog box
'constant in Built-In Dialog Box Argument Lists. For example, search for the xlDialogOpen
'constant to find the arguments for the Open dialog box. For more information about built-in
'dialog boxes, see the Dialogs collection.

'In Help search for 'Built-In Dialog Box Argument Lists'

Sub drv()

'xlDialogSortSpecial sort_by, method, key1, order1, key2, order2, key3, order3, 9=header, order, case

'call 1 - Expecting No Headers
MsgBox Application.Dialogs(xlDialogSortSpecial).Show(, , , , , , , , xlNo)

'call 2 - Expecting Headers
MsgBox Application.Dialogs(xlDialogSortSpecial).Show(, , , , , , , , xlYes)

'call 3 - Expecting No Headers
MsgBox Application.Dialogs(xlDialogSortSpecial).Show(, , , , , , , , xlNo)

'xlDialogSort orientation, key1, order1, key2, order2, key3, order3, 8=header, custom, case
'MsgBox Application.Dialogs(xlDialogSort).Show(, , , , , , , xlYes)
End Sub



I've got 3 calls to xlDialogSortSpecial and I'd think that the Has Headers would be pre-set based on the arg9 value

I also tried xlDialogSort, but same results:(


Any ideas what I'm not doing or doing wrong? :banghead:

Paul

GTO
03-12-2010, 09:39 AM
Hi Paul,

With the range to be sorted selected, seemed to work for me.

Mark

Bob Phillips
03-12-2010, 09:50 AM
I have o great lightb to throw on this I am afraid, dialogs arguments have always been flaky for me, but I assume this is 2007 - it seems okay in 2003.

BTW, you can specify arguments by name



MsgBox Application.Dialogs(xlDialogSortSpecial).Show(arg9:=xlNo)

GTO
03-12-2010, 03:37 PM
Nice info on the named arg, and I should have specified that I was in 2003 at the time.

The couple of times I have tried an app dialog, I can find the help topic that lists all the dialogs and their respective args, but have never found anything in help listing the acceptable arg values (ie - True/False, xlNo/xlYes).

Are these someplace in help, or does one save cereal box tops and send in for the decoder ring...?

Thanks,

Mark

Paul_Hossler
03-12-2010, 07:42 PM
Sorry -- I should have said it's 2007

I recorded a macro and looked at what Excel specified for arg values and copied it xlYes / xlNo

I did have the range selected in the entire Sub, and it still seems erratic (or flakey)

I tried it with the Arg9, and without it as a Called sub

Still no joy

When I get home, I'll try it again

Thanks

Paul

Bob Phillips
03-13-2010, 05:39 AM
No I wasn't saying that using Arg9 would make it work, just that you can use argument names, thereby avoidong having to count commas. It just doesn't seem to work in 2007.

SamT
03-13-2010, 08:32 AM
This seems like a good time to share some reference material I compiled. It's from xl97, but not that much has changed in 2003.

It is a zipped Workbook.

There may be something on the xlDialog worksheet that might help.

idunno4sure

Paul_Hossler
03-13-2010, 01:06 PM
No I wasn't saying that using Arg9 would make it work, just that you can use argument names, thereby avoidong having to count commas. It just doesn't seem to work in 2007.

Sorry -- I was just saying that arg was a good idea, but I couldn't get that to work either. Especially after I had to count those darn commas for the 5th time

I tried selecting the corner cell as well as the CurrentRegion and it still didn't preset the headers


I did some Googling and it might be a 2007 problem, since it seems to work for everyone in 2003:

http://www.excelforum.com/excel-programming/674125-xldialogsort-the-state-of-my-data-has-headers-cant-be-set-in-code.html



Sub drv()

'call 1 - Expecting No Headers
ActiveSheet.Cells(1, 1).Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlNo

'call 2 - Expecting Headers
ActiveSheet.Cells(1, 1).Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlYes

'call 3 - Expecting No Headers
ActiveSheet.Cells(1, 1).Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlNo
End Sub
Sub drv2()

'call 1 - Expecting No Headers
ActiveSheet.Cells(1, 1).CurrentRegion.Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlNo

'call 2 - Expecting Headers
ActiveSheet.Cells(1, 1).CurrentRegion.Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlYes

'call 3 - Expecting No Headers
ActiveSheet.Cells(1, 1).CurrentRegion.Select
Application.Dialogs(xlDialogSortSpecial).Show arg9:=xlNo
End Sub





Paul

Paul_Hossler
03-13-2010, 02:10 PM
This seems to work -- I guess the 2007 sort object is a LOT different from 2003 and earlier



Sub drv2()

'call 1 - Expecting No Headers
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.Header = xlNo '<<<<<<<<<<<<<<<<<<<<
Application.Dialogs(xlDialogSortSpecial).Show

'call 2 - Expecting Headers
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.Header = xlYes '<<<<<<<<<<<<<<<<<<<<
Application.Dialogs(xlDialogSortSpecial).Show

'call 3 - Expecting No Headers
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.Header = xlNo '<<<<<<<<<<<<<<<<<<<<
Application.Dialogs(xlDialogSortSpecial).Show
End Sub



It still doesn't explain why I could not get arg9:=xlNo, etc. to work on the .Show (which is what I really wanted), but at least I got a workaround

Thanks for the ideas and suggestions

Paul

Bob Phillips
03-13-2010, 04:47 PM
Can I ask, why are you using the dialog rather than just sorting it?

Paul_Hossler
03-13-2010, 05:46 PM
Can I ask, why are you using the dialog rather than just sorting it?

Sure (and if I over-complicated it, I'm going to be embarrassed)

I have an Excel-based "app" that has a new Ribbon tab with the macros, etc. that are applicable to the "app"

As part of this, I have a 4 specific worksheet types protected by a password that allows the users to do most things on them but not insert or delete columns since I want/need to restrict format changes to these app-specific WS's.

All cells are unlocked except for row 1 where there are specific column headers that I don't want changed because later processing depends on them

Inserting, deleting rows is OK, but leave the column order and headers alone.

View is frozen at A2 to keep the Row 1 headers from scrolling

Since the WS is protected, Excel 2007's "Sort & Filter" is unavailable (dimmed)

I have my own Sort & Filter macro, called from my own Ribbon group that

1. Unprotects the ActiveSheet
2. .Shows the xlDialogSortSpecial dialog so that the user can sort as needed
3. Re-Protects the WS

I do have some other apps where I do as you suggested: There are pre-defined sort orders that the user can pick, and they don't have to see the Excel dialog at all; all behing the scenes.

For this one however, I wanted to allow as much controlled flexibility as possible, and let the user sort using the full power of Excel on the protected WS. Hence the xlDialogSortSpecial

Problem I was having was that after getting to my sort sub, (you know users) some people cleared "Has Headers" or it was never checked, or Excel guessed wrong (row 2 might be empty), so I just wanted to always check "Has Headers" since my WS will always have headers.

I had thought about just redefining the main Sort&Filter control to go to my sub, but since my app's ribbon tab is the main 'dashboard', it just seemed easier to leave Excel's alone.

Paul

Bob Phillips
03-14-2010, 08:15 AM
What you could do Paul is use Application.InputBox(msg,Type:=8) and allow the user to select the range to be sorted, and then just use sort. I personally try to avoid the dialogs where possibel and where it does not take too much effort to do it another way.

BTW, I have just tried the dialog with Arg9:=xlYes in Excel 2010, and it still fails, it hasn't been corrected.