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 :) :) )
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
Try using the "Code Name" instead of "Sheet's name"
Something like:
Sheet1.Activate

The code name won't change when you change the sheet's name.

Example (Sheets Data should have 2 column like this):

Book2
AB
1DSOBSheet1
2ABCDSheet2
3GHTSheet3
QWE


so let's say you pick "ABCD" in the combobox, then your code should find "ABCD" in sheet "Data" then get the code name (i.e sheet2) then activate sheet2. And when you change "ABCD" to "XYZ" the code will open the same sheet.


Reference:
excel-vba-sheet-names.htm
"Sheets Code Name
This is the method used by savvy VBA coders. Each Sheet in a Workbook is given a unique CodeName that does not change even when that sheet is moved, renamed or other sheets are added. Each sheets CodeName can only be seen by going into the Visual Basic Editor (Tools>Macro>Visual Basic Editor Alt+F11) and then displaying the Project Explorer (View>Project Explorer Ctl+R)"
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,443
Office Version
  1. 2013
Platform
  1. Windows
You said:
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.
And:
Now when i change that name DODB into BDOD in the cel Data A1 my combolist changes also.
This sounds like you know how to update the Combobox names already automatically.
Tell me how this happens.
 

Dedeke

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

Ok , i read the article and do understand what they mean.
And if i want to do so how do i tell my combobox to display my colom A but choose colum B ?

i'm not a vba specialist. i'm already happy starting to understand what is hapening in some code when i see it happens. :)
My combobox is on a form calls "search"

VBA Code:
Private Sub UserForm_Initialize()
Cbo_naam.List = Sheets("Data").Range("b2:b70" & [b1].End(xlDown).Row).Value

End Sub
The moment i change the name "Data" into "sheet02" wich is also the effective code name ( i also tried dutch Blad02)in this part of the code nothing is shown in the combobox.

This is the complete code from the part combobox.

VBA Code:
Private Sub Cbo_naam_AfterUpdate()
If Cbo_naam.Value = "" Then Exit Sub
If Cbo_naam.Value = "Kms" Then
    paswoord.Show
ElseIf Cbo_naam.Value = "Ritten" Then
    RittenPsw.Show
    
Else
    Sheets(Cbo_naam.Value).Visible = True
    Sheets(Cbo_naam.Value).Select
End If
Cbo_naam = ""
Me.Hide
End Sub

Private Sub cboCloseSearch_Click()
Unload Me
End Sub

Private Sub Show_Me_Click()
If Cbo_naam.Value = "" Then Exit Sub
If Cbo_naam.Value = "Kms" Then
    paswoord.Show
ElseIf Cbo_naam.Value = "Ritten" Then
    RittenPsw.Show
    
Else
    Sheets(Cbo_naam.Value).Visible = True
    Sheets(Cbo_naam.Value).Select
End If
Cbo_naam = ""
Me.Hide
End Sub

Private Sub UserForm_Activate()
 Top = 240
  Left = 30
  
End Sub

Private Sub UserForm_Initialize()
Cbo_naam.List = Sheets("Data").Range("b2:b70" & [b1].End(xlDown).Row).Value

End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Could you upload a sample workbook (without sensitive data) to a free site such as dropbox.com or google drive & then share the link here? It will make it easier to test and find a solution.
The moment i change the name "Data" into "sheet02"
Why do you change the name "Data"? Sheets "Data" has the list of the Tab Names, right? And the names in that list that might be changed.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,443
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I would suggest using sheet double click event.
Double click on a sheet name in column A
A inputbox popsup asking for the new sheet name
You enter the new sheet name then the name is changed and the combobox is updated
then we put a script in the Combobox like this:

I could provide the complete code for the double click event script if your interested.
But I asked you a few questions earlier and you never answered back so you may already have another way you would prefer.

VBA Code:
Private Sub ComboBox1_Change()
'Modified  1/12/2021  3:57:30 PM  EST
On Error GoTo M
Dim ans As String
ans = ComboBox1.Value
Sheets(ans).Activate
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "Their is no sheet named" & vbNewLine & ans
End Sub
 

Dedeke

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

I'm very sorry my bad. I misplaced the question and thought it was coming from Akuini.
So in relay a disurved answer to your question.
I asume you can see the complete history of this thread. So in the code above it's the line wich initialize the combo box

VBA Code:
Private Sub UserForm_Initialize()
Cbo_naam.List = Sheets("Data").Range("b2:b70" & [b1].End(xlDown).Row).Value

I also have put a link to my G-drive with an example file.
If needed i can place it again.

Again my appologies for not answering your question in first place
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,443
Office Version
  1. 2013
Platform
  1. Windows
@ My answer is this,

I'm very sorry my bad. I misplaced the question and thought it was coming from Akuini.
So in relay a disurved answer to your question.
I asume you can see the complete history of this thread. So in the code above it's the line wich initialize the combo box

VBA Code:
Private Sub UserForm_Initialize()
Cbo_naam.List = Sheets("Data").Range("b2:b70" & [b1].End(xlDown).Row).Value

I also have put a link to my G-drive with an example file.
If needed i can place it again.

Again my appologies for not answering your question in first place
If the list of sheet names are on a sheet. And the combobox is on a sheet.
What are you using the UserForm for?

You said earlier:
my combobox on the sheet "Start
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,752
Messages
5,626,661
Members
416,199
Latest member
Gautamsunil

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