userform only not worksheet
 

I created a userform called frmLeaveRequest that I want to start whe
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 th
userform
How can I do this?

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


Code
-------------------

Private Sub cmdExit_Click()
ActiveWorkbook.Close
End Sub

-------------------

--
cedtech2
-----------------------------------------------------------------------
cedtech23's Profile: http://www.excelforum.com/member.php...fo&userid=3102
View this thread: http://www.excelforum.com/showthread.php?threadid=56333

userform only not worksheet
 

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

userform only not worksheet
 

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

userform only not worksheet
 

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

userform only not worksheet
 

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

userform only not worksheet
 

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" wrote in message
...
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