Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Only the User Form ... or Close Everything !
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Only the User Form ... or Close Everything !
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Only the User Form ... or Close Everything !
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open Only the User Form ... or Close Everything !
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Close User Form | Excel Discussion (Misc queries) | |||
See User Form On Open | Excel Discussion (Misc queries) | |||
Open Specific Form by User Login | Excel Discussion (Misc queries) | |||
Open a user form | Excel Discussion (Misc queries) | |||
use a button to open a user form? | Excel Discussion (Misc queries) |