Worksheet_Calculate Script error when switched off that tab
0
I created the following script to display different colored shapes based on the number when a data query refreshes, and it works but when I switch off the tab or open another workbook it gives an error when refreshing saying object not found and highlights the following line:
ActiveSheet.Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
I figured it was because of the "ActiveSheet" reference so I replaced it with the actual worksheet name and it still works but still give the error.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
What am I doing wrong here? Also, if you see any code that can be simplified, my VBS skills are not the best. The code is as follows:
Private Sub Worksheet_Calculate()
Dim Xrg As Range, Yrg As Range
Set Xrg = Range("K31")
Set Yrg = Range("K32")
For Each aCell In Xrg
If Not Intersect(Xrg, Range("K31")) Is Nothing Then
If Range("K31").Value = 0 Then
If Rows("25:25").EntireRow.Hidden = False Then
Rows("25:25").EntireRow.Hidden = True
End If
ElseIf Range("K31").Value <> 0 Then
If Rows("25:25").EntireRow.Hidden = True Then
Rows("25:25").EntireRow.Hidden = False
End If
End If
End If
Next
For Each aCell In Yrg
If Range("K32").Value < 55 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = True
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 55 And Range("K32").Value < 65 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = True
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 65 And Range("K32").Value < 75 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = True
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 75 And Range("K32").Value < 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = True
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = True
End If
Next
End Sub
microsoft-excel macros vbscript
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
add a comment |
0
I created the following script to display different colored shapes based on the number when a data query refreshes, and it works but when I switch off the tab or open another workbook it gives an error when refreshing saying object not found and highlights the following line:
ActiveSheet.Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
I figured it was because of the "ActiveSheet" reference so I replaced it with the actual worksheet name and it still works but still give the error.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
What am I doing wrong here? Also, if you see any code that can be simplified, my VBS skills are not the best. The code is as follows:
Private Sub Worksheet_Calculate()
Dim Xrg As Range, Yrg As Range
Set Xrg = Range("K31")
Set Yrg = Range("K32")
For Each aCell In Xrg
If Not Intersect(Xrg, Range("K31")) Is Nothing Then
If Range("K31").Value = 0 Then
If Rows("25:25").EntireRow.Hidden = False Then
Rows("25:25").EntireRow.Hidden = True
End If
ElseIf Range("K31").Value <> 0 Then
If Rows("25:25").EntireRow.Hidden = True Then
Rows("25:25").EntireRow.Hidden = False
End If
End If
End If
Next
For Each aCell In Yrg
If Range("K32").Value < 55 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = True
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 55 And Range("K32").Value < 65 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = True
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 65 And Range("K32").Value < 75 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = True
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 75 And Range("K32").Value < 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = True
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = True
End If
Next
End Sub
microsoft-excel macros vbscript
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
One suggestion would be to do aSELECT CASEonRange("K32").Valueinstead of all theIFstatements.
– Rey Juna
Jan 22 at 18:20
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26
add a comment |
0
0
0
1
I created the following script to display different colored shapes based on the number when a data query refreshes, and it works but when I switch off the tab or open another workbook it gives an error when refreshing saying object not found and highlights the following line:
ActiveSheet.Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
I figured it was because of the "ActiveSheet" reference so I replaced it with the actual worksheet name and it still works but still give the error.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
What am I doing wrong here? Also, if you see any code that can be simplified, my VBS skills are not the best. The code is as follows:
Private Sub Worksheet_Calculate()
Dim Xrg As Range, Yrg As Range
Set Xrg = Range("K31")
Set Yrg = Range("K32")
For Each aCell In Xrg
If Not Intersect(Xrg, Range("K31")) Is Nothing Then
If Range("K31").Value = 0 Then
If Rows("25:25").EntireRow.Hidden = False Then
Rows("25:25").EntireRow.Hidden = True
End If
ElseIf Range("K31").Value <> 0 Then
If Rows("25:25").EntireRow.Hidden = True Then
Rows("25:25").EntireRow.Hidden = False
End If
End If
End If
Next
For Each aCell In Yrg
If Range("K32").Value < 55 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = True
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 55 And Range("K32").Value < 65 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = True
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 65 And Range("K32").Value < 75 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = True
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 75 And Range("K32").Value < 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = True
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = True
End If
Next
End Sub
microsoft-excel macros vbscript
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
I created the following script to display different colored shapes based on the number when a data query refreshes, and it works but when I switch off the tab or open another workbook it gives an error when refreshing saying object not found and highlights the following line:
ActiveSheet.Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
I figured it was because of the "ActiveSheet" reference so I replaced it with the actual worksheet name and it still works but still give the error.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
What am I doing wrong here? Also, if you see any code that can be simplified, my VBS skills are not the best. The code is as follows:
Private Sub Worksheet_Calculate()
Dim Xrg As Range, Yrg As Range
Set Xrg = Range("K31")
Set Yrg = Range("K32")
For Each aCell In Xrg
If Not Intersect(Xrg, Range("K31")) Is Nothing Then
If Range("K31").Value = 0 Then
If Rows("25:25").EntireRow.Hidden = False Then
Rows("25:25").EntireRow.Hidden = True
End If
ElseIf Range("K31").Value <> 0 Then
If Rows("25:25").EntireRow.Hidden = True Then
Rows("25:25").EntireRow.Hidden = False
End If
End If
End If
Next
For Each aCell In Yrg
If Range("K32").Value < 55 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = True
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 55 And Range("K32").Value < 65 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = True
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 65 And Range("K32").Value < 75 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = True
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 75 And Range("K32").Value < 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = True
Worksheets("Outbound").Shapes("Util 5").Visible = False
End If
If Range("K32").Value >= 85 Then
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = True
End If
Next
End Sub
microsoft-excel macros vbscript
microsoft-excel macros vbscript
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
asked Jan 22 at 4:27
Mathew BaulerMathew Bauler
86
86
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
One suggestion would be to do aSELECT CASEonRange("K32").Valueinstead of all theIFstatements.
– Rey Juna
Jan 22 at 18:20
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26
add a comment |
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
One suggestion would be to do aSELECT CASEonRange("K32").Valueinstead of all theIFstatements.
– Rey Juna
Jan 22 at 18:20
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
One suggestion would be to do a
SELECT CASE on Range("K32").Value instead of all the IF statements.– Rey Juna
Jan 22 at 18:20
One suggestion would be to do a
SELECT CASE on Range("K32").Value instead of all the IF statements.– Rey Juna
Jan 22 at 18:20
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26
add a comment |
1 Answer
1
active
oldest
votes
0
One improvement that you can make to your code is to replace your IF statements with SELECT CASE and initialize your formatting prior which makes it easier to see what is being changed for each case.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
Select Case Range("K32").Value
Case Is < 55
Worksheets("Outbound").Shapes("Util 1").Visible = True
Case 55 To 64
Worksheets("Outbound").Shapes("Util 2").Visible = True
Case 65 to 74
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 3").Visible = True
Case 75 to 84
Worksheets("Outbound").Shapes("Util 4").Visible = True
Case Is > 84
Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
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%2f1396874%2fworksheet-calculate-script-error-when-switched-off-that-tab%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
One improvement that you can make to your code is to replace your IF statements with SELECT CASE and initialize your formatting prior which makes it easier to see what is being changed for each case.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
Select Case Range("K32").Value
Case Is < 55
Worksheets("Outbound").Shapes("Util 1").Visible = True
Case 55 To 64
Worksheets("Outbound").Shapes("Util 2").Visible = True
Case 65 to 74
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 3").Visible = True
Case 75 to 84
Worksheets("Outbound").Shapes("Util 4").Visible = True
Case Is > 84
Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
add a comment |
0
One improvement that you can make to your code is to replace your IF statements with SELECT CASE and initialize your formatting prior which makes it easier to see what is being changed for each case.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
Select Case Range("K32").Value
Case Is < 55
Worksheets("Outbound").Shapes("Util 1").Visible = True
Case 55 To 64
Worksheets("Outbound").Shapes("Util 2").Visible = True
Case 65 to 74
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 3").Visible = True
Case 75 to 84
Worksheets("Outbound").Shapes("Util 4").Visible = True
Case Is > 84
Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
add a comment |
0
0
0
One improvement that you can make to your code is to replace your IF statements with SELECT CASE and initialize your formatting prior which makes it easier to see what is being changed for each case.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
Select Case Range("K32").Value
Case Is < 55
Worksheets("Outbound").Shapes("Util 1").Visible = True
Case 55 To 64
Worksheets("Outbound").Shapes("Util 2").Visible = True
Case 65 to 74
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 3").Visible = True
Case 75 to 84
Worksheets("Outbound").Shapes("Util 4").Visible = True
Case Is > 84
Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
One improvement that you can make to your code is to replace your IF statements with SELECT CASE and initialize your formatting prior which makes it easier to see what is being changed for each case.
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 2
Worksheets("Outbound").Shapes("Util 1").Visible = False
Worksheets("Outbound").Shapes("Util 2").Visible = False
Worksheets("Outbound").Shapes("Util 3").Visible = False
Worksheets("Outbound").Shapes("Util 4").Visible = False
Worksheets("Outbound").Shapes("Util 5").Visible = False
Select Case Range("K32").Value
Case Is < 55
Worksheets("Outbound").Shapes("Util 1").Visible = True
Case 55 To 64
Worksheets("Outbound").Shapes("Util 2").Visible = True
Case 65 to 74
Worksheets("Outbound").Shapes("TextBox 16").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("TextBox 43").TextFrame.Characters.Font.ColorIndex = 1
Worksheets("Outbound").Shapes("Util 3").Visible = True
Case 75 to 84
Worksheets("Outbound").Shapes("Util 4").Visible = True
Case Is > 84
Worksheets("Outbound").Shapes("Util 5").Visible = True
End Select
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
answered Jan 23 at 17:26
Rey JunaRey Juna
652211
652211
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%2f1396874%2fworksheet-calculate-script-error-when-switched-off-that-tab%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
I think I actually fixed it myself. Didn't think to just remove the references altogether!
– Mathew Bauler
Jan 22 at 6:08
One suggestion would be to do a
SELECT CASEonRange("K32").Valueinstead of all theIFstatements.– Rey Juna
Jan 22 at 18:20
Could you give an example of how I would use "SELECT CASE". I've googled it and can't really wrap my head around it?
– Mathew Bauler
Jan 23 at 1:53
I've posted an example of how you could do it below.
– Rey Juna
Jan 23 at 17:26