MS Excel VBA- hiding rows based on a cell value doesn't work
I am very much a novice and am trying to get certain rows to show/hide based on values in certain cells when a command button is pressed. I need help with 2 things:
1) Would like this to actually work without pressing a button. I.e. each time the value of cell C10 is changed the code runs to hide/show.
2) I have 4 subroutines that run when the button is pressed. Three of them work fine. I can't get "Rows1to13" subroutine to run. Can't understand it because t's te same exact routine I'm just changing the cells that trigger it and changing which range of rows get hidden.
My code is below
Private Sub CommandButton4_Click()
Call HideDefault
Call rows1to13
End Sub
Sub HideDefault()
If (Range("C10")) = "Manual" Then
For a = 44 To 90
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
For a = 92 To 125
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C10")) = "Manual" Then
MsgBox "Manual Data Entry chosen"
End If
End Sub
Sub rows1to13()
'and if C11=yes and C16=yes'
If (Range("C11")) = "Yes" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "Yes" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
'and if C11=No and C16=No'
If (Range("C11")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
If (Range("C16")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
'and if C11= blank and C16= Blank'
If (Range("C11")) = "" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
End Sub
excel vba
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
add a comment |
I am very much a novice and am trying to get certain rows to show/hide based on values in certain cells when a command button is pressed. I need help with 2 things:
1) Would like this to actually work without pressing a button. I.e. each time the value of cell C10 is changed the code runs to hide/show.
2) I have 4 subroutines that run when the button is pressed. Three of them work fine. I can't get "Rows1to13" subroutine to run. Can't understand it because t's te same exact routine I'm just changing the cells that trigger it and changing which range of rows get hidden.
My code is below
Private Sub CommandButton4_Click()
Call HideDefault
Call rows1to13
End Sub
Sub HideDefault()
If (Range("C10")) = "Manual" Then
For a = 44 To 90
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
For a = 92 To 125
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C10")) = "Manual" Then
MsgBox "Manual Data Entry chosen"
End If
End Sub
Sub rows1to13()
'and if C11=yes and C16=yes'
If (Range("C11")) = "Yes" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "Yes" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
'and if C11=No and C16=No'
If (Range("C11")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
If (Range("C16")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
'and if C11= blank and C16= Blank'
If (Range("C11")) = "" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
End Sub
excel vba
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11
add a comment |
I am very much a novice and am trying to get certain rows to show/hide based on values in certain cells when a command button is pressed. I need help with 2 things:
1) Would like this to actually work without pressing a button. I.e. each time the value of cell C10 is changed the code runs to hide/show.
2) I have 4 subroutines that run when the button is pressed. Three of them work fine. I can't get "Rows1to13" subroutine to run. Can't understand it because t's te same exact routine I'm just changing the cells that trigger it and changing which range of rows get hidden.
My code is below
Private Sub CommandButton4_Click()
Call HideDefault
Call rows1to13
End Sub
Sub HideDefault()
If (Range("C10")) = "Manual" Then
For a = 44 To 90
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
For a = 92 To 125
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C10")) = "Manual" Then
MsgBox "Manual Data Entry chosen"
End If
End Sub
Sub rows1to13()
'and if C11=yes and C16=yes'
If (Range("C11")) = "Yes" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "Yes" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
'and if C11=No and C16=No'
If (Range("C11")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
If (Range("C16")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
'and if C11= blank and C16= Blank'
If (Range("C11")) = "" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
End Sub
excel vba
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
I am very much a novice and am trying to get certain rows to show/hide based on values in certain cells when a command button is pressed. I need help with 2 things:
1) Would like this to actually work without pressing a button. I.e. each time the value of cell C10 is changed the code runs to hide/show.
2) I have 4 subroutines that run when the button is pressed. Three of them work fine. I can't get "Rows1to13" subroutine to run. Can't understand it because t's te same exact routine I'm just changing the cells that trigger it and changing which range of rows get hidden.
My code is below
Private Sub CommandButton4_Click()
Call HideDefault
Call rows1to13
End Sub
Sub HideDefault()
If (Range("C10")) = "Manual" Then
For a = 44 To 90
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
For a = 92 To 125
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C10")) = "Manual" Then
MsgBox "Manual Data Entry chosen"
End If
End Sub
Sub rows1to13()
'and if C11=yes and C16=yes'
If (Range("C11")) = "Yes" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "Yes" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
'and if C11=No and C16=No'
If (Range("C11")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
If (Range("C16")) = "No" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = True
End If
Next
End If
'and if C11= blank and C16= Blank'
If (Range("C11")) = "" Then
For a = 12 To 13
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
If (Range("C16")) = "" Then
For a = 17 To 20
If Worksheets("Sheet1").Cells(a, 1).Value >= "0" Then
Worksheets("Sheet1").Rows(a).Hidden = False
End If
Next
End If
End Sub
excel vba
excel vba
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
edited Dec 28 '18 at 23:45
K.Dᴀᴠɪs
7,091112339
7,091112339
asked Dec 28 '18 at 23:40
JoeRJoeR
457
asked Dec 28 '18 at 23:40
JoeRJoeR
457
asked Dec 28 '18 at 23:40
JoeRJoeR
457
457
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11
add a comment |
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11
add a comment |
1 Answer
1
active
oldest
votes
I found the issue why the code wasn't executing. . It was very simple. The code is looking for value in a cell to execute, and it case sensitive. In the worksheet the cell input was done in Caps i.e. "NO", but the code was written to look for "No".
Used the worksheet change event to execute without having to press button. Thanks VBasic2008
answered Dec 29 '18 at 18:13
JoeRJoeR
457
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%2f53965460%2fms-excel-vba-hiding-rows-based-on-a-cell-value-doesnt-work%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I found the issue why the code wasn't executing. . It was very simple. The code is looking for value in a cell to execute, and it case sensitive. In the worksheet the cell input was done in Caps i.e. "NO", but the code was written to look for "No".
Used the worksheet change event to execute without having to press button. Thanks VBasic2008
answered Dec 29 '18 at 18:13
JoeRJoeR
457
add a comment |
I found the issue why the code wasn't executing. . It was very simple. The code is looking for value in a cell to execute, and it case sensitive. In the worksheet the cell input was done in Caps i.e. "NO", but the code was written to look for "No".
Used the worksheet change event to execute without having to press button. Thanks VBasic2008
answered Dec 29 '18 at 18:13
JoeRJoeR
457
add a comment |
I found the issue why the code wasn't executing. . It was very simple. The code is looking for value in a cell to execute, and it case sensitive. In the worksheet the cell input was done in Caps i.e. "NO", but the code was written to look for "No".
Used the worksheet change event to execute without having to press button. Thanks VBasic2008
answered Dec 29 '18 at 18:13
JoeRJoeR
457
I found the issue why the code wasn't executing. . It was very simple. The code is looking for value in a cell to execute, and it case sensitive. In the worksheet the cell input was done in Caps i.e. "NO", but the code was written to look for "No".
Used the worksheet change event to execute without having to press button. Thanks VBasic2008
answered Dec 29 '18 at 18:13
JoeRJoeR
457
answered Dec 29 '18 at 18:13
JoeRJoeR
457
answered Dec 29 '18 at 18:13
JoeRJoeR
457
answered Dec 29 '18 at 18:13
JoeRJoeR
457
457
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%2f53965460%2fms-excel-vba-hiding-rows-based-on-a-cell-value-doesnt-work%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
How is the value in C10 changed? Manually or by formula?
– VBasic2008
Dec 29 '18 at 0:35
The value in C10 is changed manually. It is also changed by hitting a button wihch resets the sheet to remove all entered data. C11 and C16 are the same. The code with C10 works, its the C11 and C16 ones that do not
– JoeR
Dec 29 '18 at 0:48
My question was in view of automation. So you should use the Change Event. And how are C11 and C16 changed? Describe a little more the problems with C11 and C16.
– VBasic2008
Dec 29 '18 at 0:52
When I press the commandbutton, Subroutine HideDefault works fine. It shows/hides rows if C10 value = "Tpx Default". I basically copy/pasted the code for that functioning code several times and changed from C10 to C11 or C16 along with changing the rows that get shown/hidden to cover the scenarios I need. When I press the button rows 12-13 (C11) and rows 17-20 (C16) should show/hide based on if C11/C16 are Yes No or Blank. NOthing happens at all.
– JoeR
Dec 29 '18 at 1:27
@JoeR - Can you please edit your question instead of adding information in comments? It will make things a lot clearer for everyone
– cybernetic.nomad
Dec 29 '18 at 15:11