Is it possible to use the show command for a userform, when the name of the
userform is stored as variable. Basically, I have a userform that leads off
to one of 10 other 'subforms' depending on the user's selection from a
dropdown. I have code that relies on If statements, etc, but it would be
easier when adding future 'sub-forms' if I could store all the userform names
in an array and then select the appropriate name from there.

I have all the array code, but cannot work out the Show command.

Here's a sub to do it, just pass the userform name

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = UserForms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & " was not
found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, "Load
userforn by name"
End Select
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Is it possible to use the show command for a userform, when the name of
the
userform is stored as variable. Basically, I have a userform that leads
off
to one of 10 other 'subforms' depending on the user's selection from a
dropdown. I have code that relies on If statements, etc, but it would be
easier when adding future 'sub-forms' if I could store all the userform
names
in an array and then select the appropriate name from there.

I have all the array code, but cannot work out the Show command.

Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected to the
simple line (in this example where 'MarketAnalysis' is the name of the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked twice for the
main userform to be closed.

If I use the Hide command before Unload I get the error message "Must close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to work. Can
you think of any possible cause?

"Bob Phillips" wrote:

Here's a sub to do it, just pass the userform name

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = UserForms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & " was not
found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, "Load
userforn by name"
End Select
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Is it possible to use the show command for a userform, when the name of
the
userform is stored as variable. Basically, I have a userform that leads
off
to one of 10 other 'subforms' depending on the user's selection from a
dropdown. I have code that relies on If statements, etc, but it would be
easier when adding future 'sub-forms' if I could store all the userform
names
in an array and then select the appropriate name from there.

I have all the array code, but cannot work out the Show command.

Ian,

I have just knocked up a demo as I understand what you are saying, and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected to the
simple line (in this example where 'MarketAnalysis' is the name of the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked twice for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message "Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to work. Can
you think of any possible cause?

Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying, and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected to the
simple line (in this example where 'MarketAnalysis' is the name of the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked twice for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message "Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to work. Can
you think of any possible cause?

Yes, strange isn't it. I'll have to put my thinking head on, there is
obviously a rational explanation. I never encounter the problem, because I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying, and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected to
the
simple line (in this example where 'MarketAnalysis' is the name of the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message "Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to work.
Can
you think of any possible cause?

Hi Bob,

I have been giving this some further thought. Is it possible that, because
the userform is declared in the form of a variable as opposed to using its
actual name, the 'true' name is never entered into memory and hence the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect because that
name has never been used. However, Unload Me would be unaffected as no name
is needed.

Just a thought. Please don't laugh too loud if it's complete nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there is
obviously a rational explanation. I never encounter the problem, because I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying, and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected to
the
simple line (in this example where 'MarketAnalysis' is the name of the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message "Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to work.
Can
you think of any possible cause?

Hi Ian,

My thinking was along the same lines, I wondered if the userform object
shown in this way got allocated some internal name rather than its 'given'
name, so that the second instead of being Userform2 got allocated some name
like {ABCD-1234-..} or something.

However, I tested it like this. On the second form I added another button
with this code

Private Sub CommandButton1_Click()
Dim form

For Each form In VBA.userforms
Debug.Print form.Name
Next form

End Sub

and it showed Userform1, Userform2 in the list.

If I repeated the exercise without the ShowByName, but a simple
Userform.Show in the first form, it showed exactly the same results in the
list.

So at this point, I am still not sure what is going on. I will continue to
delve, so keep checking this thread in case I get an insight.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using its
actual name, the 'true' name is never entered into memory and hence the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect because
that
name has never been used. However, Unload Me would be unaffected as no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there is
obviously a rational explanation. I never encounter the problem, because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected
to
the
simple line (in this example where 'MarketAnalysis' is the name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message
"Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using its
actual name, the 'true' name is never entered into memory and hence the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect because
that
name has never been used. However, Unload Me would be unaffected as no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there is
obviously a rational explanation. I never encounter the problem, because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected
to
the
simple line (in this example where 'MarketAnalysis' is the name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message
"Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

Hi Bob,

Unfortunately, I am still getting the same problem, but will persevere. Are
you able to send me your test file so that I can check I have got everything
OK?


Ian

"Bob Phillips" wrote:

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using its
actual name, the 'true' name is never entered into memory and hence the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect because
that
name has never been used. However, Unload Me would be unaffected as no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there is
obviously a rational explanation. I never encounter the problem, because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are connected
to
the
simple line (in this example where 'MarketAnalysis' is the name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error message
"Must
close
or hide topmost modal form first" when it runs the Hide command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

I must have been dreaming it Ian, as I cannot get it to work again now. Oh
well, I'll plug on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

Unfortunately, I am still getting the same problem, but will persevere.
Are
you able to send me your test file so that I can check I have got
everything
OK?


Ian

"Bob Phillips" wrote:

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by
name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using
its
actual name, the 'true' name is never entered into memory and hence
the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect
because
that
name has never been used. However, Unload Me would be unaffected as
no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete
nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there
is
obviously a rational explanation. I never encounter the problem,
because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are
saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are
connected
to
the
simple line (in this example where 'MarketAnalysis' is the
name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error
message
"Must
close
or hide topmost modal form first" when it runs the Hide
command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

So i am not the only one who returned to work today and the Usrfrm.Show
did not work, has anyone found anything that might substitute, i can
get the forms to laod and work when i have the editor open but as soon
as i close it and try to work it without VB running it Sais Show is a
hidden property

Sub Search()

SearchUF.Show
End Sub


This is what i have used for a simple macro that parses through the
excell spread sheet in two columns and then return the values of the
specific cells into a window. this has worked now for months untill i
returned to work today and not even my backup copies are throwing me
the same stop Point

Sub Search()
SearchUF.Show <----------
End Sub

This worked since december.

That is not the thrust of this thread, this is all about showing a form
using a variable name rather than a form object. That was easily solved but
it threw up a little problem which we solved, but Ian and I have been
discussing the cause.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
ups.com...

Sub Search()

SearchUF.Show
End Sub


This is what i have used for a simple macro that parses through the
excell spread sheet in two columns and then return the values of the
specific cells into a window. this has worked now for months untill i
returned to work today and not even my backup copies are throwing me
the same stop Point

Sub Search()
SearchUF.Show <----------
End Sub

This worked since december.

Hi Bob,

Here is something else which may lead us to the root of the problem.

On the subforms I have dropdown boxes, the values of which are transferred
to worksheets using the code:

[My_PrimaryVac] = MyCombo.PrimaryVac.ListIndex.

However, regardless of my selection in the dropdown,
MyCombo.PrimaryVac.ListIndex always equals -1. Similarly, trying to
initalize MyCombo.PrimaryVac.ListIndex with the value in [My_PrimaryVac] is
not possible, the box always comes up blank.

Does this jog any thoughts?


Ian



"Bob Phillips" wrote:

I must have been dreaming it Ian, as I cannot get it to work again now. Oh
well, I'll plug on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

Unfortunately, I am still getting the same problem, but will persevere.
Are
you able to send me your test file so that I can check I have got
everything
OK?


Ian

"Bob Phillips" wrote:

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by
name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using
its
actual name, the 'true' name is never entered into memory and hence
the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect
because
that
name has never been used. However, Unload Me would be unaffected as
no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete
nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there
is
obviously a rational explanation. I never encounter the problem,
because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are
saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are
connected
to
the
simple line (in this example where 'MarketAnalysis' is the
name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error
message
"Must
close
or hide topmost modal form first" when it runs the Hide
command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

Hi Again,

I have carried on experimenting, and have found that (with my code at least)
the UserForm2_Activate macro is not run when the userform is loaded from the
variable, but does run when it is loaded with the UserForm2.Show command.

Ian

"Bob Phillips" wrote:

I must have been dreaming it Ian, as I cannot get it to work again now. Oh
well, I'll plug on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

Unfortunately, I am still getting the same problem, but will persevere.
Are
you able to send me your test file so that I can check I have got
everything
OK?


Ian

"Bob Phillips" wrote:

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by
name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using
its
actual name, the 'true' name is never entered into memory and hence
the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect
because
that
name has never been used. However, Unload Me would be unaffected as
no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete
nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there
is
obviously a rational explanation. I never encounter the problem,
because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are
saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are
connected
to
the
simple line (in this example where 'MarketAnalysis' is the
name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error
message
"Must
close
or hide topmost modal form first" when it runs the Hide
command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?

My last post was not wholly accurate. The macro was being run, and works
providing Me is used instead of the userform name. I think the learning from
this whole thread s that whenever this approach is used, the userform must be
referred to as Me and not by its name.

Ian



"IanC" wrote:

Hi Again,

I have carried on experimenting, and have found that (with my code at least)
the UserForm2_Activate macro is not run when the userform is loaded from the
variable, but does run when it is loaded with the UserForm2.Show command.

Ian

"Bob Phillips" wrote:

I must have been dreaming it Ian, as I cannot get it to work again now. Oh
well, I'll plug on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

Unfortunately, I am still getting the same problem, but will persevere.
Are
you able to send me your test file so that I can check I have got
everything
OK?


Ian

"Bob Phillips" wrote:

Update.

Try this version of the show module

Public Sub ShowUserFormByName(FormName As String)
Dim oUserForm As Object
On Error GoTo err
Set oUserForm = VBA.userforms.Add(FormName)
oUserForm.Show
Exit Sub
err:
Select Case err.Number
Case 424:
MsgBox "The Userform with the name " & FormName & _
" was not found.", vbExclamation, "Load userforn by
name"
Case Else:
MsgBox err.Number & ": " & err.Description, vbCritical, _
"Load userforn by name"
End Select
End Sub

it seems to work with Me.Hide and Userform2.Hide.

Let me know if it works for you.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Hi Bob,

I have been giving this some further thought. Is it possible that,
because
the userform is declared in the form of a variable as opposed to using
its
actual name, the 'true' name is never entered into memory and hence
the
modal/topmost form is never registered against that name.

This would also explain why Unload [UserFormName] has no effect
because
that
name has never been used. However, Unload Me would be unaffected as
no
name
is needed.

Just a thought. Please don't laugh too loud if it's complete
nonsense.


Ian

"Bob Phillips" wrote:

Yes, strange isn't it. I'll have to put my thinking head on, there
is
obviously a rational explanation. I never encounter the problem,
because
I
always use Unload Me and Me.Hide.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Absolutely perfect. Thanks once again.

It was a relief to know that you could reproduce it, as I was
beginning to
think I had just made a simple mistake.


Ian

"Bob Phillips" wrote:

Ian,

I have just knocked up a demo as I understand what you are
saying,
and
totally unexpected, I was able to reproduce it. How perverse!

I have a solution for you though. don't use

Unload MarketAnalysis

but use

Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"IanC" wrote in message
...
Bob,

The userforms now display perfectly. Many thanks.

However, the Cancel buttons on the subforms, which are
connected
to
the
simple line (in this example where 'MarketAnalysis' is the
name of
the
subform):

Unload MarketAnalysis

no longer works for any of the sub forms and has to be clicked
twice
for
the
main userform to be closed.

If I use the Hide command before Unload I get the error
message
"Must
close
or hide topmost modal form first" when it runs the Hide
command.

It must be connected to this recent code, becasue they used to
work.
Can
you think of any possible cause?