Open Only the User Form ... or Close Everything !

Hello;

Workbook Book1.xls opens a user form named myUserForm.

I'm trying to show only the user form on the desktop when I open Book1.xls,
with Book1.xls minimized on the Taskbar.
And , if the Cancel button on the form is clicked, then unload the form and
close Book1.xls. That simple!

In ThisWorkbook, I have the event:

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
ShowTheForm
End Sub

On the Form, I have the Cancel button:

Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
End Sub


The above procedure shows the form, with Microsoft Excel blank window in the
background, and produces a small bar at the bottom left of the screen for
Book1.xls.
Click the Cancel button on the form, and both the Form and Book1.xls close,
but the blank Excel window remains open in the background !!!!

I suppose I would not be able to edit such version of Book1.xls, but that is
fine for now!

Your suggestion(s) would be greatly appreciated.

Hi monir:

Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
Application.Visible = False
ShowTheForm
End Sub

Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
Application.Quit
End Sub


yuo can hide Excel application
Application.Visible = False

yuo can close Excel application
pplication.Quit


--
天行健，君�*�以自強不息
地勢坤，君�*�以厚德載物

http://www.vba.com.tw/plog/


"monir" wrote:

> Hello;
>
> Workbook Book1.xls opens a user form named myUserForm.
>
> I'm trying to show only the user form on the desktop when I open Book1.xls,
> with Book1.xls minimized on the Taskbar.
> And , if the Cancel button on the form is clicked, then unload the form and
> close Book1.xls. That simple!
>
> In ThisWorkbook, I have the event:
>
> Private Sub Workbook_Open()
> ActiveWindow.WindowState = xlMinimized
> ShowTheForm
> End Sub
>
> On the Form, I have the Cancel button:
>
> Private Sub btnCancel_Click()
> Unload Me
> ThisWorkbook.Close True
> End Sub
>
>
> The above procedure shows the form, with Microsoft Excel blank window in the
> background, and produces a small bar at the bottom left of the screen for
> Book1.xls.
> Click the Cancel button on the form, and both the Form and Book1.xls close,
> but the blank Excel window remains open in the background !!!!
>
> I suppose I would not be able to edit such version of Book1.xls, but that is
> fine for now!
>
> Your suggestion(s) would be greatly appreciated.
>

Hi chijanzen;

It works ... almost perfectly! Just couple of comments.

You don't need the ActiveWindow.WindowState = xlMinimized statement in the
w/b Open code since the w/b would be invisible anyway.

More importantly, the functionality of the Form is disrupted somewhat by the
having the necessary Application.Visible = False statement in ThisWorkbook
open event. Some of the Form buttons open other w/bs, which remain invisible
until you open another w/b and then all the previously opened w/bs by the
Form (but invisible) come rushing onto the screen! Unfortunately ThisWorkbook
does not have the Visible property.

I've added Application.Visible = True to the Form buttons that deal with
opening files, and it seems to be working fine with no apparent conflicts so
far. Will continue testing!

As I suspected, with these changes it would not be possible to edit this
version of Book1.xls, simply because book1.xls would be either invisible or
closed. So, I must remember to keep a copy of the original version as well.

Incidentally, do you know of a way to manually access an invisible w/b?
This would be really very helpfull, so I don't have to edit the original file
from scratch each time I want to modify the "invisible" w/b.

Thanks again.


"chijanzen" wrote:

> Hi monir:
>
> Private Sub Workbook_Open()
> ActiveWindow.WindowState = xlMinimized
> Application.Visible = False
> ShowTheForm
> End Sub
>
> Private Sub btnCancel_Click()
> Unload Me
> ThisWorkbook.Close True
> Application.Quit
> End Sub
>
>
> yuo can hide Excel application
> Application.Visible = False
>
> yuo can close Excel application
> pplication.Quit
>
>
> --
> 天行健，君�*�以自強不息
> 地勢坤，君�*�以厚德載物
>
> http://www.vba.com.tw/plog/
>
>
> "monir" wrote:
>
> > Hello;
> >
> > Workbook Book1.xls opens a user form named myUserForm.
> >
> > I'm trying to show only the user form on the desktop when I open Book1.xls,
> > with Book1.xls minimized on the Taskbar.
> > And , if the Cancel button on the form is clicked, then unload the form and
> > close Book1.xls. That simple!
> >
> > In ThisWorkbook, I have the event:
> >
> > Private Sub Workbook_Open()
> > ActiveWindow.WindowState = xlMinimized
> > ShowTheForm
> > End Sub
> >
> > On the Form, I have the Cancel button:
> >
> > Private Sub btnCancel_Click()
> > Unload Me
> > ThisWorkbook.Close True
> > End Sub
> >
> >
> > The above procedure shows the form, with Microsoft Excel blank window in the
> > background, and produces a small bar at the bottom left of the screen for
> > Book1.xls.
> > Click the Cancel button on the form, and both the Form and Book1.xls close,
> > but the blank Excel window remains open in the background !!!!
> >
> > I suppose I would not be able to edit such version of Book1.xls, but that is
> > fine for now!
> >
> > Your suggestion(s) would be greatly appreciated.
> >

Monir,

In another spreadsheet that doesn't set application.visible=3Dfalse, turn
off macros. Then when you open the spreadsheet it will not run the
macros and you can edit your spreadsheet.

Jacob

monir wrote:
> Hi chijanzen;
>
> It works ... almost perfectly! Just couple of comments.
>
> You don't need the ActiveWindow.WindowState =3D xlMinimized statement in =
the
> w/b Open code since the w/b would be invisible anyway.
>
> More importantly, the functionality of the Form is disrupted somewhat by =
the
> having the necessary Application.Visible =3D False statement in ThisWorkb=
ook
> open event. Some of the Form buttons open other w/bs, which remain invis=
ible
> until you open another w/b and then all the previously opened w/bs by the
> Form (but invisible) come rushing onto the screen! Unfortunately ThisWork=
book
> does not have the Visible property.
>
> I've added Application.Visible =3D True to the Form buttons that deal with
> opening files, and it seems to be working fine with no apparent conflicts=
so
> far. Will continue testing!
>
> As I suspected, with these changes it would not be possible to edit this
> version of Book1.xls, simply because book1.xls would be either invisible =
or
> closed. So, I must remember to keep a copy of the original version as we=
ll.
>
> Incidentally, do you know of a way to manually access an invisible w/b?
> This would be really very helpfull, so I don't have to edit the original =
file
> from scratch each time I want to modify the "invisible" w/b.
>
> Thanks again.
>
>
> "chijanzen" wrote:
>
> > Hi monir:
> >
> > Private Sub Workbook_Open()
> > ActiveWindow.WindowState =3D xlMinimized
> > Application.Visible =3D False
> > ShowTheForm
> > End Sub
> >
> > Private Sub btnCancel_Click()
> > Unload Me
> > ThisWorkbook.Close True
> > Application.Quit
> > End Sub
> >
> >
> > yuo can hide Excel application
> > Application.Visible =3D False
> >
> > yuo can close Excel application
> > pplication.Quit
> >
> >
> > --
> > =E5=A4=A9=E8=A1=8C=E5=81=A5=EF=BC=8C=E5=90=9B=E5=AD=90=E4=BB=A5=E8=87=
=AA=E5=BC=B7=E4=B8=8D=E6=81=AF
> > =E5=9C=B0=E5=8B=A2=E5=9D=A4=EF=BC=8C=E5=90=9B=E5=AD=90=E4=BB=A5=E5=8E=
=9A=E5=BE=B7=E8=BC=89=E7=89=A9
> >
> > http://www.vba.com.tw/plog/
> >
> >
> > "monir" wrote:
> >
> > > Hello;
> > >
> > > Workbook Book1.xls opens a user form named myUserForm.
> > >
> > > I'm trying to show only the user form on the desktop when I open Book=
1=2Exls,
> > > with Book1.xls minimized on the Taskbar.
> > > And , if the Cancel button on the form is clicked, then unload the fo=
rm and
> > > close Book1.xls. That simple!
> > >
> > > In ThisWorkbook, I have the event:
> > >
> > > Private Sub Workbook_Open()
> > > ActiveWindow.WindowState =3D xlMinimized
> > > ShowTheForm
> > > End Sub
> > >
> > > On the Form, I have the Cancel button:
> > >
> > > Private Sub btnCancel_Click()
> > > Unload Me
> > > ThisWorkbook.Close True
> > > End Sub
> > >
> > >
> > > The above procedure shows the form, with Microsoft Excel blank window=
in the
> > > background, and produces a small bar at the bottom left of the screen=
for
> > > Book1.xls.
> > > Click the Cancel button on the form, and both the Form and Book1.xls =
close,
> > > but the blank Excel window remains open in the background !!!!
> > >
> > > I suppose I would not be able to edit such version of Book1.xls, but =
that is
> > > fine for now!
> > >=20
> > > Your suggestion(s) would be greatly appreciated.
> > >

Hi Jacob;
I'm not sure I understand your suggestion.

1. w/b book1.xls has 1 w/s with 1 macro to show the userform, userform and
its macros, and w/b open event.
Open book1.xls, and the userform is displayed with book1 sheet1 in the
background. Hit the cancel button on the form, and the userform is unloaded,
and I've full access to book1 to do whatever editing or changing I want to do.
Book1 works fine and as intended.

2. w/b invBook1.xls is a copy of Book1.xls, but with a few changes to make
the w/b not visible while the userform is displayed.
Open invBook1.xls, and the userform is displayed, invBook1 is not visible,
and the userform is fully functional.
Click the relevant button to proceed with selected computations, an action
which also unloads the form and closes invBook1. Or, click the cancel button
to unload the form and close invBook1.
invBook1 works fine and exactly as intended.

3. invBook1 is intentionally made invisible to reduce the clutter on the
screen & on the taskbar, and it's closed later to avoid problems with
arranging subsequent opened windows.

4. To do changes to invBook1, I've to do the changes first in Book1, test,
debug, etc., add the changes (2. above), save as invBook1, test again, back
to Book1, to edit again, and so on.

5. My question was, and still is,:
Is it possible to manually access invBook1 while it's opened but invisible
??? ... so that I may edit it directly should the need arise and forget about
4. above and Book1.
After all, it's still Excel environment, and invBook1, though not visible,
is there open somewhere.

6. If it's not possible to access invBook1 while it's open and not visible,
how about: Add a "special button" to the form. By special I mean, say, a
round & red command button, with a captionEdit.
Something like:
.......Private Sub btnEdit_Click()
...........Unload Me
...........Application.Visible = True
.......End Sub

Why round & red button? Well, it would have nothing to do with the
computations, and thus to make it distinguishable among the many other
buttons on the form!
I've never used such special controls, and I'm not even sure if they do
exist among the 10s that are available in XL2003 ToolBox. But, I've seen
them used on forms!

Sorry for the long description!

Any suggestions? Thank you kindly.



"Jacob" wrote:

> Monir,
>
> In another spreadsheet that doesn't set application.visible=false, turn
> off macros. Then when you open the spreadsheet it will not run the
> macros and you can edit your spreadsheet.
>
> Jacob
>
> monir wrote:
> > Hi chijanzen;
> >
> > It works ... almost perfectly! Just couple of comments.
> >
> > You don't need the ActiveWindow.WindowState = xlMinimized statement in the
> > w/b Open code since the w/b would be invisible anyway.
> >
> > More importantly, the functionality of the Form is disrupted somewhat by the
> > having the necessary Application.Visible = False statement in ThisWorkbook
> > open event. Some of the Form buttons open other w/bs, which remain invisible
> > until you open another w/b and then all the previously opened w/bs by the
> > Form (but invisible) come rushing onto the screen! Unfortunately ThisWorkbook
> > does not have the Visible property.
> >
> > I've added Application.Visible = True to the Form buttons that deal with
> > opening files, and it seems to be working fine with no apparent conflicts so
> > far. Will continue testing!
> >
> > As I suspected, with these changes it would not be possible to edit this
> > version of Book1.xls, simply because book1.xls would be either invisible or
> > closed. So, I must remember to keep a copy of the original version as well.
> >
> > Incidentally, do you know of a way to manually access an invisible w/b?
> > This would be really very helpfull, so I don't have to edit the original file
> > from scratch each time I want to modify the "invisible" w/b.
> >
> > Thanks again.
> >
> >
> > "chijanzen" wrote:
> >
> > > Hi monir:
> > >
> > > Private Sub Workbook_Open()
> > > ActiveWindow.WindowState = xlMinimized
> > > Application.Visible = False
> > > ShowTheForm
> > > End Sub
> > >
> > > Private Sub btnCancel_Click()
> > > Unload Me
> > > ThisWorkbook.Close True
> > > Application.Quit
> > > End Sub
> > >
> > >
> > > yuo can hide Excel application
> > > Application.Visible = False
> > >
> > > yuo can close Excel application
> > > pplication.Quit
> > >
> > >
> > > --
> > > 天行健，君�*�以自強不息
> > > 地勢坤，君�*�以厚德載物
> > >
> > > http://www.vba.com.tw/plog/
> > >
> > >
> > > "monir" wrote:
> > >
> > > > Hello;
> > > >
> > > > Workbook Book1.xls opens a user form named myUserForm.
> > > >
> > > > I'm trying to show only the user form on the desktop when I open Book1.xls,
> > > > with Book1.xls minimized on the Taskbar.
> > > > And , if the Cancel button on the form is clicked, then unload the form and
> > > > close Book1.xls. That simple!
> > > >
> > > > In ThisWorkbook, I have the event:
> > > >
> > > > Private Sub Workbook_Open()
> > > > ActiveWindow.WindowState = xlMinimized
> > > > ShowTheForm
> > > > End Sub
> > > >
> > > > On the Form, I have the Cancel button:
> > > >
> > > > Private Sub btnCancel_Click()
> > > > Unload Me
> > > > ThisWorkbook.Close True
> > > > End Sub
> > > >
> > > >
> > > > The above procedure shows the form, with Microsoft Excel blank window in the
> > > > background, and produces a small bar at the bottom left of the screen for
> > > > Book1.xls.
> > > > Click the Cancel button on the form, and both the Form and Book1.xls close,
> > > > but the blank Excel window remains open in the background !!!!
> > > >
> > > > I suppose I would not be able to edit such version of Book1.xls, but that is
> > > > fine for now!
> > > >
> > > > Your suggestion(s) would be greatly appreciated.
> > > >
>
>