macro to control visible tabs

[Jed]

Hi,

I have a spreadsheet which calculates the share of support hours based on the number of tenants in a property. I'm trying to reconfigure the spreadsheet so that the inputting for each individual is on a separate tab. Currently, I input the number of tenants. Using a conditional format, the relevant number of input boxes for tenant names appear beneath it. Basically, what I want is a macro to:
1. only display as many tabs as needed e.g. 5 tenants, 5 (named) tabs shown. (this would not include the basic info, overview and costs tab as they will always be displayed.
2. change the name of each tab to that of the individual input on the basic info screen

I have to warn you that although I am fairly advanced with creating formulae etc in Excel I have a very basic knowledge of macros, so assume very basic knowledge of VBA

thanks in anticipation

[mancubus]

welcome to vbax.

posting 2 workbooks will help us help you.

first is the one you have now.
and second is the one that you manually do what a macro will do.

click go advanced button, scroll down and then click manage attachments button. select the files and click upload.

[Jed]

As requested. I've only uploaded one version but changed the first name tab to correspond with the first name on the basic info tab.

I want a macro that will unhide tabs from tenant 1 to tenant 10, based on the number of tenants (e.g. if the number of tenants is 5, it unhides tenants 1 to 5.

Then, I want it to change the name of the tab, based on the inputting on the basic info screen, so John Smith,s tab will change to his name, rather than say tenant 1.

Hope this clarifies things. Don't worry about anything else you see on there, it is a work in progress, it's just the mechanics of the visible tabs I want to look at

thanks again

[mancubus]

requirement:
hide all worksheets if their names are not listed in cells B13:B22 (depending on the number in cell C10) of Basic info or if their names are not "Basic Info", "Staff levels", "Overview", "Costs".

if this is correct then try this:
(included in the attached file)

Sub hide_sheets_not_in_list()

    Dim wsNames As String
    Dim i As Integer, NumTenant As Integer, StartRow As Integer
    Dim ws As Worksheet
    Dim wsUnhide
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    
    wsNames = "Basic Info|Staff levels|Overview|Costs"
    StartRow = 13
    
    With Worksheets("Basic Info")
        NumTenant = .Range("C10").Value
        For i = StartRow To StartRow + NumTenant - 1
            If .Cells(i, 2) <> "" Then wsNames = wsNames & "|" & .Cells(i, 2).Value
        Next i
    End With

    wsUnhide = Split(wsNames, "|")

    For Each ws In Worksheets
        If UBound(Filter(wsUnhide, ws.Name)) < 0 Then ws.Visible = xlSheetHidden
    Next ws

End Sub

[Jed]

Hi, This doesn't seem to be working. The tabs for tenants 6-10 are still on view, even though the no. of tenants is 5. The tab names aren't changing when I add a name either. Is there a chance there is something blocking it working on the Trust Centre

[mancubus]

hi.
you didn't mention if i understood your requirement correctly, but it seems, i did.

in your file, tab name Tenant 1 is replaced with John Smith.

in my file, based on this, a replaced 4 more tab names with HM star names of my choice as an example.

i dowloaded the file that i uploaded.

and when i run hide_sheets_not_in_list, all sheets but listed below are invisible.
Basic Info, John Smith, Joey Di Maio, Ronnie James Dio, James Hatfield, Dave Mustaine, Staff levels, Overview

that is below sheets are hidden:
Tenant 6, Tenant 7, Tenant 8, Tenant 9, Tenant 10

[Jed]

sorry for the misunderstanding. I've run the macro and it did work. Is it possible to get it to run automatically, with the inputting

[mancubus]

sure.

and inputting what? and where?
or what event will fire the macro?

[Jed]

When you open the workbook, I don't mind if tenants 1-10 are displayed. As soon as the number of tenants is input and 'enter' pressed, I want it to hide the remainder (5 input, only tenants 1-5 remain visible, 6-10 are immediately hidden).

I also want the names for each tab to change as you input and press 'enter' on the keyboard.

Thanks for your continued patience, I know it can't be easy with noobs like me!

[mancubus]

and where? which cell?
A1?
CQ99847?

show me the map...


ps: i know what you mean, but also want you to define your requirement clearly when posting in help forums...

[mancubus]

copy below code to worksheet's (Basic Info) code module.
(right click the tab name, click "view code")

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsNames As String
    Dim i As Integer, NumTenant As Integer, StartRow As Integer
    Dim ws As Worksheet
    Dim wsUnhide
    
    If Target.Address <> "$C$10" Then Exit Sub
    'macro is triggered only when a change is made in cell C10.
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
     
    For Each ws In Worksheets
        ws.Visible = xlSheetVisible
    Next ws
     
    wsNames = "Basic Info|Staff levels|Overview|Costs"
    StartRow = 13
     
    NumTenant = Target.Value
    For i = StartRow To StartRow + NumTenant - 1
        If Cells(i, 2) <> "" Then wsNames = wsNames & "|" & Cells(i, 2).Value
    Next i
     
    wsUnhide = Split(wsNames, "|")
     
    For Each ws In Worksheets
        If UBound(Filter(wsUnhide, ws.Name)) < 0 Then ws.Visible = xlSheetHidden
    Next ws
End Sub

[Jed]

hi, see what you mean. Cell c10 should control the number of tabs visible (between tenant 1 - tenant 10). The other tabs (basic info, costs, staff levels, overview) have no macro control on them. I've tried the code above and it worked if I reduced the number in c10 but not if I increased it. The tabs remained hidden.

Also, the tabs aren't changing automatically when I input in the name input cells (b13-b22). If at all possible I'd like the code to react to any changes I make, whether I have to change the names of individuals in the property, or the property size. The purpose of the spreadsheet is to aid social workers who assess people with a learning disability who live in shared houses. We have about 700 properties in our geographic region and need to be able to create a workbook for each property, so it needs to be that flexible.

Hope this helps a bit

[mancubus]

As soon as the number of tenants is input and 'enter' pressed, I want it to hide the remainder.

the code in post 11 exactly does this. increasing or decreasing the number does not matter.

you can modify it to solve your similar additional requirements.