@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
Reply With Quote
Originally Posted by GTO
