Hi All

I have a macro that makes use of a UserForm for gathering data from the
user. While the userform should always exist on the users' machines this
might not always be the case. Is anybody aware of a way of checking to see
if a UserForm exists in the Personal.xls workbook so I can handle such
instances? If it doesn't then there is obviously a compile error so I have
had a look at the #const and #if... directives but can't figure it out.

Thanks

Andy

Look at Chip Pearson's page on programming the VBE

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data from the
user. While the userform should always exist on the users' machines this
might not always be the case. Is anybody aware of a way of checking to
see
if a UserForm exists in the Personal.xls workbook so I can handle such
instances? If it doesn't then there is obviously a compile error so I
have
had a look at the #const and #if... directives but can't figure it out.

Thanks

Andy

Hi Andy,

This demonstrates it

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Some form name")
On Error GoTo 0
If oUserForm Is Nothing Then
MsgBox "The Userform was not found.", vbExclamation, "Load userforn
by name"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data from the
user. While the userform should always exist on the users' machines this
might not always be the case. Is anybody aware of a way of checking to
see
if a UserForm exists in the Personal.xls workbook so I can handle such
instances? If it doesn't then there is obviously a compile error so I
have
had a look at the #const and #if... directives but can't figure it out.

Thanks

Andy

Just for clarification, this code would be used within a workbook containing
the userform. It would not work as code housed external to the workbook
containing the useform.

Your explanation of what you want is somewhat ambiguous - this is Bob's
interpretation and mine was a differnt interpretation of what you want.


--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Hi Andy,

This demonstrates it

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Some form name")
On Error GoTo 0
If oUserForm Is Nothing Then
MsgBox "The Userform was not found.", vbExclamation, "Load
userforn
by name"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data from the
user. While the userform should always exist on the users' machines
this
might not always be the case. Is anybody aware of a way of checking to
see
if a UserForm exists in the Personal.xls workbook so I can handle such
instances? If it doesn't then there is obviously a compile error so I
have
had a look at the #const and #if... directives but can't figure it out.

Thanks

Andy

There is no way with conditional compilation to determine whether
a userform exists. Why are you assuming that the form should be
in the user's personal.xls workbook? This is bad program design.
Personal.xls is, by definition, where users put the own personal
code. Why don't you put the form in your workbook, so you can be
assured that it exists.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data
from the
user. While the userform should always exist on the users'
machines this
might not always be the case. Is anybody aware of a way of
checking to see
if a UserForm exists in the Personal.xls workbook so I can
handle such
instances? If it doesn't then there is obviously a compile
error so I have
had a look at the #const and #if... directives but can't figure
it out.

Thanks

Andy

Thanks Tom/Bob

I think that Bob's code will be sufficient for my needs this time but the
link to Chip's website will be useful in the future and answers a few
questions that have rattled around at the back of my brain for a while!

Apologies for the ambiguous question - it can be difficult to phrase it
clearly when you don't know what all the options are.

Thanks very much

Andy

"Tom Ogilvy" wrote:

Just for clarification, this code would be used within a workbook containing
the userform. It would not work as code housed external to the workbook
containing the useform.

Your explanation of what you want is somewhat ambiguous - this is Bob's
interpretation and mine was a differnt interpretation of what you want.


--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Hi Andy,

This demonstrates it

Dim oUserForm As Object
On Error Resume Next
Set oUserForm = UserForms.Add("Some form name")
On Error GoTo 0
If oUserForm Is Nothing Then
MsgBox "The Userform was not found.", vbExclamation, "Load
userforn
by name"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data from the
user. While the userform should always exist on the users' machines
this
might not always be the case. Is anybody aware of a way of checking to
see
if a UserForm exists in the Personal.xls workbook so I can handle such
instances? If it doesn't then there is obviously a compile error so I
have
had a look at the #const and #if... directives but can't figure it out.

Thanks

Andy

Hi Chip

Thanks for the reply, the macro needs to be run against different (new)
workbooks every time so there is not one workbook that the users will always
use. However, I take your point that importing modules into people's
personal.xls workbooks is bad program design. Would I therefore be better to
have a workbook with the sole purpose of containing common macros that opens
on startup?

Thanks
Andy

"Chip Pearson" wrote:

There is no way with conditional compilation to determine whether
a userform exists. Why are you assuming that the form should be
in the user's personal.xls workbook? This is bad program design.
Personal.xls is, by definition, where users put the own personal
code. Why don't you put the form in your workbook, so you can be
assured that it exists.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering data
from the
user. While the userform should always exist on the users'
machines this
might not always be the case. Is anybody aware of a way of
checking to see
if a UserForm exists in the Personal.xls workbook so I can
handle such
instances? If it doesn't then there is obviously a compile
error so I have
had a look at the #const and #if... directives but can't figure
it out.

Thanks

Andy

You could put your code and forms in an add-in and distribute
that to your users. I would never use anything that did anything
to my personal.xls file.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi Chip

Thanks for the reply, the macro needs to be run against
different (new)
workbooks every time so there is not one workbook that the
users will always
use. However, I take your point that importing modules into
people's
personal.xls workbooks is bad program design. Would I
therefore be better to
have a workbook with the sole purpose of containing common
macros that opens
on startup?

Thanks
Andy

"Chip Pearson" wrote:

There is no way with conditional compilation to determine
whether
a userform exists. Why are you assuming that the form should
be
in the user's personal.xls workbook? This is bad program
design.
Personal.xls is, by definition, where users put the own
personal
code. Why don't you put the form in your workbook, so you can
be
assured that it exists.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering
data
from the
user. While the userform should always exist on the users'
machines this
might not always be the case. Is anybody aware of a way of
checking to see
if a UserForm exists in the Personal.xls workbook so I can
handle such
instances? If it doesn't then there is obviously a compile
error so I have
had a look at the #const and #if... directives but can't
figure
it out.

Thanks

Andy

Thanks for your advice Chip.

Andy

"Chip Pearson" wrote:

You could put your code and forms in an add-in and distribute
that to your users. I would never use anything that did anything
to my personal.xls file.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi Chip

Thanks for the reply, the macro needs to be run against
different (new)
workbooks every time so there is not one workbook that the
users will always
use. However, I take your point that importing modules into
people's
personal.xls workbooks is bad program design. Would I
therefore be better to
have a workbook with the sole purpose of containing common
macros that opens
on startup?

Thanks
Andy

"Chip Pearson" wrote:

There is no way with conditional compilation to determine
whether
a userform exists. Why are you assuming that the form should
be
in the user's personal.xls workbook? This is bad program
design.
Personal.xls is, by definition, where users put the own
personal
code. Why don't you put the form in your workbook, so you can
be
assured that it exists.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Andy" wrote in message
...
Hi All

I have a macro that makes use of a UserForm for gathering
data
from the
user. While the userform should always exist on the users'
machines this
might not always be the case. Is anybody aware of a way of
checking to see
if a UserForm exists in the Personal.xls workbook so I can
handle such
instances? If it doesn't then there is obviously a compile
error so I have
had a look at the #const and #if... directives but can't
figure
it out.

Thanks

Andy