Tricky event procedure/app.windowstate question...

[chouston]

Hi! Anyone who can help me on this is a pro. I created a userform that is a main menu with buttons that call other macros on the spreadsheet. What I'd like to do is have just the main menu userform appear when the file is opened. I tried this:
--
Private Sub Workbook_Open()

Call showmenu

End Sub

Sub showmenu()

Application.WindowState = xlMinimized

mainmenu.Show

End Sub
--

However, the workbook stays minimized and the bar for the workbook on the windows task bar blinks orange (in Windows XP). When I click once on the bar, just the main menu userform appears like I want it to.

Is there any way to have just the main menu appear without having to click on the windows task bar?

Muchas thanks in advance for any help!

[Colo]

Hi chouston,
You need to activate excel application.
So, try AppActivate Statement.

Private Sub Workbook_Open()

    Call showmenu

End Sub



Sub showmenu()

    With Application

        .WindowState = xlMinimized

        AppActivate .Caption

    End With

    mainmenu.Show

End Sub

[chouston]

Thank You!!! It worked perfectly. You are a pro! Is there a way to take the bar for the workbook off the the windows' task bar?

Just for kicks, here's what I tried before I got the reply:

Sub showmenu()
With Application
.WindowState = xlMinimized
.WindowState = xlNormal
.Width = 235.5
.Height = 36.75
.Left = 257.5
.Top = 207.25
End With

mainmenu.Show

End Sub

It opened the workbook as small as possible and the userform covered it up, pretty amatuer eh?

[Colo]

Hello again chouston,

Is there a way to take the bar for the workbook off the the windows' task bar?

About above, recording macro will be shown the answer to you.

If you want to display only an userform, how about this?

'---Write on Userform module---

Private Sub UserForm_Terminate()

  Application.Visible = True

End Sub



'---Write on Thisworkbook module

Private Sub Workbook_Open()

    Call showmenu

End Sub



Sub showmenu()

    With Application

        AppActivate .Caption

        .Visible = False

    End With

    mainmenu.Show

End Sub

[chouston]

Thanks again Colo! the .visible = false did exactly what I was looking for (taking the bar off of the windows task bar). However, in the userform_terminate sub, application.quit serves my purposes better, but I wouldn't have figured that out without your help.