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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Use either
Unload Me
or
Me.Hide

Don't use combination

GNaga
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
"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
 

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
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.
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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
 

Forum statistics

Threads
1,144,122
Messages
5,722,613
Members
422,447
Latest member
knopp

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
Top