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

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

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).

--
Regards,
Tom Ogilvy


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

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.

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

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, you could always give the users a "Close Form" button
which Hides the Excel form only. When they want it back they
press an "Show Form" button which unhides the form. This
takes only a fraction of a second to redisplay as you do not
reinitialize the form.

To see if this is what you want add a form to your project and
have it open when you click a button. Add a button to that
new form with the code MyNewForm.Hide Add some code to
the initialization method of this form that takes AGES to run.
Now run your main form and show this for then close it and display
it again. The second time it is faster.

At least this means you get your current project and the change the
users want without the uncertainty which Harald refers to.

Chrissy.
P.S, I had a look at FormFun and I like it but think that VB is a safer
solution - the guys I work for want Excel used and will not allow me to
use VB. I have the same issue about a form needing to be "put away".

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

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.


.