ActiveX Checkbox fails after sheetname change

[Nickdozaj]

Hello - I am using activex checkboxes to hide/unhide cells as well as sheets. I originally wrote the code to update the sheet name based on cell value, but then the check box code would fail. Now I am trying to write the code to continue working no matter what the sheet name is changed to.

What I'd like to happen is:
Check box 1
Unhide 15:15
Someone will enter a contractor name eg. Joe Blow Electric
Sheet 2 (CONTRACTOR 1) updates to Joe Blow Electric
The issue comes when we later change number of contractors from 1 to 3 and the code no longer works because the sheet name has been updated to the contractor's name.

Start Page:

Code setup for checkboxes:

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
Sheets("Contractor 1").Visible = True
[15:15].EntireRow.Hidden = False



Else
Sheets("Contractor 1").Visible = False
[15:15].EntireRow.Hidden = True


End If


End Sub

Private Sub CheckBox2_Click()

If CheckBox2.Value = True Then
Sheets("Contractor 1").Visible = True
Sheets("Contractor 2").Visible = True
[15:16].EntireRow.Hidden = False

Else
Sheets("Contractor 1").Visible = False
Sheets("Contractor 2").Visible = False
[15:16].EntireRow.Hidden = True


End If

End Sub

Private Sub CheckBox3_Click()

If CheckBox3.Value = True Then
Sheets("Contractor 1").Visible = True
Sheets("Contractor 2").Visible = True
Sheets("Contractor 3").Visible = True
[15:17].EntireRow.Hidden = False

Else
Sheets("Contractor 1").Visible = False
Sheets("Contractor 2").Visible = False
Sheets("Contractor 3").Visible = False
[15:17].EntireRow.Hidden = True

End If

End Sub

Private Sub CheckBox4_Click()

If CheckBox4.Value = True Then
Sheets("Contractor 1").Visible = True
Sheets("Contractor 2").Visible = True
Sheets("Contractor 3").Visible = True
Sheets("Contractor 4").Visible = True
[15:18].EntireRow.Hidden = False

Else
Sheets("Contractor 1").Visible = False
Sheets("Contractor 2").Visible = False
Sheets("Contractor 3").Visible = False
Sheets("Contractor 4").Visible = False
[15:18].EntireRow.Hidden = True

End If


End Sub

Private Sub CheckBox5_Click()


If CheckBox5.Value = True Then
Sheets("Contractor 1").Visible = True
Sheets("Contractor 2").Visible = True
Sheets("Contractor 3").Visible = True
Sheets("Contractor 4").Visible = True
Sheets("Contractor 5").Visible = True
[15:19].EntireRow.Hidden = False

Else
Sheets("Contractor 1").Visible = False
Sheets("Contractor 2").Visible = False
Sheets("Contractor 3").Visible = False
Sheets("Contractor 4").Visible = False
Sheets("Contractor 5").Visible = False
[15:19].EntireRow.Hidden = True

End If

End Sub

Private Sub CheckBox6_Click()

If CheckBox6.Value = True Then
Sheets("Contractor 1").Visible = True
Sheets("Contractor 2").Visible = True
Sheets("Contractor 3").Visible = True
Sheets("Contractor 4").Visible = True
Sheets("Contractor 5").Visible = True
Sheets("Contractor 6").Visible = True
[15:20].EntireRow.Hidden = False

Else
Sheets("Contractor 1").Visible = False
Sheets("Contractor 2").Visible = False
Sheets("Contractor 3").Visible = False
Sheets("Contractor 4").Visible = False
Sheets("Contractor 5").Visible = False
Sheets("Contractor 6").Visible = False
[15:20].EntireRow.Hidden = True

End If

End Sub


Here is the code attempt for Sheet2 to update but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Sheets("Start").Range("D15")) Is Nothing Then

If Sheets("Start").Range("D15") = Empty Then
ActiveSheet.Name = "Customer Unspecified"
Else
ActiveSheet.Name = Sheets("start").Range("D15")
End If

End If
End Sub

 

[NoSparks]

If you use sheet code names in your code rather than sheet tab names it wouldn't matter what the sheet gets named, it will always have the same code name.

Unless you are requiring more than one check box to be checked at a time, which doesn't look likely, should consider using option buttons which would automatically only have one selected.

 

[Nickdozaj]

If you use sheet code names in your code rather than sheet tab names it wouldn't matter what the sheet gets named, it will always have the same code name.

Unless you are requiring more than one check box to be checked at a time, which doesn't look likely, should consider using option buttons which would automatically only have one selected.

I tried using sheet codes instead and I still can't get it to work for some reason.

 

[NoSparks]

Show us the code you tried

 

[bobsan42]

I tried using sheet codes instead and I still can't get it to work for some reason.

As NoSparks pointed out, instead of using Sheets("some sheet name") use Sheet1, Sheet2, etc. These stay the same no matter what sheet name you give them.

Here is the code attempt for Sheet2 to update but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Sheets("Start").Range("D15")) Is Nothing Then

If Sheets("Start").Range("D15") = Empty Then
ActiveSheet.Name = "Customer Unspecified"
Else
ActiveSheet.Name = Sheets("start").Range("D15")
End If

End If
End Sub

Which worksheet contains this code? How is it triggered?