Hide sheet: Worksheet name change

pjordy1

New Member
Joined
Oct 24, 2017
Messages
1
Hi All,

I am new to VBA coding and I have the following query:

Background of what I want to achieve:

I have two tabs in this example.
The first tab is called "Summary".
The name for the second tab various based on the value included in cell B2 of that worksheet which is determined by a concatenate function.
I have the same formula in the summary tab in B4.
I have an activeX check box called CheckBox1 in the Summary tab.

I want the second tab to be hidden if the checkbox (CheckBox1) is not checked.

I also have certain columns that are hidden on the summary tab if the Check box is not checked.

VBA code1:
Private Sub CheckBox1_Click()

Application.Goto Reference:="Cell_B3_Hide"
Selection.EntireRow.Hidden = Not CheckBox1.Value

Dim Prod_1 As String
Prod_1 = "Product 1 - "
Set Target = Prod_1 + Range("B4")

ThisWorkbook. Sheets(Target).Visible = CheckBox1.Value

End if

End Sub

VBA code2:
Private Sub CheckBox1_Click()

Application.Goto Reference:="Cell_B3_Hide"
Selection.EntireRow.Hidden = Not CheckBox1.Value

Dim ws As Worksheet
Range("B4").Select
If InStr(ws.Name, Selection) > 0 Then
ws.Visible = CheckBox1.Value

End if

End Sub

I have tried both the codes. For both codes I get a "Run-time error '1004'".

Your assistance in this regard would be greatly appreciated.

Thanks.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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