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

**DocAElstein** · 06-01-2020, 02:42 PM

Hi
_....When I delete the content of last cell in the list, i.e., A5 (RAHIM), the corresponding sheet "RAHIM" remains visible. The macro to hide the sheet works for all cells, except the last one in the list….
I think I see the problem: In the scenario that the last used cell is empty, then the coding gets the last row calculation, Lr1 , wrong: The value comes out one less than we want.

This is one way to over come this, ( there may be better ways… ):
We need
_ an extra event macro which records the cell range which was last used in column 1 ( Column A )

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  '    https://excelfox.com/forum/showthread.php/2501-VBA-for-dynamic-sheets-name-dynamic-link-hide-sheets-based-on-a-cell-Value?p=13456&viewfull=1#post13456
    If Target.Column = 1 And Not IsArray(Target.Value) Then ' we are in column A ,  And  we selected one cell
     Set LRng = Target
    Else

    End If
End Sub

_ This cell range is recorded in a “global” variable, that is to say one which goes outside of any procedure , towards the top of the code module:

Code:

Option Explicit
Dim LRng As Range

_ Finally, a check is made within Private Sub Worksheet_Change(ByVal Target As Range) which attempts to check for the scenario, and correct the calculated value of Lr1

Code:

    If Not LRng Is Nothing And Target.Value = "" And LRng.Row = Lr1 + 1 Then Let Lr1 = Lr1 + 1
Dim Rng As Range

I have not checked this solution thoroughly. I leave that to you.

File: ( with modified macros )
DynamicWorksheetNamesLinkHideBasedOnCellValue2.xlsm : https://app.box.com/s/dgklq01wbxrvxxurjd8wccr2ghh9vlze

….I want to shift the list from Range A1:A5 to Range C4:C9, that is, the list of name will be started from the cell C4 instead of the cell A1….

I have adjusted the macros for a range in column C starting at row 4:
Macro here:
https://excelfox.com/forum/showthrea...ll=1#post13457
File:
DynamicWorksheetNamesLinkHideBasedOnCellValueC.xlsm : https://app.box.com/s/alo1fbzx8r41jd81rttghikytqzvm0w9

Alan