Hi,
For the first question, I believe you should use a pivot table and use an expression for the last column (=only([Column C]) for example)
or you can include an expression like ' ' and make the column very small...
I found the second question very interesting. I may depend a little bit on what you want to do.
I tought that the user would use the chart to make a selection in a field. I would use that selection to choose another sheet to be activated.
I also tought that the user could make the same selection on other place in the interface but I would not want to send him to another sheet.
My approach was:
- When there is a selection in a specific field (Group, for example), test if the users is in the right sheet.
- If he is in the right sheet, test if it is the right object (the chart)
- If it is the right chart, test if there is one an only one selection in the field (Group)
- If there is only one selection, check the selected value to send the user to the right sheet.
That the macro I came up with. I included many message boxes, so you can see what I am testing
sub gotoSheet
if ActiveDocument.Sheets(0).IsActive then
rem ** actions when macro triggered from first sheet **
msgbox("You are in sheet 0")
else
msgbox("You are not sheet 0")
end if
set s0 = ActiveDocument.GetSheetObject("CH01")
if s0.isActive then
msgbox ("The right chart is active")
set mySelections = ActiveDocument.fields("GROUP").GetSelectedValues
IF mySelections.Count =1 THEN
MSgBOX "There is only one value selected"
msgbox "Moving to other sheet..."
msgbox "The Selected Group is " & mySelections.Item(0).text
if mySelections.Item(0).text="A" then
ActiveDocument.Sheets("Group A").Activate
end if
if mySelections.Item(0).text="B" then
ActiveDocument.Sheets("Group B").Activate
end if
else
msgbox "There is more than one value selected"
end if
else
msgbox ("The right chart is not active")
end if
end sub
I also included the app on my post
Hope it helps,
Erich
