minimise workbook / display form on startup

  • 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

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

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

    if i use

    Private Sub Workbook_Open()
            Application.WindowState = xlMinimized
    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?


  • 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?

    Unload Me
    ' or 



  • 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
    hWnd = FindWindow(vbNullString, Me.Caption)

    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]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!