Excel Sheet name Change Macro Code


  I Created 1 Work book with 5 Sheets aprox.

    1 cell named class & Command Button1 in Sheet1

If i click Command Button1 

I Want the Following

Doubt 1.
If class<3, 
The sheet name Sheet5 must be changed to some other name like XYZ, 
Otherwise ,ie, class >= 3 there is no change in sheet name.

Doubt 2.
If class<3
In Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 The Coloum B,C,D must be Hidden, all other Coloums Shown.
Otherwise,ie, class>=3, in all sheets ColoumB,C,D must be shown.

How to do this?
Please send Code for this.
Thanks in advance.



Selected Answer

Your renaming plan isn't sufficiently precise because it doesn't include a schedule what to do after the sheet has been renamed and the condition that triggered its renaming doesn't hold true any longer. The code below fills this gap by returning the sheet to its original name.

Sub Button1_Click()
'    Sheet5.Visible = IIf(Range("Class").Value < 3, xlSheetVeryHidden, xlSheetVisible)
    Const OriginalName As String = "TestSheet"
    Const NewName As String = "Other Name"
    With Sheet5
        If Range("Class").Value < 3 Then
            .Name = NewName
            .Columns("B:D").Hidden = True
            .Name = OriginalName
            .Columns("B:D").Hidden = False
       End If
    End With
End Sub

Please assign the names you want to the constants OriginalName and NewName. You can also change the sheet that the action is taken on. Bear in mind that Sheet5 is a CodeName.



I removed the line "Debug.Print Range("Class").Value" from the version of the above procedure originally published here. That line was a left-over from testing during development and should not remain in the procedure when you use it.
Variatus (rep: 3063) May 2, '18 at 4:27 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login