Form focus (Office 97)

Topic

New Reply

My spreadsheet opens with a menu form but my users want to be able to access toolbar items
Is there a way to use a menu bar item without having to close the form first
for example if the form is displayed and a user wants to Print (which is not on the UserForm – I know it could be) they have to click a button to close the form to make the menubar items acessable

Reply | Quote

Replies

This excerpt from one of Walkenbach’s books may help. But please note – the book I use was written for Excel2000. I noticed that you’re using 97. I really can’t say if this info will work for 97 – you may want to wait for someone else to chime in…
[indent]

---

In Excel 2000, a UserForm can be modeless. This means that the user doesn’t have to dismiss the UserForm before activating the workbook and doing other work in Excel. The Show method of the UserForm object defaults to displaying a modal form, meaning that the rest of Excel is suspended while the UserForm is visible. To display a modeless UserForm, use an instruction such as this:

UserForm1.Show vbModeless

The word vbModeless is a built-in constant that has a value of 0. Therefore, the effect of the following statement is identical to that of the preceeding statement:

UserForm1.Show 0

---

[/indent]

Reply | Quote

This excerpt from one of Walkenbach’s books may help. But please note – the book I use was written for Excel2000. I noticed that you’re using 97. I really can’t say if this info will work for 97 – you may want to wait for someone else to chime in…
[indent]

---

In Excel 2000, a UserForm can be modeless. This means that the user doesn’t have to dismiss the UserForm before activating the workbook and doing other work in Excel. The Show method of the UserForm object defaults to displaying a modal form, meaning that the rest of Excel is suspended while the UserForm is visible. To display a modeless UserForm, use an instruction such as this:

UserForm1.Show vbModeless

The word vbModeless is a built-in constant that has a value of 0. Therefore, the effect of the following statement is identical to that of the preceeding statement:

UserForm1.Show 0

---

[/indent]

Reply | Quote

In addition to suggestions of modeless form that others provided:

If there were not a lot of toolbar items that the user might want to access, you could just create buttons to call the items from the userform. Add a “Print” button, or a “print preview” or any other items they might want.

If their is no room on the form, just add one button to call a new user form which contains all the supplemental menu items.

another idea is to make your menu a new toolbar (if it only contains commands) instead a user form, and they can use these toolbars and the “normal” toolbars.

Steve

Steve

Reply | Quote

In addition to suggestions of modeless form that others provided:

If there were not a lot of toolbar items that the user might want to access, you could just create buttons to call the items from the userform. Add a “Print” button, or a “print preview” or any other items they might want.

If their is no room on the form, just add one button to call a new user form which contains all the supplemental menu items.

another idea is to make your menu a new toolbar (if it only contains commands) instead a user form, and they can use these toolbars and the “normal” toolbars.

Steve

Steve

Reply | Quote

(Edited by Leif on 13-Mar-04 16:33. to update link to http://www.bmsltd.ie/)

Excel only started supporting modeless forms with XL2K. But, thanks to a bug in XL97, it actually can be done, but it’s not a good idea. There is a link to ModelessForm.zip on Stephen Bullen’s Excel Page that shows how it can be done. I actually think that I delivered code like this to a customer who has never complained, so maybe it is safe. HTH –Sam

Reply | Quote

(Edited by Leif on 13-Mar-04 16:33. to update link to http://www.bmsltd.ie/)

Excel only started supporting modeless forms with XL2K. But, thanks to a bug in XL97, it actually can be done, but it’s not a good idea. There is a link to ModelessForm.zip on Stephen Bullen’s Excel Page that shows how it can be done. I actually think that I delivered code like this to a customer who has never complained, so maybe it is safe. HTH –Sam

Reply | Quote