userform only not worksheet

[cedtech23]

I created a userform called frmLeaveRequest that I want to start when the user double clicks on the excel file

Please Login or Register  to view this content.

I don't want the worksheet to be visible to the user, just the userform
How can I do this?

I also want to close the active workbook and Excel if there are no other active workbook. What else do I have to and to this code to accomplish this??

Please Login or Register  to view this content.

[Tom Hutchins]

If I understand you correctly, what you are asking can't be done with Excel.
An open workbook must have at least one visible sheet. If you close the
workbook, your userform is unloaded also.

You could make all the sheets exept one blank sheet xlVeryHidden. In the
VBA, password protect the project. Have a Workbook_Open event turn off
ScreenUpdating after displaying your userform. Of course, the event won't
fire (and your userform won't be displayed) if the user disables macros - and
ulimately, you can't prevent that.

You could do what you describe using VB6 or VB.Net.

Hope this helps,

Hutch

"cedtech23" wrote:

>
> I created a userform called frmLeaveRequest that I want to start when
> the user double clicks on the excel file
>
>
> Code:
> --------------------
>
> Private Sub Workbook_Open()
> frmLeaveRequest.Show
> End Sub
>
> --------------------
>
>
> I don't want the worksheet to be visible to the user, just the
> userform
> How can I do this?
>
> I also want to close the active workbook and Excel if there are no
> other active workbook. What else do I have to and to this code to
> accomplish this??
>
>
> Code:
> --------------------
>
> Private Sub cmdExit_Click()
> ActiveWorkbook.Close
> End Sub
>
> --------------------
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=563330
>
>

[JNW]

The following should work. Excel is hidden when the workbook is opened just
leaving the form visible. When the workbook is closed it makes Excel visible
and checks to see how many workbooks are open. If more than one workbook is
open it only closes ThisWorkbook. If only one workbook then close excel.

Private Sub Workbook_Open()
frmLeaveRequest.Show
Application.Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Visible = True
If Workbooks.Count > 1 Then
ThisWorkbook.Close False 'Change to true if you want it to save the
file.
Else
Application.Quit
End if
End Sub
--
JNW


"cedtech23" wrote:

>
> I created a userform called frmLeaveRequest that I want to start when
> the user double clicks on the excel file
>
>
> Code:
> --------------------
>
> Private Sub Workbook_Open()
> frmLeaveRequest.Show
> End Sub
>
> --------------------
>
>
> I don't want the worksheet to be visible to the user, just the
> userform
> How can I do this?
>
> I also want to close the active workbook and Excel if there are no
> other active workbook. What else do I have to and to this code to
> accomplish this??
>
>
> Code:
> --------------------
>
> Private Sub cmdExit_Click()
> ActiveWorkbook.Close
> End Sub
>
> --------------------
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=563330
>
>

[Tom Hutchins]

Very cool. I stand corrected - I forgot about Application.Visible, because I
never use it. Just wanted to mention that the userform needs its ShowModal
property set to False for this to work.

Regards,

Hutch

"JNW" wrote:

> The following should work. Excel is hidden when the workbook is opened just
> leaving the form visible. When the workbook is closed it makes Excel visible
> and checks to see how many workbooks are open. If more than one workbook is
> open it only closes ThisWorkbook. If only one workbook then close excel.
>
> Private Sub Workbook_Open()
> frmLeaveRequest.Show
> Application.Visible = False
> End Sub
>
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Application.Visible = True
> If Workbooks.Count > 1 Then
> ThisWorkbook.Close False 'Change to true if you want it to save the
> file.
> Else
> Application.Quit
> End if
> End Sub
> --
> JNW
>
>
> "cedtech23" wrote:
>
> >
> > I created a userform called frmLeaveRequest that I want to start when
> > the user double clicks on the excel file
> >
> >
> > Code:
> > --------------------
> >
> > Private Sub Workbook_Open()
> > frmLeaveRequest.Show
> > End Sub
> >
> > --------------------
> >
> >
> > I don't want the worksheet to be visible to the user, just the
> > userform
> > How can I do this?
> >
> > I also want to close the active workbook and Excel if there are no
> > other active workbook. What else do I have to and to this code to
> > accomplish this??
> >
> >
> > Code:
> > --------------------
> >
> > Private Sub cmdExit_Click()
> > ActiveWorkbook.Close
> > End Sub
> >
> > --------------------
> >
> >
> > --
> > cedtech23
> > ------------------------------------------------------------------------
> > cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> > View this thread: http://www.excelforum.com/showthread...hreadid=563330
> >
> >

[JNW]

Thanks Tom. Forgot that bit.

--
JNW


"Tom Hutchins" wrote:

> Very cool. I stand corrected - I forgot about Application.Visible, because I
> never use it. Just wanted to mention that the userform needs its ShowModal
> property set to False for this to work.
>
> Regards,
>
> Hutch
>
> "JNW" wrote:
>
> > The following should work. Excel is hidden when the workbook is opened just
> > leaving the form visible. When the workbook is closed it makes Excel visible
> > and checks to see how many workbooks are open. If more than one workbook is
> > open it only closes ThisWorkbook. If only one workbook then close excel.
> >
> > Private Sub Workbook_Open()
> > frmLeaveRequest.Show
> > Application.Visible = False
> > End Sub
> >
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Application.Visible = True
> > If Workbooks.Count > 1 Then
> > ThisWorkbook.Close False 'Change to true if you want it to save the
> > file.
> > Else
> > Application.Quit
> > End if
> > End Sub
> > --
> > JNW
> >
> >
> > "cedtech23" wrote:
> >
> > >
> > > I created a userform called frmLeaveRequest that I want to start when
> > > the user double clicks on the excel file
> > >
> > >
> > > Code:
> > > --------------------
> > >
> > > Private Sub Workbook_Open()
> > > frmLeaveRequest.Show
> > > End Sub
> > >
> > > --------------------
> > >
> > >
> > > I don't want the worksheet to be visible to the user, just the
> > > userform
> > > How can I do this?
> > >
> > > I also want to close the active workbook and Excel if there are no
> > > other active workbook. What else do I have to and to this code to
> > > accomplish this??
> > >
> > >
> > > Code:
> > > --------------------
> > >
> > > Private Sub cmdExit_Click()
> > > ActiveWorkbook.Close
> > > End Sub
> > >
> > > --------------------
> > >
> > >
> > > --
> > > cedtech23
> > > ------------------------------------------------------------------------
> > > cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> > > View this thread: http://www.excelforum.com/showthread...hreadid=563330
> > >
> > >

[Peter T]

Just to add, although the workbook must contain at least one visible sheet
the workbook could be hidden (like Personal.xls). Or the workbook could be
an addin, even if named *.xls albeit a bit unconventional.

It's not clear if the OP wants to hide everything in Excel except his form,
if so go with JNW's. But if need to keep existing workbook visible put code
in a hidden workbook. Perhaps even a combination, if no other visible
workbooks open make XL invisible, otherwise leave it as is.

Regards,
Peter T

"Tom Hutchins" <TomHutchins@discussions.microsoft.com> wrote in message
news:DCE202A1-BFF6-4636-A8DA-A8E37F0753CB@microsoft.com...
> If I understand you correctly, what you are asking can't be done with
Excel.
> An open workbook must have at least one visible sheet. If you close the
> workbook, your userform is unloaded also.
>
> You could make all the sheets exept one blank sheet xlVeryHidden. In the
> VBA, password protect the project. Have a Workbook_Open event turn off
> ScreenUpdating after displaying your userform. Of course, the event won't
> fire (and your userform won't be displayed) if the user disables macros -
and
> ulimately, you can't prevent that.
>
> You could do what you describe using VB6 or VB.Net.
>
> Hope this helps,
>
> Hutch
>
> "cedtech23" wrote:
>
> >
> > I created a userform called frmLeaveRequest that I want to start when
> > the user double clicks on the excel file
> >
> >
> > Code:
> > --------------------
> >
> > Private Sub Workbook_Open()
> > frmLeaveRequest.Show
> > End Sub
> >
> > --------------------
> >
> >
> > I don't want the worksheet to be visible to the user, just the
> > userform
> > How can I do this?
> >
> > I also want to close the active workbook and Excel if there are no
> > other active workbook. What else do I have to and to this code to
> > accomplish this??
> >
> >
> > Code:
> > --------------------
> >
> > Private Sub cmdExit_Click()
> > ActiveWorkbook.Close
> > End Sub
> >
> > --------------------
> >
> >
> > --
> > cedtech23
> > ------------------------------------------------------------------------
> > cedtech23's Profile:
http://www.excelforum.com/member.php...o&userid=31022
> > View this thread:
http://www.excelforum.com/showthread...hreadid=563330
> >
> >