Modeless userform doesn't show if called twice from a modal userform (help!)

[Oliver Dewar]

Hi, first post and first attempt at in depth excel programming (so go easy on me!).

Here's a simplified version of what's going on for me.

I have a modal userform (modal1). If I click on a button the userform is hidden and a modeless userform (modeless1) is shown (modeless1.show False).

This modeless userform allows the user to search for and select a record on another sheet from which a lot of data will be used. Once the record has been selected the user presses a button on modeless1 and the data is copied to the original sheet, modeless1 is unloaded (modeless1.unload), the original sheet is selected and the original modal userform is shown (modal1.show) with the new info in it.

This works brilliantly... but it only works once. (and here's my problem).

If the user realises they selected the wrong record when using modeless1 and tries to go back to modeless1 to correct it by clicking on the same button on modal1... modal1 hides and then modeless1 does not appear (even though VBA shows that macros are still being run).

Is there a way to show modeless1 again? I tried hiding it instead of unloading it but it did exactly the same thing.

All help will be greatly appreciated.

Regards,

Oliver

 

[Glory]

Have you tried using the visible property?

 

[mikerickson]

The syntax for unloading a userform is

 UnLoad UserForm1

' not
'UserForm1.UnLoad

 

[Oliver Dewar]

Ah yes my mistake in my post... I am using unload.userform syntax.

No, I haven't tried the visible. What exactly would I write... modeless1.visible ?

 

[Glory]

= false / = true

 

[Oliver Dewar]

OK...

I tried this code in modal1 in the macro for the button that calls modeless1:

Me.Hide
modeless1.Show False
If modeless1.Visible = False Then
modeless1.Visible = True
End If

and got this error:

Compile Error: Function or interface marked as restricted, or the function uses an Automation type not supported in Visual Basic.

Should I try it somewhere else... or am I doing something wrong?
(Thanks for the lightening responses by the way!)

 

[xenou]

Couldn't you just use modal1 for everything? Wouldn't a refedit control let you select data on a worksheet? Otherwise a quick fix might be a button (on modeless1) that shows modal1. Since this is in response to a user doing something wrong, this button could be called "Cancel".

 

[mikerickson]

Perhaps creating a specific instance would work

Dim modelessForm as Object

Set modelessForm = New modeless1
modelessForm.Show

Or, along Xenou's line, perhaps a multi-page control on modal1, page 0 with modal1's current controls and page 1 with modeless's.

 

[Oliver Dewar]

Thanks for your responses everyone.

I've tucked the refedit and multipage options in my back pocket for the moment, I'll resort to one of them for certain if I can't get my current setup working.

Just wondering with:

Dim modelessForm as Object

Set modelessForm = New modeless1
modelessForm.Show

Where would I put it?

 

[mikerickson]

The last two lines would go where modeless1.Show is currently. The declaration line would go at the top of that sub.