Minimize and Maximize Forms

[Geoff]

If I want to interact with other applications or other
workbooks when my data input form is used, I know I must
make the form modeless, but how do I install Minimize and
Maximise buttons in the form title bar?
When resuming use of a modeless form, I have found if I
do not close other workbooks or do not activate
the 'parent' workbook then actions on the form which get
data or write data are likely to cause an error. I
presume this is because references to the 'parent'
workbook are incomplete if all my code has been written
using just Sheets(1) notation etc without being prefixed
by the workbook name ie WBook1.Sheets(1). Must I prefix
all my Sheets references to be safe, or, is it possible
to use a mouseclick event (or maximize event) on the form
to activate the wbook automatically?

I would appreciate some help on this as I have found it
difficult to understand the code on Stephen Bullen's
FormFun example despite it being well commented.

T.I.A.

Geoff

 

[Chrissy]

Geoff wrote

If I want to interact with other applications or other
workbooks when my data input form is used, I know I must
make the form modeless, but how do I install Minimize and
Maximise buttons in the form title bar?

Yes, it would be GREAT to have those little gems but I do not
know if it is possible. Hopefully someone else will answer that
with more authority.

You can use the Hide method on the form and then just unhide
it when you want it again. That way the user does not see it but
it is still loaded in memory so does not need reinitializing. This
is separate to the errors you mention and is not really connected.

When resuming use of a modeless form, I have found if I
do not close other workbooks or do not activate
the 'parent' workbook then actions on the form which get
data or write data are likely to cause an error. I
presume this is because references to the 'parent'
workbook are incomplete if all my code has been written
using just Sheets(1) notation etc without being prefixed
by the workbook name ie WBook1.Sheets(1). Must I prefix
all my Sheets references to be safe, or, is it possible
to use a mouseclick event (or maximize event) on the form
to activate the wbook automatically?

If I gave direction to my house as "take the first turn left then
the third one on the right and I live at the fifth one past the
bright blue one" you would only find my place IF you started
at the same place each time. To save you having to start at
the same place each time I could say "go to the KFC on
Main Street and head north". That gives you a starting point.

VBA is the same. Think of it as relative references vs. absolute
references in cell formulas. You need a starting point or it will use
the sheet in the active workbook called "Sheet1" or the range
on the active sheet "Data" with these two lines of code:

Worksheets("Sheet1").......
Range("Data").ClearContents

You will get an error if the active workbook does not contain
a Sheet1 or the active sheet does not contain a named range
Data. This could be better than if Sheet1 and Data did exist
as you would not get an error and you could destroy the
active workbook - well, the data in it anyway.

What you need to do is use the absolute name of the workbook.
I use this:

Dim WB as Workbook
Dim WS as Worksheet

Set WB = ThisWorkbook
Set WS = WB.Worksheets("Sheet1")

Then I just go

WS....
WB.Range("Data")
WS.Range("Data")

depending on what I want to achieve. You can also use constants
in place of Sheet1 and Data and that makes it easy to change all the
code.

Hope this helps.

I would appreciate some help on this as I have found it
difficult to understand the code on Stephen Bullen's
FormFun example despite it being well commented.

What is this FormFun example?

Chrissy

 

[Tom Ogilvy]

http://www.bmsltd.co.uk/Excel/Default.htm

Form Fun can be found on that page. Gives and example of many different
things you can do with a userform using the Windows API. (such as adding
the minimize and maximize buttons).

 

[Harald Staff]

Hi Geoff

If I want to interact with other applications or other
workbooks when my data input form is used, I know I must
make the form modeless, but how do I install Minimize and
Maximise buttons in the form title bar?

Talking of the FormFun sample , it raises some interesting effects/errors when it comes to
states of "modeless". I have no solution to this yet, but it is interesting to look into.

If you have VB6 to play with, a vb addin with a "real" vb form is better suited for
cross-app Windows than an Office Userform

Must I prefix
all my Sheets references to be safe

Yes. You can assume nothing and expect the worst from any user, yourself included. Time
spent on safe code is better use of time than cleaning up disasters caused by intelligent
systems and not-so users ;-)

HTH. Best wishes Harald
Followup to newsgroup only please.

 

[Geoff]

Harald,
Your comments on FormFun and 'real' VB6 forms are most
interesting. I have very recently been given access to
VB6 but have never used it before. I did note however
the automatic install of minimize/maximise buttons on
forms and this may offer a quick solution to my problem
though not without some required learning.

I have just finished what to me is a sizable wbook
project in VBA, in all nearly 6000 lines of code. My aim
was always to work within the constraints of a modal
form. After my thorough testing I have not had any code
based bugs from the users so far. However, the users now
say they can get incoming queries which may demand
accessibility to other applications. This means either
closing the data input form or delaying their responses
hence the request for minimize/maximise functionality and
my dilemma. This is on the one hand, try to comprehend
FormFun's code and adapt it to my project or go on a
quick learning curve and create a VB6 addin with all the
necessary window state precautions etc. The latter means
I would also have to re-think my strategy on menus as for
simplicity I have used autoshapes with a macro attached.
So a VB6 solution is not without a lot of effort but you
suggest a VB6 'real' form is the more robust way. I
accept your experience but why do you feel FormFun is
perhaps less robust and do your misgivings involve
minimize/maximize functions?

Thanks for your thought provoking comments so far.

Geoff

-----Original Message-----
Hi Geoff


Talking of the FormFun sample , it raises some

interesting effects/errors when it comes to

states of "modeless". I have no solution to this yet,

but it is interesting to look into.

If you have VB6 to play with, a vb addin with a "real" vb form is better suited for
cross-app Windows than an Office Userform


Yes. You can assume nothing and expect the worst from

any user, yourself included. Time

spent on safe code is better use of time than cleaning

up disasters caused by intelligent