VBA Auto Cell Rename / Show

ewoeckel

New Member
Joined
Mar 7, 2013
Messages
22
I am trying to have a workbook where it is used for pricing projects. The workbook has the ability to price 15 unique project sites. There is a toggle which allows the user to "enable" or "disable" a site based on the particular project being priced. Directly below is the pretty basic toggle which I have programmed to both show and hide the new sites based on the user input.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If [C47] = "Disable" Then
    Sheets("SITE 2").Visible = False
Else
    Sheets("SITE 2").Visible = True
End If

If [C48] = "Disable" Then
    Sheets("SITE 3").Visible = False
Else
    Sheets("SITE 3").Visible = True
End If


If [C49] = "Disable" Then
    Sheets("SITE 4").Visible = False
Else
    Sheets("SITE 4").Visible = True
End If

' ...... etc through 15 sites


I would like the user to not only be able to enable or disable sites in column "C", but also to have the ability to change the name of that particular tab name, based on the contents of cell "B" (easier to track which site is being priced when multiple locations are enabled). Below is a short version table which reflects my what I am trying to get across.

Column BColumn C
46Enter Site Name / LocationEnable Or Disable
47Site #1Enable
48Site #2Enable
49Site #3Disable
50Site #4Disable

<tbody>
</tbody>


So the user can input a site name like "Chicago North" in [B47], and toggle [C47] to "enable". That would unhide the tab, AND rename that tab to "Chicago North".

I am running into issues once the tab name changes because my original code is not updated with the new tab name, which breaks the workbook. Any help / suggestions would be appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Can you paste your current code?

Instead of referring to the sheets with their names, you can refer to them using their respective numbers ("Worksheets(4)"). I don't know about your code, so I don't know if this would work for you.
 
Upvote 0
Right click this master sheet name and select "View Code". Paste this in:

Code:
Private oldValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)

Dim thisSheet As Worksheet

' Deal with renaming tabs
If Target.Count = 1 And Target.Column = 2 Then
    For Each thisSheet In ThisWorkbook.Worksheets
        If thisSheet.Name = oldValue Then
            thisSheet.Name = Target.Value
            Exit For
        End If
    Next thisSheet
End If

' Deal with sheet show/hide
If Target.Count = 1 And Target.Column = 3 Then
    For Each thisSheet In ThisWorkbook.Worksheets
        If thisSheet.Name = Cells(Target.Row, 2).Value Then
            thisSheet.Visible = Target.Value = "Enable"
        End If
    Next thisSheet
End If

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 And Target.Column = 2 Then oldValue = Target.Value

End Sub

This will handle changing the name of the site and also the show/hide of the appropriate sheet.

WBD
 
Upvote 0
Can you paste your current code?

Instead of referring to the sheets with their names, you can refer to them using their respective numbers ("Worksheets(4)"). I don't know about your code, so I don't know if this would work for you.


This is the type of code I am working with, but I am very novice with VBA and coding.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SITE2 As String
SITE2 = Range("B47")




If [C47] = "Disable" Then
    Sheets(SITE2).Visible = False
    Sheets(SITE2).Name = Range("B47")
    SITE2 = Range("B47")
Else
    Sheets(SITE2).Visible = True
    Sheets(SITE2).Name = Range("B47")
    SITE2 = Range("B47")
End If

End Sub
 
Last edited:
Upvote 0
The thing is, when you want to change sheet names you'll have to link cells to tab numbers. So if I were to for instance change cell B5 to "Tim_Tastic_" only say tab 4 would change because cell B5 rules over that tab.

in that case
Code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]Dim SITE2 As String
SITE2 = Range("B5")




If [B5] = "Disable" Then
    Sheets(4).Visible = False
    Sheets(4).Name = SITE2
    
Else
    Sheets(4).Visible = True
    Sheets(4).Name = SITE2    End If </pre>[COLOR=#333333]End Sub[/COLOR]

Did you check WBD's code?
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,737
Members
448,295
Latest member
Uzair Tahir Khan

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