minimise workbook / display form on startup

benny · Apr 11th 2006

hello all,

i'm trying to put together a spreadsheet using excel 97 that uses forms for data input & display - the spreadsheet itself is used solely for data storage & no editing is done directly

to ensure people aren't trying to mess about with the spreadsheet i thought it would be an idea to minimise excel when the spreadsheet is launched & have the forms pop up on screen instead

the problem i've got is getting the first form on screen whilst excel is minimised

if i use

Code

Private Sub Workbook_Open()
    
    frmSplash.Show
    Application.WindowState = xlMinimized
        
End Sub

the spreadsheet launches, the splash screen launches, but excel doesn't minimise

if i use

Code

Private Sub Workbook_Open()
    
        Application.WindowState = xlMinimized
        frmSplash.Show


End Sub

the spreadsheet minimises, but the form doesn't display - the excel taskbar button flashes to indicate something is going on

clicking the taskbar button brings up the form & keeps excel minimised....but the staff who will be using this won't be happy with this at all!!!

is there any way around this or do i need to re-think completely?

i

**Batman** · Apr 11th 2006

Re: minimise workbook / display form on startup

Hi benny,

In your first example, control desn't pass back to the statement after the form is loaded unless you hide or unload the form. Are you able to include either of the following within the code module for the form?

Code

Unload Me


' or 


'Me.Hide

Regards,
Batman.

Insomniac · Apr 16th 2006

Re: minimise workbook / display form on startup

For xl97 modeless forms are not available so to release the WorkbookOpen event DoEvents is needed.
Try like this: in WorkbookOpen
[vba]Private Sub Workbook_Open()
UserForm1.Show '(or whatever your userform is called)
End Sub[/vba]
in UserForm
[vba]Option Explicit

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

Private 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

Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40

Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long

Private Sub UserForm_Initialize()
Dim hWnd As Long
Application.WindowState = xlMinimized

While hWnd = 0
DoEvents
hWnd = FindWindow(vbNullString, Me.Caption)
Wend

SetWindowPos hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE

SetForegroundWindow FindWindow("xlmain", Application.Caption)

End Sub

Private Sub UserForm_Terminate()
Application.WindowState = xlNormal
End Sub[/vba]

minimise workbook / display form on startup

Participate now!

Share