Solved: Hide UserForm & Stop Closing Excel

[Rob342]

Hi All
I have a multipage user form that opens directly from workbook open procedure, at the moment the user cannot access excel without closing the form, the user wants to open various other excel spreadsheets at the same time

I can hide the form by using the code below and unhide the form from the prompt page which is visible & protected, all other sheets are hidden.
[vba]
Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Me.Hide
End Sub
[/vba]
The problem it leaves the user in the excel?
How can i make the user return to the form, without closing the workbook that this form belongs to? but the can close any other excel workbook.
Have also tried having the form vbmodeless
Hope thats clear
Rob

[GTO]

Greetings,

...The problem it leaves the user in the excel?

I am not following. I can hide a userform (vbModal) and switch applications w/o issue. Actually, the form can be displayed and I can still switch to another application, so could you re-explain this?

...How can i make the user return to the form, without closing the workbook that this form belongs to?

but the can close any other excel workbook.

I am afraid I am not following this part at all

Could you attach a small workbook, .xls format preferably, showing the basic issue? Maybe that will help us see what you are trying to solve

Mark

[Rob342]

HI Mark

Thanks for the reply
Have attached a small example, the form is full screen and because there is no min button you cannot make it smaller, so i need to hide it and make the user return to the form, without closing the workbook that this form belongs to.

Rob

ps if you want the full WB can i send this PM

[snb]

This might be sufficient:

[VBA]
Private Sub CommandButton8_Click()
Hide
ThisWorkbook.Save
End Sub
Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Move 0, 0, .Width, .Height
End With
End Sub
[/VBA]

[Rob342]

thanks snb

But how does the user get the form to reappear? and it does not stop the user hitting the X button in excel to close excel down without returning to the form?
Rob

[snb]

Insert an object in the sheet or use doubleclick in a certain cell:

[vba]
Private commandbutton1_Clcik()
userform1.show
end sub
[/vba]

NB. userform1.show :
- loads the userform
- shows the userform

if the userform has been hidden, userform1.show
- doesn't load the userform again but only
- shows the userform (retaining the most recent values of the userform controls).

[Rob342]

thanks snb

i did originally try this, but thought there might be something more elegant
is there no way to stop the user hitting the X button to close the sheet or can you set Workbook before close to cancel =false ?

Rob

[GTO]

thanks snb

i did originally try this, but thought there might be something more elegant
is there no way to stop the user hitting the X button to close the sheet or can you set Workbook before close to cancel =false ?

Rob

You could disable the the workbook's close button, but really just blocking the action would seem much more effective/easier to me.

In the ThisWorkbook Module:

[VBA]Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long

For i = 0 To UserForms.Count - 1
If UserForms(i).Name = "frmTest" Then
Cancel = True
UserForms(i).Show
Exit For
End If
Next
End Sub[/VBA]

In the UserForm's Module:
[VBA]Option Explicit

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Cancel or Exit Button !"
End If
End Sub

Private Sub cmdExit_Click()
Unload Me
ThisWorkbook.Save
ThisWorkbook.Close False
End Sub

Private Sub cmdHide_Click()
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim lngWinState As XlWindowState
Dim ws As Worksheet
Dim IRow As Long

With Application
.ScreenUpdating = False
lngWinState = .WindowState
.WindowState = xlMaximized
Me.Move 0, 0, .Width, .Height
.WindowState = lngWinState
.ScreenUpdating = True
End With
End Sub

Private Sub UserForm_Resize()
Me.cmdHide.Top = (Me.Height - Me.cmdHide.Height) - 80
Me.cmdHide.Left = Me.Width - Me.cmdHide.Width - 26
Me.cmdExit.Top = Me.cmdHide.Top + Me.cmdHide.Height + 6
Me.cmdExit.Left = Me.cmdHide.Left
End Sub[/VBA]

Please note that added a userform and am simply using the form's Name property to see if it still exists.

Hope that helps,

Mark

[Rob342]

Thanks Mark
That works just the way i want it to.
most appreciated
Rob

[GTO]

Thanks Mark
That works just the way i want it to.
most appreciated
Rob

You are most welcome and glad it worked :-)

[snb]

an alternative approach