Disable the OK (Unload Me) button

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
I'm attempting to disable the OK (Unload Me) button a userform with the macro shown below. The macro works (well in a way it does), except that I keep getting the following error message each time:

Run time error 438.
Object doesn't support this property or method.

When I click the Debug button, the following line on the macro is highlighted:

Unload Me.Hide

Can someone provide some guidance please?
The macro is below:

Private Sub CommandButton1_Click()
If Range("x29").Value * Range("i29").Value < 90 Then
Unload Me.Hide
If Range("x29").Value * Range("i29").Value > 90 Then
Unload Me
End If
End If
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Use either
Unload Me
or
Me.Hide

Don't use combination

GNaga
 
Upvote 0
Hi,

You can either Unload Me or Me.Hide, they are separate instructions. The first unloads the form from memory, the second simply hides it from view.

What exactly are you trying to do?

(BTW, what happens in your code if the condition =90 rather than being > or < ?)

HTH
 
Upvote 0
"Unload Me" will unload the userform, is that what you want?

"Unload Me.Hide" is invalid code.

"Unload" will remove a userform from memory.
"Hide" merely makes the userform invisible to the user. i.e. it hides it.

So you can't remove a userform and hide it. (though you could hide it, then unload it)

If you are looking to disable the CommandButton you would use a line of code like this:

<pre>
Me.CommandButton1.Enabled = False</pre>

From your code, it looks like you want this to happen when the result is any value other than 90, may I suggest this:

<pre>
Private Sub CommandButton1_Click()

If Range("x29").Value * Range("i29").Value <> 90 Then
Me.CommandButton1.Enabled = False
End If

End Sub</pre>

If this is totally not what you want, repost and explain what you want to happen and we can help you.

HTH
 
Upvote 0
Thanks to you all...gnaga, Richie and Mark. All the suugestions were very helpful, in the end I decided to use:

Private Sub CommandButton1_Click()
If Range("x29").Value * Range("i29").Value < 90 Then
Me.Hide
If Range("x29").Value * Range("i29").Value > 90 Then
Unload Me
End If
End If
End Sub

Thanks again.
 
Upvote 0
That'll only work if the result is <90.

It will never hit the second condition >90 because it has to go past the check:

Is this value is <90 then.

A value that is <90, obviously can't also be >90 at the same time.

For the desired effect, you may want this:

<pre>

Private Sub CommandButton1_Click()
'If value <90, simply hide the form
If Range("x29").Value * Range("i29").Value < 90 Then
Me.Hide
End If

'If value >90 then destroy it
If Range("x29").Value * Range("i29").Value > 90 Then
Unload Me
End If
End Sub </pre>

HTH
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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