Run script after web query refresh. Can't get it to work
0
This is the code I have for the worksheet. It works if I type something into D32, but if the formula changes the number automatically it does nothing. What am I doing wrong? I basically have it set to hide a row if a cell is zero or show it if it a positive number.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D32")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
If Range("D32").Value = "0" Then
Rows("32:32").EntireRow.Hidden = True
ElseIf Range("D32").Value = "<>0" Then
Rows("32:32").EntireRow.Hidden = False
End If
End If
End Sub
microsoft-excel vbscript
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
|
show 1 more comment
0
This is the code I have for the worksheet. It works if I type something into D32, but if the formula changes the number automatically it does nothing. What am I doing wrong? I basically have it set to hide a row if a cell is zero or show it if it a positive number.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D32")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
If Range("D32").Value = "0" Then
Rows("32:32").EntireRow.Hidden = True
ElseIf Range("D32").Value = "<>0" Then
Rows("32:32").EntireRow.Hidden = False
End If
End If
End Sub
microsoft-excel vbscript
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value inD32, if isZEROthe code hidesRow 32it self and if and in case of other thanZerois not getting clicked after been hidden becauseD32is not visible cell to edit.
– Rajesh S
Jan 3 at 6:32
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any likeD30to hide & unhide theRow 32!!. And if inD32you have entered Formula and you are looking to hideRow32if the Formula returnsZerothen you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so thatD32become<>0
– Rajesh S
Jan 3 at 6:47
Cont,, and your Syntax shouldIf Range("D32").Value = 0 ThenandElseIf Range("D32").Value <> 0 Then☺
– Rajesh S
Jan 3 at 7:00
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45
|
show 1 more comment
0
0
0
1
This is the code I have for the worksheet. It works if I type something into D32, but if the formula changes the number automatically it does nothing. What am I doing wrong? I basically have it set to hide a row if a cell is zero or show it if it a positive number.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D32")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
If Range("D32").Value = "0" Then
Rows("32:32").EntireRow.Hidden = True
ElseIf Range("D32").Value = "<>0" Then
Rows("32:32").EntireRow.Hidden = False
End If
End If
End Sub
microsoft-excel vbscript
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
This is the code I have for the worksheet. It works if I type something into D32, but if the formula changes the number automatically it does nothing. What am I doing wrong? I basically have it set to hide a row if a cell is zero or show it if it a positive number.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D32")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' Place your code here.
If Range("D32").Value = "0" Then
Rows("32:32").EntireRow.Hidden = True
ElseIf Range("D32").Value = "<>0" Then
Rows("32:32").EntireRow.Hidden = False
End If
End If
End Sub
microsoft-excel vbscript
microsoft-excel vbscript
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
asked Jan 3 at 3:41
Mathew BaulerMathew Bauler
86
86
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value inD32, if isZEROthe code hidesRow 32it self and if and in case of other thanZerois not getting clicked after been hidden becauseD32is not visible cell to edit.
– Rajesh S
Jan 3 at 6:32
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any likeD30to hide & unhide theRow 32!!. And if inD32you have entered Formula and you are looking to hideRow32if the Formula returnsZerothen you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so thatD32become<>0
– Rajesh S
Jan 3 at 6:47
Cont,, and your Syntax shouldIf Range("D32").Value = 0 ThenandElseIf Range("D32").Value <> 0 Then☺
– Rajesh S
Jan 3 at 7:00
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45
|
show 1 more comment
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value inD32, if isZEROthe code hidesRow 32it self and if and in case of other thanZerois not getting clicked after been hidden becauseD32is not visible cell to edit.
– Rajesh S
Jan 3 at 6:32
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any likeD30to hide & unhide theRow 32!!. And if inD32you have entered Formula and you are looking to hideRow32if the Formula returnsZerothen you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so thatD32become<>0
– Rajesh S
Jan 3 at 6:47
Cont,, and your Syntax shouldIf Range("D32").Value = 0 ThenandElseIf Range("D32").Value <> 0 Then☺
– Rajesh S
Jan 3 at 7:00
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value in
D32, if is ZERO the code hides Row 32 it self and if and in case of other than Zero is not getting clicked after been hidden because D32 is not visible cell to edit.– Rajesh S
Jan 3 at 6:32
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value in
D32, if is ZERO the code hides Row 32 it self and if and in case of other than Zero is not getting clicked after been hidden because D32 is not visible cell to edit.– Rajesh S
Jan 3 at 6:32
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any like
D30 to hide & unhide the Row 32 !!. And if in D32 you have entered Formula and you are looking to hide Row32 if the Formula returns Zero then you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so that D32 become <>0– Rajesh S
Jan 3 at 6:47
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any like
D30 to hide & unhide the Row 32 !!. And if in D32 you have entered Formula and you are looking to hide Row32 if the Formula returns Zero then you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so that D32 become <>0– Rajesh S
Jan 3 at 6:47
Cont,, and your Syntax should
If Range("D32").Value = 0 Then and ElseIf Range("D32").Value <> 0 Then ☺– Rajesh S
Jan 3 at 7:00
Cont,, and your Syntax should
If Range("D32").Value = 0 Then and ElseIf Range("D32").Value <> 0 Then ☺– Rajesh S
Jan 3 at 7:00
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45
|
show 1 more comment
1 Answer
1
active
oldest
votes
0
Mathew, try this code, is fully functional.
Private Sub Worksheet_Calculate()
Dim KeyCells As Range
Set KeyCells = Range("C1")
If Range("C1").Value = 0 Then
Rows("3:5").EntireRow.Hidden = True
ElseIf Range("C1").Value <> 0 Then
Rows("3:5").EntireRow.Hidden = False
End If
End Sub
N.B. Adjust Cell/Row references as needed.
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
});
}
});
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1390032%2frun-script-after-web-query-refresh-cant-get-it-to-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
0
Mathew, try this code, is fully functional.
Private Sub Worksheet_Calculate()
Dim KeyCells As Range
Set KeyCells = Range("C1")
If Range("C1").Value = 0 Then
Rows("3:5").EntireRow.Hidden = True
ElseIf Range("C1").Value <> 0 Then
Rows("3:5").EntireRow.Hidden = False
End If
End Sub
N.B. Adjust Cell/Row references as needed.
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
add a comment |
0
Mathew, try this code, is fully functional.
Private Sub Worksheet_Calculate()
Dim KeyCells As Range
Set KeyCells = Range("C1")
If Range("C1").Value = 0 Then
Rows("3:5").EntireRow.Hidden = True
ElseIf Range("C1").Value <> 0 Then
Rows("3:5").EntireRow.Hidden = False
End If
End Sub
N.B. Adjust Cell/Row references as needed.
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
add a comment |
0
0
0
Mathew, try this code, is fully functional.
Private Sub Worksheet_Calculate()
Dim KeyCells As Range
Set KeyCells = Range("C1")
If Range("C1").Value = 0 Then
Rows("3:5").EntireRow.Hidden = True
ElseIf Range("C1").Value <> 0 Then
Rows("3:5").EntireRow.Hidden = False
End If
End Sub
N.B. Adjust Cell/Row references as needed.
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
Mathew, try this code, is fully functional.
Private Sub Worksheet_Calculate()
Dim KeyCells As Range
Set KeyCells = Range("C1")
If Range("C1").Value = 0 Then
Rows("3:5").EntireRow.Hidden = True
ElseIf Range("C1").Value <> 0 Then
Rows("3:5").EntireRow.Hidden = False
End If
End Sub
N.B. Adjust Cell/Row references as needed.
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
answered Feb 2 at 9:18
Rajesh SRajesh S
3,8081523
3,8081523
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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.
draft saved
draft discarded
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%2fsuperuser.com%2fquestions%2f1390032%2frun-script-after-web-query-refresh-cant-get-it-to-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
Nothing wrong with the Code,,, actually the Worksheet Change event check the Value in
D32, if isZEROthe code hidesRow 32it self and if and in case of other thanZerois not getting clicked after been hidden becauseD32is not visible cell to edit.– Rajesh S
Jan 3 at 6:32
Cont,,, if you want to get rid from all that,, change the Key Cell D32 to any like
D30to hide & unhide theRow 32!!. And if inD32you have entered Formula and you are looking to hideRow32if the Formula returnsZerothen you need to use Worksheet Calculate Event but better hide some other Rows instead of Row has Formula,, since after hiding you can't get chance to edit value so thatD32become<>0– Rajesh S
Jan 3 at 6:47
Cont,, and your Syntax should
If Range("D32").Value = 0 ThenandElseIf Range("D32").Value <> 0 Then☺– Rajesh S
Jan 3 at 7:00
Thank you, that makes a lot of sense!
– Mathew Bauler
Jan 3 at 7:22
So I tried: Private Sub Worksheet_Calculate() 'Updateby Extendoffice Dim Xrg As Range Set Xrg = Range("K39") If Not Intersect(Xrg, Range("K39")) Is Nothing Then If Range("K39").Value = 0 Then Rows("32:32").EntireRow.Hidden = True ElseIf Range("K39").Value <> 0 Then Rows("32:32").EntireRow.Hidden = False End If End If End Sub ...but now it just loops saying calculating and freezes and crashes :(
– Mathew Bauler
Jan 3 at 7:45