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

Oliver Dewar

Board Regular
Joined
Apr 17, 2011
Messages
201
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
The syntax for unloading a userform is
Code:
 UnLoad UserForm1

' not
'UserForm1.UnLoad
 

Oliver Dewar

Board Regular
Joined
Apr 17, 2011
Messages
201
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

Well-known Member
Joined
Mar 16, 2011
Messages
640
= false / = true
 

Oliver Dewar

Board Regular
Joined
Apr 17, 2011
Messages
201
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
Perhaps creating a specific instance would work

Code:
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.
 
Last edited:

Oliver Dewar

Board Regular
Joined
Apr 17, 2011
Messages
201
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:

Code:
Dim modelessForm as Object

Set modelessForm = New modeless1
modelessForm.Show
Where would I put it?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
The last two lines would go where modeless1.Show is currently. The declaration line would go at the top of that sub.
 

Forum statistics

Threads
1,081,693
Messages
5,360,658
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top