VBA for dynamic sheets name + dynamic link + hide sheets based on a cell Value

[Anshu]

Question 2

I have a workbook which contain ONLY 1 sheet named as "Master Sheet". On Master Sheet there is a list of 5 names, as
A1=ANUJ
A2=RITA
A3=MUKESH
A4=RAM
A5=RAHIM

(Actually I have total 400 names, but for easiness I have taken only 5)

Now, my requiremet is:
1. I want to create 5 tabs (Sheets) on the basis of these 5 names. (Now the workbook will have 6 tabs, including Master Sheet)

2. Tab name should be dynamic. Means, if I change the value of A1 from 'ANUJ' to 'SHONA' on Master Sheet, then the "ANUJ" tab should automatically be renamed as "SHONA", without going to the tab "ANUJ". (Because going to 400 tabs to make it "ActiveSheet" is very much time consuming)

3. Each tab should be linked with the corresponding list name, that is, If I click on "A1" (ANUJ) on Master Sheet, the tab (Sheet) "ANUJ" will be opened.

4. Also, this linking should be dynamic, that is, if I change the name "ANUJ" as "SHONA" on Master Sheet, the tab "ANUJ" will be renamed as "SHONA" and remain linked with Cell A1 of Master Sheet.

5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.


In simple lines,
I want a workbook which contain
- dynamic sheet name (without going to the sheet (VBA code without "ActiveSheet")
- dynamic hyperlink
- auto hide feature for any sheet

Thanks in advance!


I'm a counsellor and preparing a Record of all students. If someone helps, I'll really be grateful. Please help if possible.

---

Cross Posts:
2020-05-29 23:28:19 PRAVEEN https://excelribbon.tips.net/T007993...Tab_Names.html
https://www.mrexcel.com/board/thread...names.1135624/
https://ask.wellsr.com/1457/dynamic-sheets-name-dynamic-link-hide-sheets https://ask.wellsr.com/1457/dynamic-...nk-hide-sheets

Hello Anshu ( PRAVEEN )
Welcome to excelfox.
See if this helps get you started: https://excelfox.com/forum/showthrea...ll=1#post13443

I can look at the further requirements tomorrow

( If you post the same question elsewhere, and/ or get a solution in the meantime, then please tell us where, and post the solution.
Please always tell everybody where you duplicate a question.
Thanks,
Alan )

Question 2


Hi

Macros and File Solution Here: https://excelfox.com/forum/showthrea...ll=1#post13447


Explantion:
_ 1. create 5 tabs (WorkSheets) on the basis of names list
Sub AddWorksheetsfromListOfNames2() answers your first question. ( It works similarly to the other macro I did for your, Sub AddWorksheetsfromListOfNames()

_2. dynamicÂ… Means, if I change the value of A1 from 'ANUJ' to 'SHONA' on Master Sheet, then the "ANUJ" tab should automatically be renamed as "SHONA Â….
This is basically answered by my previous macro written for you, Private Sub Worksheet_Change(ByVal Target As Range) ( https://excelfox.com/forum/showthrea...ll=1#post13444 )

_ 3. – _5.
Some of this is new stuff for me, so my solution may be far from the best
_3. Each tab should be linked with the corresponding list name, that is, If I click on "A1" (ANUJ) on Master Sheet, the tab (Sheet) "ANUJ" will be opened.
Sub AddHypolinkToWorksheet() seems to do this.

_4. Also, this linking should be dynamic, that is, if I change the name "ANUJ" as "SHONA" on Master Sheet, the tab "ANUJ" will be renamed as "SHONA" and remain linked with Cell A1 of Master Sheet.
This can be achieved by adding a code line within Private Sub Worksheet_Change(ByVal Target As Range) , to re run Sub AddHypolinkToWorksheet() seems to do this.

'
Call AddHypolinkToWorksheet

This may not be the most efficient way to do this.


_5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.
To achieve this, a modification in Private Sub Worksheet_Change(ByVal Target As Range) seems to work:
Changing .._

         Let ThisWorkbook.Worksheets.Item(Rw + 1).Name = Target.Value ' In the list, each row number corresponds to one less than the item number of our worksheets made from that list

_.. to

        If Target.Value = "" Then '  5. If I delete the content of A1 (ANUJ), i.e, if cell A1 is blank, the corresponding sheet "ANUJ" should hide automatically.
         ThisWorkbook.Worksheets.Item(Rw + 1).Visible = False
        Exit Sub
        Else
         ThisWorkbook.Worksheets.Item(Rw + 1).Visible = True
         Let ThisWorkbook.Worksheets.Item(Rw + 1).Name = Target.Value ' In the list, each row number corresponds to one less than the item number of our worksheets made from that list
        End If
    Else

_._________________

Here are the macros and workbook which also contains the macros: https://excelfox.com/forum/showthrea...ll=1#post13447

Alan

P.S.If this solution is satisfactory, It is customary to inform at the other forums where you have cross posted, of your solution, so as to avoid others wasting time.

---

SOLVED!
Excellent Sir, Very Very Thanks to you!!