Joe..

All of what you want can be achieved!

This demo show's what's happening with the form's events..

And instead of minimising, we're hiding excel completely!

Be very carefull
IF your code crashes... NO way to make the session visible again.

I've included the messagebox to show the event gets triggered
Plus I've added an application.inputbox type 8(range reference)
for the demo


Put this in a userform with a commandbutton and a textbox wide enough
to hold a fully qualified range address

Written in excelXP and tested in Excel97,
Works well with Modal and Modeless userforms.


Option Explicit

Private Sub CommandButton1_Click()
On Error GoTo exitH
Me.Hide

If InputBox("Type the password") = "wp" Then
Application.Visible = True
ActiveWorkbook.Worksheets(2).Activate
Me.TextBox1.Text = Application.InputBox( _
Prompt:="Now select a range", Type:=8).Address(external:=True)
Me.TextBox1.Visible = True
Else
MsgBox "Wrong password.. it's: wp in lowercase"
End If

exitH:
If Err < 0 Then MsgBox "ERROR on commandclick!" & vbNewLine & _
Err.Description
Me.Show 'this activates thus hides excel!
End Sub

Private Sub UserForm_Activate()
Application.Visible = False
Debug.Print "form activated"
End Sub

Private Sub UserForm_Initialize()
On Error GoTo errH
Application.VBE.MainWindow.Visible = False
Me.CommandButton1.Caption = "Get the range" & vbNewLine & "(pw
protected)"
Me.CommandButton1.AutoSize = True
Me.TextBox1.Visible = False
Me.Caption = "Demo by keepITcool"
Debug.Print "form initialized"
Exit Sub

errH:
MsgBox "An error occurred during the form's init" & vbNewLine &
Err.Description
Application.Visible = True
End
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Debug.Print "form closed ,"cancel:"; Cancel, "closemode:"; CloseMode
'we have terminate to handle the close
End Sub

Private Sub UserForm_Terminate()
Application.Visible = True
Application.VBE.MainWindow.Visible = True
Debug.Print "form terminated"
End Sub


Have fun...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Joe 90" (remove silly spam) wrote:

There is probably nothing that can be done about this, but when using
Application.Windowstate xlminimised, to run a form, I have a input box
to close the form with a password, but cannot see it (even though you
can still type the password). Do I have to use another userform to
handle this problem.

Also is there any way to bring the form to the front of any other
windows that might be open, when minimising the spreadsheet?