hide and unhide across sheets based on cell value

[tubrak]

hello

I have about 60 sheets . it should be hidden when open file except sheet MAIN which search for specific sheet in cell A4 . every sheet contain reference no in cell M28 . if cell A4 match with M28 for any sheet then should just show this sheet which match with this value , if it's not then should be all the sheets are hidden except sheet MAIN and gives me message " it's not match .
with considering when search for another sheet then should hide the old sheet has ever searched and replace of it the new if it's not right

thanks in advance

 

[sijpie]

OK, not difficult, but you have to follow the instructions below very carefully.
First: In Excel, right mouseclick on the tabname of MAIN. Then select 'View Code'
This will open the worksheet module for this sheet in VBA editor. Paste the following code into this window.

'==================  Code for the MAIN sheet code module ================== 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A4").Address Then
        'cell A4 on main has changed. Check for matching sheet to show
        ShowHideSheets
    End If
End Sub

While you are in the VBA editor, go to the menu item Insert and select 'Module'.
A new window will open, this time a normal code module. Paste the following macro in this window.

'==================  Code for  the normal code module ====================
Option Explicit

Sub ShowHideSheets()
    Dim wsWS As Worksheet
    Dim sEntry As String
    Dim bFound As Boolean
    
    'copy the value in cell A4 of Main into a variable
    sEntry = Sheets("Main").Range("A4").Value
    If Len(sEntry) > 0 Then
        'cell A4 contains something, so go check against M28 in each of the worksheets
        For Each wsWS In Sheets
            If Not UCase(wsWS.Name) Like "MAIN" Then        '<<<<<< put the name of the Main sheet here all upper case <<<<<<<<
                'don't include sheet Main in the search
                
                If sEntry Like CStr(wsWS.Range("M28")) Then
                    'A sheet with the same reference has been found. Show the sheet
                    wsWS.Visible = xlSheetVisible
                    bFound = True   'this is a flag to notify that a sheet has been found
                    
                Else
                    'the sheet does not have the same reference, so hide it
                    wsWS.Visible = xlSheetHidden
                End If
            End If
        Next wsWS
    End If
    'all the sheets have been looped through
    
    'check the flag to see if error message is needed
    If bFound = False Then MsgBox "Invalid sheet code in A4", vbOKOnly + vbInformation, "No sheet to show"
End Sub

In this macro there is a comment with <<<<<<<<. Check to see that the name used there is correct.

OK two more things.
In the VBA editor on the left side you will see your workbook with all the sheets listed and at the bottom of the sheets ThisWorkBook (see image)

Double click on 'ThisWorkBook' . This will open the code module for the workbook. Here you can write code for instance what to do when the user opens the workbook.

Paste the following code into this module.

Option Explicit

Private Sub Workbook_Open()
    ShowHideSheets
End Sub

OK. Now go back to Excel and save the workbook as a macro enabled workbook (.xlsm)

Now test it: make a change to cell A4 in Main.

 

[tubrak]

excellent ! this is exactly what I want .
much appreciated !