make button invisible

[KateB]

Hi, please excuse my lack of knowledge around this, but if anyone can help it
will really be appreciated.

I have a button on a worksheet that when 'pressed' will e-mail the content
of certain cells to me. This works really well! (I found the instructions
via a link in another thread!) However, I don't want this button to be
visible unless cell E49 has a value of 37 (it is the sum of other cells).
The worksheet is called Waste quiz. Can anyone help? I know there is a
difference between command buttons and form buttons - I believe this is a
form button.

Any help is gratefully received.

Regards,
Kate

 

[Jacob Skaria]

Right click the sheet tab; and view Code..Paste the below code and try. I
have named the button as CommandButton1. Change to suit..

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("E49") = 37 Then
Me.CommandButton1.Visible = True
Else
Me.CommandButton1.Visible = False
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes

 

[KateB]

Thanks Jacob, but its not a command button, its a form button so I don't know
if that makes a difference to the script? I've been trying this:

Private Sub Worksheet_Change (ByVal Target As Range)
If Not Intersect(Me.Range("E49"), Target) Is Nothing Then
If Target.Value = 37 Then
Buttons("button 8").Visible = True
Else
Buttons("button 8").Visible = False
End If
End If
End Sub

which I found on another thread, but it doesn't do anything. Can anyone
suggest changes I can make to it to try to get it to respond?

Kate

 

[john]

Kate,
I ran you code & worked ok for me. I am wondering if you have EnableEvents
set False.

Try running this bit of code & then test your code again

Sub atest()
Application.EnableEvents = True
End Sub

 

[KateB]

I ran that and still no difference. I tried swapping the e-mail script to a
command button but couldn't make it 'click' so maybe there is something wrong
with my settings?

Kate

 

[john]

Strange, I took it that you are using the Worksheet_Calculate event as
suggested to you? Your sample code showed the Worksheet_Change event which
will not work for you as the result you are testing is in a calculated cell.
If you have now added a command button and cannot get it ‘click’ that sounds
like you are still in design mode & you need to turn that off. You can find
this option on the controls toolbar.

Paste code below behind the appropriate sheet – It shows option for either
forms Button or Control Toolbox Commandbutton delete as required.

Private Sub Worksheet_Calculate()
'delete button type not used

If Me.Range("E49").Value = 37 Then

'forms button
Me.Buttons("button 8").Visible = True

'or controls commandbutton
Me.CommandButton1.Visible = True

Else
'forms button
Me.Buttons("button 8").Visible = False

'or controls commandbutton
Me.CommandButton1.Visible = False

End If

 

[KateB]

Brilliant! Works exactly how I wanted it to. Many thanks!

Kate.