Sheetname Changes Depending on Cell Input Data

wedzmer

New Member
Joined
Mar 15, 2015
Messages
24
Hi everyone!

I would like my worksheet name to change depending on the result of a certain cell thus copying that result as the new sheetname.

Example, Sheet 1 would change if cell B1 result would be: "MATHEMATICS".
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
In the worksheet class module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Me.Name = Target.Value
    End If
End Sub
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,072
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
In the worksheet class module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Me.Name = Target.Value
    End If
End Sub
Some cell values containing special characters might spring up an error. Like a slash or......
 

wedzmer

New Member
Joined
Mar 15, 2015
Messages
24
In the worksheet class module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Me.Name = Target.Value
    End If
End Sub

where exactly would I insert that here in my VBA?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub


    On Error Resume Next
    If Not Intersect(Target, Range("b12:B131")) Is Nothing Then
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
    End If
    On Error GoTo 0


End Sub
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,714
where exactly would I insert that here in my VBA?
You could use an If...Then... Else... structure like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    If Target.Address = "$B$1" Then
        Me.Name = Target.Value
    Else
        On Error Resume Next
        If Not Intersect(Target, Range("b12:B131")) Is Nothing Then
            Application.EnableEvents = False
            Target = UCase(Target)
            Application.EnableEvents = True
        End If
        On Error GoTo 0
    End If

End Sub
 

wedzmer

New Member
Joined
Mar 15, 2015
Messages
24
You could use an If...Then... Else... structure like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    If Target.Address = "$B$1" Then
        Me.Name = Target.Value
    Else
        On Error Resume Next
        If Not Intersect(Target, Range("b12:B131")) Is Nothing Then
            Application.EnableEvents = False
            Target = UCase(Target)
            Application.EnableEvents = True
        End If
        On Error GoTo 0
    End If

End Sub

unfortunately it is not working....

Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
On Error GoTo ErrH
If Target.Address = "$a$6" Then
Me.Name = Target
Application.EnableEvents = True
Exit Sub
End If


If Not Intersect(Target, Range("b12:B131")) Is Nothing Then
Target = UCase(Target)
End If
Application.EnableEvents = True
Exit Sub


ErrH:
MsgBox "Invalid Worksheet Name"
Application.EnableEvents = True
End Sub


Sub HideRow()
Dim lLastRow As Long
Dim lCounter As Long


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

With ActiveSheet

.Unprotect
.Shapes("Button 1").Visible = False


lLastRow = .Range("E65536").End(xlUp).Row
For lCounter = 14 To lLastRow
If .Cells(lCounter, "E").Value = 1 Then
.Cells(lCounter, "E").EntireRow.Hidden = True
'Else: .Cells(lCounter, "E").EntireRow.Hidden = False
End If
Next lCounter
.Range("G12").Select


.Shapes("Button 2").Visible = True
End With


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True


End Sub


Sub UnHideRow()


With ActiveSheet
.Unprotect
.Rows.Hidden = False
.Shapes("Button 1").Visible = True
.Shapes("Button 2").Visible = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With


End Sub


Sub CopySheet()
ActiveSheet.Copy After:=ActiveSheet
End Sub

Maybe because the cell target is a dropdown data validation list.. is that it?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,566
Messages
5,625,542
Members
416,116
Latest member
Joemamasuka

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
Top