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

    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

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

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

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

Participate now!

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