A userform should call itself

S Oberlander

Board Regular
Joined
Nov 25, 2020
Messages
147
Office Version
  1. 365
Platform
  1. Windows
So the user selects something that does not have a valid result. I give an error message "Try again!" and now the userform needs to pop back up.
I've done this in the past, I don't know why now I am getting Run-time error 438: Object does not support this property or method.
VBA Code:
Private Sub Calculate_Click()
Unload Me

    Select Case True
       Case a
          'do a
       Case b
          'do b
       Case c
          'do c
        Case Else
           MsgBox "Try again"
           IQMPossibilities.show 'code stops here
           Exit Sub       
       End Select
    
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Is your provided Calculate_Click event handler in the code module of the userform named IQMPossibilities?
 
Upvote 0
Then you most likely used the name of your userform for another object elsewhere in your code, but within the scope of the userform. VBA simply allows this and then compiles the code without argue, even with Option Explicit on top of every code module, but you will encounter unexpected things during execution.
That said, usually it is not necessary to unload a userform. If memory problems do arise, it is better to remove the form from memory after it has been closed, i.e. it's no longer displayed on the screen. The code that puts the modal userform on the screen is therefore responsible for cleaning up its code. Of course, the code of the userform itself must be adapted to this.

Now back to your code. One would assume that unloading a userform takes place instantly, but that is not the case. This actually happens the moment all the code is finished. In your code that point is never reached since the Exit Sub after the Show method is never been executed, so what you actually accomplish is that the userform is loaded multiple times, depending on the Select Case conditions.
Would recommend the following changes to your code.

Rich (BB code):
Private Sub Calculate_Click()
Me.Hide

    Select Case True
       Case a
          'do a
       Case b
          'do b
       Case c
          'do c
        Case Else
           MsgBox "Try again"
           Me.Show 
           Exit Sub       
       End Select
    
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top