PDA

View Full Version : Access different tabs in Excel forms



JPL007
07-02-2014, 08:47 AM
Hi All

I am trying to create a macro that would open "Format" -> "Cells",
and then switch to the tab "Border" (tab by default is "Number". All tabs are : Number/Alignment/Font/Border/Patterns/Protection).
(I want to avoid sendKeys)




Public Sub findFunction(functionName)
For i = 1 To CommandBars("Worksheet Menu Bar").Controls.Count
Set mn = CommandBars("Worksheet Menu Bar").Controls.Item(i)
For j = 1 To mn.Controls.Count
Set fc = mn.Controls.Item(j)
If Replace(Replace(LCase(fc.Caption), "&", ""), ".", "") = LCase(functionName) Then
leMenuIndex = mn.Index
leFunctionIndex = fc.Index
GoTo lineExitFindFunction
End If
Next j
Next i



lineExitFindFunction:
Set leMenu = CommandBars("Worksheet Menu Bar").Controls.Item(leMenuIndex)
Set leFunction = leMenu.Controls.Item(leFunctionIndex)

leMenu.CommandBar.AdaptiveMenu = False
leMenu.Execute
leMenu.Controls.Item(1).SetFocus
leFunction.SetFocus
leFunction.execute

End Sub

After this code, the "Format Cells" form is open. But I do not know how to access this form object,
and consequently to change tab.

any ideas?
thanks a lot for your help
Julien

GTO
07-02-2014, 11:58 AM
Have you tried:
Application.Dialogs(xlDialogBorder).Show

JPL007
07-03-2014, 02:01 AM
Thanks a lot.
thats almost what i want.

This thing is opening the border tab on its own.
I would still like to have access to the other tabs.

GTO
07-04-2014, 01:44 PM
Yeh, I know :-( I flipped thru all the dialogs, and if it's accessible, I sure didn't see it. Hopefully another member can point out what (probably painfully obvious 'thing') I missed?

Bob Phillips
07-04-2014, 03:38 PM
No, each dialog is a separate tab. You can use the old commandbar but that opens on the last accessed tab.

GTO
07-04-2014, 08:27 PM
@XLD:

Hi Bob,

Gosh, there's like 1200+ dialogs, leastwise by .Count

Anyways, I could not find (least not with the Spy I use) anything to latch onto (like for instance if each tab in the form that shows using the cells context menu, or as you said, the old commandbar like the OP shows, were to be its own window...).

@JPL007:

Hi Julien,

Quickly written/tried, but if you don't mind a tiny flash, this would just momentarily show the dialog/tab wanted, then display the form as you are used to seeing it (all tabs).

In a Standard Module:



Option Explicit

Private Const WM_CLOSE = &H10

Private Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any _
) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String _
) As Long

Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long _
) As Long

Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, _
ByVal nIDEvent As Long _
) As Long

Private hwndDialog As Long
Private lRetSet As Long

Private Sub TimerProc(ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long)

hwndDialog = FindWindow("bosa_sdm_XL9", "Format Cells")

If hwndDialog <> 0 Then
KillTimer 0&, lRetSet
'Debug.Print "Killed"
SendMessage hwndDialog, WM_CLOSE, 0&, 0&
End If

End Sub

Sub aCallIt()

MsgBox "Called w/a slight delay to see the ""flash""", 0&, vbNullString
Application.OnTime Now + TimeSerial(0, 0, 3), "'findFunction ""Cells""'"

End Sub

Public Sub findFunction(functionName)
Dim i As Long, j As Long, leFunctionIndex As Long
Dim mn, fc, leMenuIndex, leMenu, leFunction

For i = 1 To CommandBars("Worksheet Menu Bar").Controls.Count

Set mn = CommandBars("Worksheet Menu Bar").Controls.Item(i)

For j = 1 To mn.Controls.Count

Set fc = mn.Controls.Item(j)
'Debug.Print fc.Caption
'Stop
If Replace(Replace(LCase(fc.Caption), "&", ""), ".", "") = LCase(functionName) Then
leMenuIndex = mn.Index
leFunctionIndex = fc.Index
GoTo lineExitFindFunction
End If
Next j
Next i

Exit Sub
lineExitFindFunction:

'Set a quick timer callback, display just the Border Dialog and close it. This leaves
'this tab as the last used, so the desired tab is active when you call it.
lRetSet = 0&
hwndDialog = 0&
lRetSet = SetTimer(0&, 0&, 1&, AddressOf TimerProc)
Application.Dialogs(xlDialogBorder).Show

Set leMenu = CommandBars("Worksheet Menu Bar").Controls.Item(leMenuIndex)
Set leFunction = leMenu.Controls.Item(leFunctionIndex)

leMenu.CommandBar.AdaptiveMenu = False
leMenu.Execute
leMenu.Controls.Item(1).SetFocus
leFunction.SetFocus
leFunction.Execute

End Sub

Does that help at all?

Mark

GTO
07-05-2014, 03:07 AM
P.S. - Regardless if you want to try the API or not, I think you would want to include the Exit Sub.

snb
07-06-2014, 02:41 AM
Sub M_snb()
Application.Dialogs(45).Show
End Sub

GTO
07-06-2014, 03:51 AM
@snb:

Been there, done that... The OP wants all the tabs to show, but with the borders tab selected...


Have you tried:
Application.Dialogs(xlDialogBorder).Show


Thanks a lot.
thats almost what i want.

This thing is opening the border tab on its own.
I would still like to have access to the other tabs.

JPL007
07-07-2014, 06:18 AM
Waou, lots of things I dont know here ;-). I Will need to do some digging.

First of all, that works. So, thank you very much for your help and time on this.

Now I am trying to make this as general as possible.

Would you have any ideas on how to get automatically the name of the window opened by :
Application.Dialogs(xlDialogBorder).Show?
this way, I will be able to replace "Format Cells" in

hwndDialog = FindWindow("bosa_sdm_XL9", "Format Cells")

by the name of the relevant window.

hwndDialog = FindWindow("bosa_sdm_XL9", relevantWindowName)

Kind regards
Julien

GTO
07-07-2014, 10:41 PM
Would you have any ideas on how to get automatically the name of the window opened by :

Application.Dialogs(xlDialogBorder).Show?


I may not be understanding, but I don't believe we'd want to do that. That is, I think our best shot of closing the correct window is to identify it by both classname and titlebar text. Does that seem sensible?

Mark

JPL007
07-08-2014, 05:22 AM
thanks a lot Mark.

I will investiguate.

Kind regards
Julien

JPL007
07-23-2014, 08:42 AM
Hi guys

actually I have similar question that I am going to put in the same thread. I am guessing an API is the solution.


I am trying to input a function name in the function wizard form and then press enter to access the form "Function argument".

Example of algorithm script:

Function name = "PRODUCT"
Application.Dialogs(xlDialogFunctionWizard).show
It will put "PRODUCT" in "Search for a function" textbox.
Then execute Go, then OK to display the relevant form "Function argument".

thanks a lot for your help.
Kind regards
Julien