Update TAB Names from master list

Dedeke

Board Regular
Joined
Dec 1, 2020
Messages
70
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 :) :) )
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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)"
 
Upvote 0
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.
 
Upvote 0
@ 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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
@ 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
 
Upvote 0
@ 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
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
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