Userform shown in windows other than Excel

[abcsms]

Hi all,

I use an Excel vba to "upload" data from Excel data to my accounting
software by sendkey command. The steps are 1)real data from Excel cells, 2)
switch the active window to accounting software, 3) use sendkey command to
fill accouting system blank field, 4) switch back to Excel and end

My question is: after switching to accounting system window, can it show a
userform with certain animated gif and text indicating that data is being
input? I can easily manage to show it in excel window, but unsuccessful in
other active windows.

Any help is much appreciated!

Thanks

Alan

 

[Guest]

The only way I know to do this (keep a userform created in Excel showing on
top of other apps) is with some Windows API functions, FindWindow and
SetWindowPos; code follows (using elements I got from the MSDN Access
knowledgebase when trying to accomplish something similar in Access):

' Declare the Windows API library functions:
Declare Function FindWindow% Lib "user32" Alias "FindWindowA" _
(ByVal lpclassname As Any, _
ByVal lpCaption As Any)

Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal cX As Long, _
ByVal cY As Long, _
ByVal wFlags As Long) As Long

' Declare constants to use as parameters:
Global Const HWND_TOPMOST = -1 ' Puts the Window permanently on top of all
others
Global Const SWP_NOSIZE = &H1 ' Do not resize the Window
Global Const SWP_NOMOVE = &H2 ' Do not move the Window

' Sub to find the userform window
' (by its title: must exactly match the .Caption property of the userform)

Public Sub TopMost(WinCaption As String)
Dim hwnd%
hwnd% = FindWindow%(0&, WinCaption)
Call SetWindowPos(hwnd%, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or
SWP_NOSIZE)
End Sub

You could use this code to activate it:
Userform1.Show ' show your form
TopMost(UserForm1.Caption) ' make it the topmost window

I am not sure if switching to another application and using SendKeys while
this is showing will cause any problems with the display of the form; I
suppose you will have to find that out.

 

[abcsms]

Thank you very much. I will try it ^^

The only way I know to do this (keep a userform created in Excel showing
on
top of other apps) is with some Windows API functions, FindWindow and
SetWindowPos; code follows (using elements I got from the MSDN Access
knowledgebase when trying to accomplish something similar in Access):

' Declare the Windows API library functions:
Declare Function FindWindow% Lib "user32" Alias "FindWindowA" _
(ByVal lpclassname As Any, _
ByVal lpCaption As Any)

Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal X As Long, _
ByVal y As Long, _
ByVal cX As Long, _
ByVal cY As Long, _
ByVal wFlags As Long) As Long

' Declare constants to use as parameters:
Global Const HWND_TOPMOST = -1 ' Puts the Window permanently on top of all
others
Global Const SWP_NOSIZE = &H1 ' Do not resize the Window
Global Const SWP_NOMOVE = &H2 ' Do not move the Window

' Sub to find the userform window
' (by its title: must exactly match the .Caption property of the userform)

Public Sub TopMost(WinCaption As String)
Dim hwnd%
hwnd% = FindWindow%(0&, WinCaption)
Call SetWindowPos(hwnd%, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or
SWP_NOSIZE)
End Sub

You could use this code to activate it:
Userform1.Show ' show your form
TopMost(UserForm1.Caption) ' make it the topmost window

I am not sure if switching to another application and using SendKeys while
this is showing will cause any problems with the display of the form; I
suppose you will have to find that out.