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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,958
Office Version
  1. 365
Platform
  1. Windows
Here's my understanding:
  1. Only sheet "Start" is visble.
  2. Admin can change the sheet names from sheet "Start" without unhiding other sheets.
Here's one method:
1. Put a command button in sheet "Start" to open a userform with a password.
2. Admin can change a sheet name via a combobox & textbox. The combobox has the list of the sheet names to choose from & he can type a new sheet name in the textbox. Then the code will change the sheet name to a new name.

Let me know if you're interested in this method.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Here's my understanding:
  1. Only sheet "Start" is visble.
  2. Admin can change the sheet names from sheet "Start" without unhiding other sheets.
Here's one method:
1. Put a command button in sheet "Start" to open a userform with a password.
2. Admin can change a sheet name via a combobox & textbox. The combobox has the list of the sheet names to choose from & he can type a new sheet name in the textbox. Then the code will change the sheet name to a new name.

Let me know if you're interested in this method.
@Akuini,

Yes off course, the less actions, the admin has to take the better for me.
The less they see, the less they can mees arround :)
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,958
Office Version
  1. 365
Platform
  1. Windows
OK, try this:
1. Hit OPEN USERFORM
2. Enter password "asd", you can change the password in Module1 in Sub openUserForm1().
3. Pick a sheet name from the combobox.
4. Enter a new name in the textbox
5. Hit RENAME

The file:
Dedeke_-Tabnames_2.xlsm

Note: Now in sheet Data you only need to have the list in col A, so col B is no longer needed
 
Last edited:

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
OK, try this:
1. Hit OPEN USERFORM
2. Enter password "asd", you can change the password in Module1 in Sub openUserForm1().
3. Pick a sheet name from the combobox.
4. Enter a new name in the textbox
5. Hit RENAME

The file:
Dedeke_-Tabnames_2.xlsm

Note: Now in sheet Data you only need to have the list in col A, so col B is no longer needed
Akuini,
Updated my actual file.
Works all fine excempt one little thing, i think it's an easy one for you.
So on your form when clicking the exit button it just closes the form.

VBA Code:
Private Sub CommandButton2_Click()
Unload Me
End Sub

I need the sheet "Data" also updated/Saved while clicking the exit and closing the form.
Why: On al the other sheets there is also 1 cell with a formula =Data!XXX
So on "exit" everything is like i said OK. I can immediatly choose the renamed sheet. But that one cell is not updated unless the Sheet "Data" is saved one time.

I will also try to find the solution my own. Hoping i've learnt something;; ;)
 

Akuini

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

ADVERTISEMENT

I need the sheet "Data" also updated/Saved while clicking the exit and closing the form.
Put this code on the Userform module.
It will save the workbook when you exit the Userform.
VBA Code:
Private Sub UserForm_Terminate()
ThisWorkbook.Save
End Sub
 
Solution

Dedeke

New Member
Joined
Dec 1, 2020
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Put this code on the Userform module.
It will save the workbook when you exit the Userform.
VBA Code:
Private Sub UserForm_Terminate()
ThisWorkbook.Save
End Sub
I found the same.. :)

I think i can close this one now. If i come up with something new , after users input, i know the place to be.

Thx for all the help.
Keep up the good work.. keep it healthy
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,958
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,718
Members
415,922
Latest member
gemmatay88

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