CODEWITHSUNDEEP
vbaexcel
Tom Stone
Tom Stone

Reputation: 39

Setting a Sheet Name to a User Defined Variable, then Referencing it in same Code

Still learning Excel and VBA, so apologies if the code is hideous, or this answer is simple.

I have a set of sheets that are tied to a checkbox; if the box is checked they come out of hidden and the row they are tied to in "SUMMARY" is unhidden, and vice versa. I am trying to make a set of sheets that get renamed to the contents of a specific cell in "Control" sheet, and then operate as though the name in the "Control" sheet has always been their name. I have tried setting variables, establishing ranges, and have failed each time. What do I need to do to have an existing sheet be renamed according to the cell in "Control" I9, and then operate from that point on as though the new name has been the name all along?

Would it be better to have the checkbox simply make a new sheet, format it as I need, and then name it to the user defined variable?

Application.ScreenUpdating = False
    If CheckBox40.Value = True Then
         DIM Name AS String
            Name = Sheets("Control").Cells(9 "I")

        ActiveWorkbook.Unprotect 
        Sheets("ALL 1").Unprotect 
        Sheets("SUMMARY").Unprotect 

        Sheets("ALL 1").Visible = -1
        Worksheets("SUMMARY").Rows("25").EntireRow.Hidden = False
        Worksheets("SUMMARY").Cells(25, 2).Value = "ALL 1"
        Worksheets("SUMMARY").Cells(25, 3).Value = "INTEGRATED AUTOMATION"
        Worksheets("SUMMARY").Cells(25, 4).Value = "='Control'!F26"
        Worksheets("SUMMARY").Cells(25, 5).Value = "='Control'!G26"
        Worksheets("SUMMARY").Cells(25, 6).Value = "='ALL 1'!$H$69"
        Worksheets("SUMMARY").Cells(25, 7).Value = "='ALL 1'!$J$69"
        Worksheets("SUMMARY").Cells(25, 8).Value = "='ALL 1'!$N$69"
        Worksheets("SUMMARY").Cells(25, 9).Value = "='ALL 1'!$P$69"
        Worksheets("SUMMARY").Cells(25, 10).Value = "=SUM(F25:I25)/D25"
        Worksheets("SUMMARY").Cells(25, 11).Value = "=M25/F3"
        Worksheets("SUMMARY").Cells(25, 12).Value = "='ALL 1'!$U$69"
        Worksheets("SUMMARY").Cells(25, 13).Value = "=M25/$K$57"

        ActiveWorkbook.Protect True, True
        Sheets("ALL 1").Protect True, True
        Sheets("SUMMARY").Protect True, True
        Sheets("ALL 1").Name = Name
End If

If CheckBox40.Value = False Then

        ActiveWorkbook.Unprotect 
        Sheets("ALL 1").Unprotect 
        Sheets("SUMMARY").Unprotect 

        Sheets("ALL 1").Visible = 2
        Worksheets("SUMMARY").Rows("25").EntireRow.ClearContents
        Worksheets("SUMMARY").Rows("25").EntireRow.Hidden = True

        ActiveWorkbook.Protect True, True
        Sheets("ALL 1").Protect True, True
        Sheets("SUMMARY").Protect True, True
        Sheets("ALL 1").Name = Name

End If
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 317

Answers (1)

Harassed Dad
Harassed Dad

Reputation: 4704

Use a worksheet variable to hold the renamed sheet and then use that to build your formulas:

DIM myName AS String  'don't use "name" as the name of a variable, it's a property
Dim ws as worksheet  
MyName = Sheets("Control").Cells(9 "I")
set ws = worksheets("All 1")  'point to "All 1"
ws.name = MyName  'rename All 1 to something else
Worksheets("SUMMARY").Cells(25, 12).Value = "='" & ws.name & "'!$U$69"  
'keeps pointing to whatever All 1 is now called

Upvotes: 2

Related Questions