Hiding and unhiding multiple cells based on one cells values.

bigbee10200

New Member
Joined
Jun 25, 2018
Messages
2
Hello everyone,
I'm not well versed on VB or Macros, and that will be Obviously apparent when you see my workbook. I'm trying to write a code that will allow me to hide and unhide sheets based on once cells value. However, my code writing is terrible. I've been racking my head for 2 or 3 weeks. In the "General" tab If cell named "HowManyWells" (Q4) has a selected value, I only want the corresponding tab to be displayed. The code I've been working on is great only if "1" is selected. If I continue the code as written, and select "2", the tab 2 Wells is displayed. However if I go back "1" in "HowManyWells", both the "1 Well" than and the "2 Wells" tab are displayed. The general tab should always be displayed. I just don't know what to do. Thanks for the help in advanced.

Below is the code I was using:

Private Sub Worksheet_Change(ByVal Target As Range)


If [HowManyWells] = "1" Then
Sheets("2 Wells").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If


If [HowManyWells] = "2" Then
Sheets("1 Well").Visible = False
Sheets("3 Wells").Visible = False
Sheets("4 Wells").Visible = False
Sheets("5 Wells").Visible = False
Sheets("6 Wells").Visible = False
Sheets("7 Wells").Visible = False
Sheets("8 Wells").Visible = False
Sheets("9 Wells").Visible = False
Sheets("10 Wells").Visible = False
Sheets("11 Wells").Visible = False
Sheets("12 Wells").Visible = False
Sheets("13 Wells").Visible = False
Sheets("14 Wells").Visible = False
Sheets("15 Wells").Visible = False
Sheets("16 Wells").Visible = False
Sheets("17 Wells").Visible = False
Sheets("18 Wells").Visible = False
Sheets("19 Wells").Visible = False
Sheets("20 Wells").Visible = False
Else
Sheets("1 Well").Visible = True
Sheets("2 Wells").Visible = True
Sheets("3 Wells").Visible = True
Sheets("4 Wells").Visible = True
Sheets("5 Wells").Visible = True
Sheets("6 Wells").Visible = True
Sheets("7 Wells").Visible = True
Sheets("8 Wells").Visible = True
Sheets("9 Wells").Visible = True
Sheets("10 Wells").Visible = True
Sheets("11 Wells").Visible = True
Sheets("12 Wells").Visible = True
Sheets("13 Wells").Visible = True
Sheets("14 Wells").Visible = True
Sheets("15 Wells").Visible = True
Sheets("16 Wells").Visible = True
Sheets("17 Wells").Visible = True
Sheets("18 Wells").Visible = True
Sheets("19 Wells").Visible = True
Sheets("20 Wells").Visible = True
End If


End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi,
welcome to the forum.

see if following update to your code does what you want

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer
    If Not Intersect(Target, Me.Range("HowManyWells")) Is Nothing Then
        For i = 1 To 20
            Sheets(i & " Wells").Visible = Target.Value = i
        Next i
    End If


End Sub

It is assumed that the value in your named range HowManyWells is entered directly & not changed by formula.

Dave
 
Last edited:
Upvote 0
Dave,

Thanks so much, worked like a dream. Your a real life saver, now Im on to the next task of getting a email button (that will open outlook and copy and paste a range of cells), clear data button (for a range of cells). But you have help out tremendously. Thanks so much.

Hi,
welcome to the forum.

see if following update to your code does what you want

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer
    If Not Intersect(Target, Me.Range("HowManyWells")) Is Nothing Then
        For i = 1 To 20
            Sheets(i & " Wells").Visible = Target.Value = i
        Next i
    End If


End Sub

It is assumed that the value in your named range HowManyWells is entered directly & not changed by formula.

Dave
 
Upvote 0
Dave,

Thanks so much, worked like a dream. Your a real life saver, now Im on to the next task of getting a email button (that will open outlook and copy and paste a range of cells), clear data button (for a range of cells). But you have help out tremendously. Thanks so much.

Hi glad suggestion helped you.

If you have not already found it - visit Ron de Bruin's site:https://www.rondebruin.nl/windows_articles.htm

Ron has some good articles that may help you with next part of your project.

https://docs.microsoft.com/en-us/pr...eveloper/officetalk2010/ff458119(v=office.11)


Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top