Update TAB Names from master list

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I already found a lot off treads concerning TAB names but none off them are a solution for my problem.

I try to explain.
I'm having a sheet called Data. On this sheet is a range A1: A20(even more but not important) This range should be the range with the names for the TAB names.
On a second sheet called "Start" is a combobox wich is filled by the same range from Data(A1:A20). So when i select a name over there it brings me straigh to the specific sheet with that name.
So know when i change a name on the DATA sheet, my combobox updates instantly, but the sheet name(TAB name) has to change also otherwise when i select the changed name it can not find the sheet(logical to me)
Ex: Cel DataA1 has the Name: DODB. In my combobox on the sheet "Start" i find this name; I select it and a sheet with the name DODB opens. All fine
Now when i change that name DODB into BDOD in the cel Data A1 my combolist changes also. So when i go there i find the name BDOD. When i select this it can not find the sheet BDOD, logical because the name of the sheet hasn't changed yet.

So i found several solutions to change this name of the sheet ,but is has to change already when ichange the name on the Data sheet.
For info: I don't want the combobox to get the names from all worksheets in the workbook.
User must be limited to choose only from the name list.

Someone who can get me on the right track ??
Many Thx already, (hope this all make sense, even with incorrect English :) :) )
 

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
@ My answer is this

I try to explain some more.

The user only get's to see the sheet "start" when opening file
Simular like the file in link.
On this sheet he has the possibility to type in a name or search the list. Select a name click on show me
and that sheet will open.
When he closes that sheet he returns to the sheet "Start"
It is the administrator who is responsible to change the names on the sheet "data"
Maybe the small example of the file makes it more clear.

The button unhide all sheet is not on the actual file, i've just put it there for try out reasons.


Remark: i noticed that you have to download the file cause (google sheets) does not open the file as it should be.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Actually using sheet's Code Name is harder than I thought. So we're gonna use sheet Index instead, but it only works if the sheets position in the workbook will not change. Let me know if it isn't the case.
The Sheet Index number is the sheet position in the workbook. So in sheet Data the table should look like this (col A is the sheets Index):

Dedeke -Tabnames 1.xlsm
AB
1IndexNaam
26Naam 1
38Naam 2
45Naam 3
53abcd
69Naam 5
75Naam 6
84Reserve 1
97Reserve 2
Data


Here's the code:
VBA Code:
Private Sub Show_Me_Click()
Dim fm
Dim sn As Long
If Cbo_naam.Value = "" Then Exit Sub

fm = Application.Match(Cbo_naam.Value, Sheets("Data").Range("B:B"), 0)
    If IsNumeric(fm) Then
        sn = Sheets("Data").Range("A" & fm)
    Else
        MsgBox "Can't find Tab " & Cbo_naam.Value
        Exit Sub
    End If

If Cbo_naam.Value = "Kms" Then
    paswoord.Show
ElseIf Cbo_naam.Value = "Ritten" Then
    RittenPsw.Show
    
Else
    Worksheets(sn).Visible = True
    Worksheets(sn).Select
End If
Cbo_naam = ""
Me.Hide

End Sub

Note:
1. I removed "Private Sub Cbo_naam_AfterUpdate()" because its code is the same as "Private Sub Show_Me_Click()", so it's redundant.
2. I tried this: open the useform > select sheet Dirk and it did open > close the userform > in sheet Data I changed Dirk to abcd >
open the userform > select "abcd" > sheet Dirk is opened.


The workbook:

to get the sheets Index you can use this macro (I put this code in Module1), see the result in the immediate window:
VBA Code:
Sub getSheetIndex()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    Debug.Print ws.Index & vbTab & ws.Name

Next

End Sub

One question:
If you change a sheet name in the list (in sheet Data) why can't you also actually change the sheet name?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
Your getting a lot of good help here so I will move on to help someone else.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
@My Aswer Is This
The combo is on a userform & is called "Cbo_naam"
The sheet names are on a sheet called Data & are like
Tabnames.xlsm
AB
1Naam
21Naam 1
32Naam 2
43Naam 3
54dirk
65Naam 5
76Naam 6
87Reserve 1
98Reserve 2
Data
In Post 1 the user said:
On a second sheet called "Start" is a combobox
No where in post 1 was there a mention of a UserForm.
 

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Appreciate all the help, gonna find some time this evening to try out the latest solution.
Sorry for not being complete with the given information. As excel user it is not always clear wich detail could be from great importance for the vba specialist.

Keep you all posted after try out...
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
I was going to provide a solution like this:
On your sheet where you have all the sheet names in column A.
You would double click on a cell in column A like the cell has the value "Alpha"
which is a current sheet name.
Upon double clicking on the cell a Inputbox popsup asking what is new name you want to give the sheet. You enter "Bravo" for example and now the sheet named Alpha is now named Bravo.
The name in column A is updated and the combobox is then updated also with the current name.
But I mentioned this earlier I believe and you never mentioned if this solution was one you would like.
Now where the combobox is located I'm confused about first you said on a sheet named Start. But then you seemed to say it was on a Userform. Either way we could work with it. I'm not sure what your using the Userform for. You could double click on a cell with the userform displayed if you opened the userform like this: Userfrom1.show modeless
Modeless lets you work on your sheets with the userform displayed.
 

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@ Akuini and also @ My answer is this.

i found another approach and it's my duty to inform you what i am using now.
From Akuini' s idea to work with 2 colums i came to this.(grabbed on the internet) Working with a colum Previous Name and New Name
This works fine and it is no problem when the order off the ws changes.
But i think it needs a little bit of fine tuning.

To rename my sheets i use following code

VBA Code:
Sub MyRenameSheets()

    Dim lrow As Long
    Dim r As Long
    Dim prevNm As String
    Dim newNm As String
    
    Application.ScreenUpdating = False
    
'   Find last row in column A on Summary sheet with data
    lrow = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
    
    On Error Resume Next
'   Loop through all rows on Summary sheet starting on row 2
    For r = 2 To lrow
'       Capture values
        prevNm = Sheets("Data").Cells(r, "A")
        newNm = Sheets("Data").Cells(r, "B")
'       Rename sheets
        Sheets(prevNm).Name = newNm
    Next r
    On Error GoTo 0
    
    Application.ScreenUpdating = True
        
End Sub

When this one is finished i copy paste New Names to Previous names with this one.


VBA Code:
Sub Copy_Paste_Newnames()
'PURPOSE: How To Paste Values Only With PasteSpecial

    
'Copy A Range of Data
  Worksheets("Data").Range("B2:B12").Copy

'PasteSpecial Values Only
  Worksheets("Data").Range("A2:A12").PasteSpecial Paste:=xlPasteValues

'Clear Clipboard (removes "marching ants" around your original data set)
  Application.CutCopyMode = False
  
End Sub

To run these 2 macro's i created a button on the data sheet which call the 2 others to run.
So far so good.

Fine tuning question.
Is there a possibility to modify the code Copy A range of data in this way:

Copy and paste only a cell with data in it from colom B to the same row in colom A

Ex: A1:A12 have all the prev Names. B1:B12 are empty. I put a new name in B7. I run the first macro wich change the sheet name. The second macro just copy past B7 to to A7 and clears B7 again.

Many Thx
 

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
I was going to provide a solution like this:
On your sheet where you have all the sheet names in column A.
You would double click on a cell in column A like the cell has the value "Alpha"
which is a current sheet name.
Upon double clicking on the cell a Inputbox popsup asking what is new name you want to give the sheet. You enter "Bravo" for example and now the sheet named Alpha is now named Bravo.
The name in column A is updated and the combobox is then updated also with the current name.
But I mentioned this earlier I believe and you never mentioned if this solution was one you would like.
Now where the combobox is located I'm confused about first you said on a sheet named Start. But then you seemed to say it was on a Userform. Either way we could work with it. I'm not sure what your using the Userform for. You could double click on a cell with the userform displayed if you opened the userform like this: Userfrom1.show modeless
Modeless lets you work on your sheets with the userform displayed.
@My Aswer Is This

Just read your post while i was adding my other reply.
It's not so that if a found A solution there is no BETTER solution.
So reading your post I'm still interested in your solution. How els can i find out which one works the best for me.
I also want to learn out off any suggestion in the hope to learn a bit off VBA.
Like I said before, i'm starting to understand a bit off what is doing what in the code when i can see what happens.

And if there are other suggestions to improve what i am trying to do, i can only learn from it.
So i certainly gonna try your suggestion concerning the userform. And if i still can have it.... your proposal :) :)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You don't need 2 macros. Try this one:
1. At first you need to put the list in column A.
2. then say to change "Dirk" to "abcd", just type "abcd" in adjacent column > run the macro.

Dedeke -Tabnames 1.xlsm
AB
1PrevNew
2Naam 1
3Naam 2
4Naam 3
5dirkabcd
6Naam 5
7Naam 6
8Reserve 1
9Reserve 2
Data


The code:
VBA Code:
Sub MyRenameSheets1()

    Dim lrow As Long
    Dim r As Long
    Dim prevNm As String
    Dim newNm As String
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Set ws = Sheets("Data")
'   Find last row in column A on Summary sheet with data
    lrow = Cells(Rows.Count, "B").End(xlUp).Row
    
   
'   Loop through all rows on Summary sheet starting on row 2
    For r = 2 To lrow
'       Capture values
        prevNm = ws.Cells(r, "A")
        newNm = ws.Cells(r, "B")
'       Rename sheets
        If newNm <> Empty Then
            
            On Error Resume Next
            Sheets(prevNm).Name = newNm
                If Err.Number > 0 Then
                    
                    MsgBox "Error found: " & Err.Description
                    
                Else
                    
                    If prevNm <> newNm Then ws.Cells(r, "A") = newNm
                
                End If
            On Error GoTo 0

        End If

    Next r
    ws.Range("B2:B" & lrow).ClearContents
    Application.ScreenUpdating = True
        
End Sub
 

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
You don't need 2 macros. Try this one:
1. At first you need to put the list in column A.
2. then say to change "Dirk" to "abcd", just type "abcd" in adjacent column > run the macro.

Dedeke -Tabnames 1.xlsm
AB
1PrevNew
2Naam 1
3Naam 2
4Naam 3
5dirkabcd
6Naam 5
7Naam 6
8Reserve 1
9Reserve 2
Data


The code:
VBA Code:
Sub MyRenameSheets1()

    Dim lrow As Long
    Dim r As Long
    Dim prevNm As String
    Dim newNm As String
    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
    Set ws = Sheets("Data")
'   Find last row in column A on Summary sheet with data
    lrow = Cells(Rows.Count, "B").End(xlUp).Row
   
  
'   Loop through all rows on Summary sheet starting on row 2
    For r = 2 To lrow
'       Capture values
        prevNm = ws.Cells(r, "A")
        newNm = ws.Cells(r, "B")
'       Rename sheets
        If newNm <> Empty Then
           
            On Error Resume Next
            Sheets(prevNm).Name = newNm
                If Err.Number > 0 Then
                   
                    MsgBox "Error found: " & Err.Description
                   
                Else
                   
                    If prevNm <> newNm Then ws.Cells(r, "A") = newNm
               
                End If
            On Error GoTo 0

        End If

    Next r
    ws.Range("B2:B" & lrow).ClearContents
    Application.ScreenUpdating = True
       
End Sub
Thx for upgrading. Changed the ranges according to my needs in the original file. This works fine.
Could this also work when the sheets are hidden.
In the final file it schould only be the sheet"Start" wich will be visible. In the start sheet i will have a button to unhide the "Data"sheet protected with password for Admin purpose only.
On the data sheet i prepared everything so when Admin needs to change a name he/she finds everything they need completly based on your last code.
Fill in the name - press a button - done. But.... for the moment i have to unhide at least the sheets from wich the name should change.
So could your code also work on hidden sheets. If not, i unhide all sheets together with the data sheet. Doesn't look proffessional but will also work.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,439
Messages
5,642,129
Members
417,257
Latest member
Sarahbw

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