Advice for my code that hides shapes on worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
This code shown below will hides shapes etc on the worksheet.

VBA Code:
Sub HideButtons()
On Error GoTo ErrHand:
Call TurnOffFeatures ' Stop events etc...

' Define the original sheet
Dim wks As Worksheet
Set wks = Worksheets("INV")

' Define the copied sheet
If wks.Range("G13").Value <> "" Then                    ' if name
    wks.Copy After:=Worksheets(Sheets.count)    ' Copy to last sheets
    Worksheets(Sheets.count).NAME = wks.Range("G13").Value           ' rename sheet
    Dim wksNew As Worksheet
    Set wksNew = Worksheets(wks.Range("G13").Value)     ' set wksNew as new sheet (Selected by name: I would recommend using this)
End If

' Hide all others CommanButtons except "PrintGeneratedSheet"
Dim Shape As Shape
For Each Shape In wksNew.Shapes
    If Shape.NAME <> "PrintGeneratedSheet" Then
        Shape.Visible = False
    End If
Next

ErrHand:
    Call TurnOnFeatures ' Enable events etc...
End Sub

Ive since added CheckBox1 & CheckBox2 & would like them to be shown on the page.

See screenshot of current page where they should be shown.
Please advise how i alter the code to see the 2 Checkboxes.


EaseUS_2024_09_ 2_13_28_18.jpg



I did add this but made no difference & was still hidden

VBA Code:
    If Shape.NAME <> "CheckBox1" Then
        Shape.Visible = True
    End If
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi
untested but see if this update to your code will do what you want

VBA Code:
Sub HideButtons()
    Dim StayVisible     As Variant
    Dim Shape           As Shape
    Dim NewSheetName    As String
    Dim wks             As Worksheet, wksNew      As Worksheet
    
    'list all shape names that stay visible
    StayVisible = Array("PrintGeneratedSheet", "CheckBox1", "CheckBox2")
    
    On Error GoTo ErrHand:
    ' Stop events etc...
    
    ' Define the original sheet
    With ThisWorkbook
        Set wks = .Worksheets("INV")
        
        NewSheetName = wks.Range("G13").Value
        
        'check if name exists
        If Not Evaluate("ISREF('" & NewSheetName & "'!A1)") Then
        
            Call TurnOffFeatures
            
            ' Copy to last sheets
            wks.Copy After:=.Worksheets(Sheets.Count)
            ' rename sheet
            .Worksheets(Sheets.Count).Name = NewSheetName
            
            ' set wksNew as new sheet
            Set wksNew = .Worksheets(NewSheetName)
            
            ' Hide all sahpes not in StayVisible array
            
            For Each Shape In wksNew.Shapes
                Shape.Visible = Not IsError(Application.Match(Shape.Name, StayVisible, 0))
            Next
            
        End If
    End With
    
ErrHand:
    ' Enable events etc...
    Call TurnOnFeatures
End Sub

Dave
 
Upvote 0
Solution

Forum statistics

Threads
1,224,910
Messages
6,181,675
Members
453,061
Latest member
schiefA

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