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.
[vba]
'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
[/vba]
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?
Paul