Hide Excel sheet based on value of a cell in a range
I'm trying to hide a sheet in an Excel workbook based on the contents of any of the cells in a given range.
Let's say I have two sheets - "Sheet1" and "Sheet2".
On Sheet1, I want to set up a range - cell C10 to F10.
Each of these cells can either be blank, or contain "Yes" or "No" - chosen from a dropdown box.
If ANY of the cells in the range are set to "Yes", I want Sheet2 to be visible, otherwise (if all the cells are either blank or contain "No") I want Sheet2 hidden.
I've tried various pieces of code, including the below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Range("C10:F10")
If rCell.Value = "Yes" Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
Next rCell
Application.ScreenUpdating = True
End Sub
I've got about as far as Sheet 2 being visible if all the cells equal "Yes" or if F10 equals "Yes", but not if only one of the cells contains "Yes".
excel vba
edited Jan 5 at 16:56
Community♦
11
asked Jan 2 at 14:44
user3722957user3722957
375
add a comment |
I'm trying to hide a sheet in an Excel workbook based on the contents of any of the cells in a given range.
Let's say I have two sheets - "Sheet1" and "Sheet2".
On Sheet1, I want to set up a range - cell C10 to F10.
Each of these cells can either be blank, or contain "Yes" or "No" - chosen from a dropdown box.
If ANY of the cells in the range are set to "Yes", I want Sheet2 to be visible, otherwise (if all the cells are either blank or contain "No") I want Sheet2 hidden.
I've tried various pieces of code, including the below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Range("C10:F10")
If rCell.Value = "Yes" Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
Next rCell
Application.ScreenUpdating = True
End Sub
I've got about as far as Sheet 2 being visible if all the cells equal "Yes" or if F10 equals "Yes", but not if only one of the cells contains "Yes".
excel vba
edited Jan 5 at 16:56
Community♦
11
asked Jan 2 at 14:44
user3722957user3722957
375
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
3
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0
– Scott Craner
Jan 2 at 14:51
@ScottCraner - you forgot the criteria part of theCOUNTIF.
– Darren Bartrup-Cook
Jan 2 at 14:57
1
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58
add a comment |
I'm trying to hide a sheet in an Excel workbook based on the contents of any of the cells in a given range.
Let's say I have two sheets - "Sheet1" and "Sheet2".
On Sheet1, I want to set up a range - cell C10 to F10.
Each of these cells can either be blank, or contain "Yes" or "No" - chosen from a dropdown box.
If ANY of the cells in the range are set to "Yes", I want Sheet2 to be visible, otherwise (if all the cells are either blank or contain "No") I want Sheet2 hidden.
I've tried various pieces of code, including the below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Range("C10:F10")
If rCell.Value = "Yes" Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
Next rCell
Application.ScreenUpdating = True
End Sub
I've got about as far as Sheet 2 being visible if all the cells equal "Yes" or if F10 equals "Yes", but not if only one of the cells contains "Yes".
excel vba
edited Jan 5 at 16:56
Community♦
11
asked Jan 2 at 14:44
user3722957user3722957
375
I'm trying to hide a sheet in an Excel workbook based on the contents of any of the cells in a given range.
Let's say I have two sheets - "Sheet1" and "Sheet2".
On Sheet1, I want to set up a range - cell C10 to F10.
Each of these cells can either be blank, or contain "Yes" or "No" - chosen from a dropdown box.
If ANY of the cells in the range are set to "Yes", I want Sheet2 to be visible, otherwise (if all the cells are either blank or contain "No") I want Sheet2 hidden.
I've tried various pieces of code, including the below.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Range("C10:F10")
If rCell.Value = "Yes" Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
Next rCell
Application.ScreenUpdating = True
End Sub
I've got about as far as Sheet 2 being visible if all the cells equal "Yes" or if F10 equals "Yes", but not if only one of the cells contains "Yes".
excel vba
excel vba
edited Jan 5 at 16:56
Community♦
11
asked Jan 2 at 14:44
user3722957user3722957
375
edited Jan 5 at 16:56
Community♦
11
asked Jan 2 at 14:44
user3722957user3722957
375
edited Jan 5 at 16:56
Community♦
11
edited Jan 5 at 16:56
Community♦
11
edited Jan 5 at 16:56
Community♦
11
11
asked Jan 2 at 14:44
user3722957user3722957
375
asked Jan 2 at 14:44
user3722957user3722957
375
asked Jan 2 at 14:44
user3722957user3722957
375
375
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
3
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0
– Scott Craner
Jan 2 at 14:51
@ScottCraner - you forgot the criteria part of theCOUNTIF.
– Darren Bartrup-Cook
Jan 2 at 14:57
1
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58
add a comment |
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
3
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0
– Scott Craner
Jan 2 at 14:51
@ScottCraner - you forgot the criteria part of theCOUNTIF.
– Darren Bartrup-Cook
Jan 2 at 14:57
1
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
3
3
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0– Scott Craner
Jan 2 at 14:51
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0– Scott Craner
Jan 2 at 14:51
@ScottCraner - you forgot the criteria part of the
COUNTIF.– Darren Bartrup-Cook
Jan 2 at 14:57
@ScottCraner - you forgot the criteria part of the
COUNTIF.– Darren Bartrup-Cook
Jan 2 at 14:57
1
1
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58
add a comment |
2 Answers
2
active
oldest
votes
No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
End If
End Sub
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the rangeC10:F10then you only need check that value:Worksheets("Sheet2").Visible = Target = "Yes"
– Darren Bartrup-Cook
Jan 2 at 15:00
add a comment |
Modify and try:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim Hide As Boolean
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.Range("C10:F10")
For Each cell In rng
Hide = False
If cell.Value = "Yes" Then
Hide = False
Exit For
Else
Hide = True
End If
Next
If Hide = True Then
ws.Visible = False
End If
Next
End Sub
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008332%2fhide-excel-sheet-based-on-value-of-a-cell-in-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
End If
End Sub
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the rangeC10:F10then you only need check that value:Worksheets("Sheet2").Visible = Target = "Yes"
– Darren Bartrup-Cook
Jan 2 at 15:00
add a comment |
No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
End If
End Sub
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the rangeC10:F10then you only need check that value:Worksheets("Sheet2").Visible = Target = "Yes"
– Darren Bartrup-Cook
Jan 2 at 15:00
add a comment |
No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
End If
End Sub
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
No loop needed, and create an If to test whether the Cell that change is in the range to test, just to save some comp time:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("C10:F10"),Target) Is Nothing Then
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"),"Yes")>0
End If
End Sub
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
edited Jan 2 at 14:58
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
answered Jan 2 at 14:56
Scott CranerScott Craner
92.6k82652
92.6k82652
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the rangeC10:F10then you only need check that value:Worksheets("Sheet2").Visible = Target = "Yes"
– Darren Bartrup-Cook
Jan 2 at 15:00
add a comment |
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the rangeC10:F10then you only need check that value:Worksheets("Sheet2").Visible = Target = "Yes"
– Darren Bartrup-Cook
Jan 2 at 15:00
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
Absolutely perfect!! Many thanks - works a treat!
– user3722957
Jan 2 at 14:58
1
1
Was thinking of something like that - only possible change would be that if you're changing a single value in the range
C10:F10 then you only need check that value: Worksheets("Sheet2").Visible = Target = "Yes"– Darren Bartrup-Cook
Jan 2 at 15:00
Was thinking of something like that - only possible change would be that if you're changing a single value in the range
C10:F10 then you only need check that value: Worksheets("Sheet2").Visible = Target = "Yes"– Darren Bartrup-Cook
Jan 2 at 15:00
add a comment |
Modify and try:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim Hide As Boolean
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.Range("C10:F10")
For Each cell In rng
Hide = False
If cell.Value = "Yes" Then
Hide = False
Exit For
Else
Hide = True
End If
Next
If Hide = True Then
ws.Visible = False
End If
Next
End Sub
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
add a comment |
Modify and try:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim Hide As Boolean
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.Range("C10:F10")
For Each cell In rng
Hide = False
If cell.Value = "Yes" Then
Hide = False
Exit For
Else
Hide = True
End If
Next
If Hide = True Then
ws.Visible = False
End If
Next
End Sub
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
add a comment |
Modify and try:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim Hide As Boolean
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.Range("C10:F10")
For Each cell In rng
Hide = False
If cell.Value = "Yes" Then
Hide = False
Exit For
Else
Hide = True
End If
Next
If Hide = True Then
ws.Visible = False
End If
Next
End Sub
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
Modify and try:
Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim Hide As Boolean
For Each ws In ThisWorkbook.Worksheets
Set rng = ws.Range("C10:F10")
For Each cell In rng
Hide = False
If cell.Value = "Yes" Then
Hide = False
Exit For
Else
Hide = True
End If
Next
If Hide = True Then
ws.Visible = False
End If
Next
End Sub
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
answered Jan 2 at 14:55
Error 1004Error 1004
2,3231417
2,3231417
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008332%2fhide-excel-sheet-based-on-value-of-a-cell-in-a-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
you could probably save some time and use a application.countif(wsrng,"Yes")>0 for your desired range, so you don't have to loop. if true, show, if false, hide.
– Cyril
Jan 2 at 14:49
3
Worksheets("Sheet2").Visible = Application.Countif(Range("C10:F10"))>0– Scott Craner
Jan 2 at 14:51
@ScottCraner - you forgot the criteria part of the
COUNTIF.– Darren Bartrup-Cook
Jan 2 at 14:57
1
@DarrenBartrup-Cook which is why I answered, noticed that after the time limit was up.
– Scott Craner
Jan 2 at 14:58