Hide button on condition Excel

Rayne
Rayne used Ask the Experts™
on
I have included the worksheet change code as well. So anytime, the worksheet [some] is hidden, the button [btn-1] is hidden. So if worksheet [some] is unhidden, then we can see button [btn_1] visible.
I have a code that’s working but I want it to be very good and never break – because it’s very very important to hide the button when sheet some is hidden – any suggestions or improvements is greatly appreciated. And I want to make sure I am doing that the right way.

Sub Worksheet_Change(ByVal target As Range)
   
    HideOnCondition
   
End Sub

Sub HideonCondition()

'if worksheet [some] is visible, only then show button [btn_1]
If (Sheet2.Visible = xlSheetVisible) Then
   ActiveSheet.Shapes("btn_1").Visible = True
Else
   ActiveSheet.Shapes("btn_1").Visible = False
End If

     
End Sub
hideButtononCondition.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I would do this

ActiveSheet.Shapes("btn_1").Visible = False

If (Sheet2.Visible = xlSheetVisible) Then
   ActiveSheet.Shapes("btn_1").Visible = True
End If
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I'm not seeing any difference in reliability, but you could do everything in one line (and perhaps eliminate the need for calling a sub):
ActiveSheet.Shapes("btn_1").Visible = (Sheet2.Visible = xlSheetVisible)

The parenthetical expression on the right will be evaluated as True or False, which is exactly the value needed for the .Visible property of your button.
Most Valuable Expert 2012
Top Expert 2012
Commented:
I agree with byundt, re: reliability.  However, why not just use:

ActiveSheet.Shapes("btn_1").Visible = Sheet2.Visible 'evaluates correctly

Open in new window


Also, you might consider doing this on the Worksheet_Activate() or Worksheet_SelectionChange() event as opposed to the Worksheet_Change() event which requires the user to make a change before the code would run.

See attached, using the above suggested code on the SelectionChange() event.  It really depends on what you see the user doing/behaving with your workbook the most relevant event to trigger this code.

Cheers,

Dave
hideButtononCondition-r1.xlsm

Author

Commented:
Thank you Dave, Byundt and Martin,

thank you all for confirming. I justed wanted to make sure I am doing it right. Further, Dave - the workbook selection event was perfect....I cant ask for more :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial