vba script to unhide next tab only if a cell value is correct

[ennakkadan]

I have to create a database form. I want to unhide next sheet with the same fields only if the value of a particular cell is correct. For instance - the answer to question "Do you want to add more?" is "Yes" then the next sheet will appear, otherwise will remain hidden. Same way if the same cell value in that sheet turns "Yes" next sheet will appear and so on. Can someone help me. BTW the work book is created in .xlsx. Thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

the work book is created in .xlsx

For your request to be implemented, that will have to change to a .xlsm

.. if the same cell value in that sheet turns "Yes" ..

How does the cell change to "Yes"?
a) Is there formula in the cell that may return "Yes"
b) Does the user manually enter "Yes" in the cell when appropriate?
c) Something else? What?

What is the cell address that would contain this "Yes"? (I assume that it would be the same cell in all the relevant sheets)

 

[ennakkadan]

Hello Peter, Thanks for your reply.

For your request to be implemented, that will have to change to a .xlsm - Thanks. This means I have to save the same in .xlsm

The "Yes" would be selected from a drop down menu. The Cell address would be same in all the sheets. In my Data sheet the cell is "B34"

Regards
Ennakkadan

 

[Peter_SSs]

The "Yes" would be selected from a drop down menu. The Cell address would be same in all the sheets. In my Data sheet the cell is "B34"

OK, try this in a copy of your workbook saved as a macro-enabled file (*.xlsm).

1. With the workbook active, open the vba editor (Alt+F11)
2. Double click the 'ThisWorkbook' module of your project in the left hand pane.
3. Paste the code below into the main right hand pane that appeared after step 2.
4. Close the Visual Basic window and test by changing cell B34 in your right-most visible sheet to "Yes" with your drop-down.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Const UnhideCell As String = "B34"
  
  If Not Intersect(Target, Sh.Range(UnhideCell)) Is Nothing Then
    If LCase(Sh.Range(UnhideCell).Value) = "yes" Then
      If Sh.Index < Sheets.Count Then
        Sheets(Sh.Index + 1).Visible = True
      End If
    End If
  End If
End Sub

 

[ennakkadan]

Thanks a lot Peter. It works well. Thanks again.
Regards, Anil (ennakkadan)

 

[Peter_SSs]

Glad it helped. Thanks for letting us know.