Mixing
Run macro when slicer change
0
I can't get the macro to automatically run when an item in a slicer (from a chart) is selected. I tried by adding the code under a private sub or even public function but still can't get the click/selection of an item in the slicer to trigger the macro. I tested the sub Slicer_Opt and it works when I have an item selected and I run it manually, but as I said, I can't get it to automatically trigger when I select an item in the slicer. Until now I have the following:
Public Function ClickedSlicerItem(ByRef SlicerOpt1)
Call Slicer_Opt
End Function
Sub Slicer_Opt()
Dim LastMonth As String
Dim MiddleMonth As String
Dim FirstMonth As String
Dim Date1 As Date
Dim MonthMax As String
Dim MinMonth As Date
Dim SlicerMthCache As SlicerCache
Dim SlicerOpt1 As SlicerItem
Date1 = WorksheetFunction.Max(Sheets("Activity Log").Range("H:H"))
MinMonth = WorksheetFunction.Min(Sheets("Activity Log").Range("H:H"))
MonthMax = Month(Date1)
LastMonth = MonthName(MonthMax, True)
MiddleMonth = MonthName(MonthMax - 1, True)
FirstMonth = MonthName(MonthMax - 2, True)
If SlicerOpt1.ActiveItem Is Not Null Then
Application.ScreenUpdating = False
If ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(LastMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(MiddleMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(FirstMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
End If
Application.ScreenUpdating = True
Else: ActiveChart.ChartType = xlBarStacked100
End If
End Sub
excel vba
asked Nov 21 '18 at 18:18
MariaEMariaE
11
add a comment |
0
I can't get the macro to automatically run when an item in a slicer (from a chart) is selected. I tried by adding the code under a private sub or even public function but still can't get the click/selection of an item in the slicer to trigger the macro. I tested the sub Slicer_Opt and it works when I have an item selected and I run it manually, but as I said, I can't get it to automatically trigger when I select an item in the slicer. Until now I have the following:
Public Function ClickedSlicerItem(ByRef SlicerOpt1)
Call Slicer_Opt
End Function
Sub Slicer_Opt()
Dim LastMonth As String
Dim MiddleMonth As String
Dim FirstMonth As String
Dim Date1 As Date
Dim MonthMax As String
Dim MinMonth As Date
Dim SlicerMthCache As SlicerCache
Dim SlicerOpt1 As SlicerItem
Date1 = WorksheetFunction.Max(Sheets("Activity Log").Range("H:H"))
MinMonth = WorksheetFunction.Min(Sheets("Activity Log").Range("H:H"))
MonthMax = Month(Date1)
LastMonth = MonthName(MonthMax, True)
MiddleMonth = MonthName(MonthMax - 1, True)
FirstMonth = MonthName(MonthMax - 2, True)
If SlicerOpt1.ActiveItem Is Not Null Then
Application.ScreenUpdating = False
If ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(LastMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(MiddleMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(FirstMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
End If
Application.ScreenUpdating = True
Else: ActiveChart.ChartType = xlBarStacked100
End If
End Sub
excel vba
asked Nov 21 '18 at 18:18
MariaEMariaE
11
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22
add a comment |
0
0
0
I can't get the macro to automatically run when an item in a slicer (from a chart) is selected. I tried by adding the code under a private sub or even public function but still can't get the click/selection of an item in the slicer to trigger the macro. I tested the sub Slicer_Opt and it works when I have an item selected and I run it manually, but as I said, I can't get it to automatically trigger when I select an item in the slicer. Until now I have the following:
Public Function ClickedSlicerItem(ByRef SlicerOpt1)
Call Slicer_Opt
End Function
Sub Slicer_Opt()
Dim LastMonth As String
Dim MiddleMonth As String
Dim FirstMonth As String
Dim Date1 As Date
Dim MonthMax As String
Dim MinMonth As Date
Dim SlicerMthCache As SlicerCache
Dim SlicerOpt1 As SlicerItem
Date1 = WorksheetFunction.Max(Sheets("Activity Log").Range("H:H"))
MinMonth = WorksheetFunction.Min(Sheets("Activity Log").Range("H:H"))
MonthMax = Month(Date1)
LastMonth = MonthName(MonthMax, True)
MiddleMonth = MonthName(MonthMax - 1, True)
FirstMonth = MonthName(MonthMax - 2, True)
If SlicerOpt1.ActiveItem Is Not Null Then
Application.ScreenUpdating = False
If ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(LastMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(MiddleMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(FirstMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
End If
Application.ScreenUpdating = True
Else: ActiveChart.ChartType = xlBarStacked100
End If
End Sub
excel vba
asked Nov 21 '18 at 18:18
MariaEMariaE
11
I can't get the macro to automatically run when an item in a slicer (from a chart) is selected. I tried by adding the code under a private sub or even public function but still can't get the click/selection of an item in the slicer to trigger the macro. I tested the sub Slicer_Opt and it works when I have an item selected and I run it manually, but as I said, I can't get it to automatically trigger when I select an item in the slicer. Until now I have the following:
Public Function ClickedSlicerItem(ByRef SlicerOpt1)
Call Slicer_Opt
End Function
Sub Slicer_Opt()
Dim LastMonth As String
Dim MiddleMonth As String
Dim FirstMonth As String
Dim Date1 As Date
Dim MonthMax As String
Dim MinMonth As Date
Dim SlicerMthCache As SlicerCache
Dim SlicerOpt1 As SlicerItem
Date1 = WorksheetFunction.Max(Sheets("Activity Log").Range("H:H"))
MinMonth = WorksheetFunction.Min(Sheets("Activity Log").Range("H:H"))
MonthMax = Month(Date1)
LastMonth = MonthName(MonthMax, True)
MiddleMonth = MonthName(MonthMax - 1, True)
FirstMonth = MonthName(MonthMax - 2, True)
If SlicerOpt1.ActiveItem Is Not Null Then
Application.ScreenUpdating = False
If ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(LastMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(MiddleMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Last_Log_in").SlicerItems(FirstMonth).Selected = True Then
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartType = xlBarClustered
End If
Application.ScreenUpdating = True
Else: ActiveChart.ChartType = xlBarStacked100
End If
End Sub
excel vba
excel vba
asked Nov 21 '18 at 18:18
MariaEMariaE
11
asked Nov 21 '18 at 18:18
MariaEMariaE
11
asked Nov 21 '18 at 18:18
MariaEMariaE
11
asked Nov 21 '18 at 18:18
MariaEMariaE
11
asked Nov 21 '18 at 18:18
MariaEMariaE
11
11
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22
add a comment |
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22
add a comment |
0
active
oldest
votes
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53418302%2frun-macro-when-slicer-change%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
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%2fstackoverflow.com%2fquestions%2f53418302%2frun-macro-when-slicer-change%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
Untested, but maybe see how to run a macro while clicking on a value in slicer
– BigBen
Nov 21 '18 at 18:22