none
Displaying contextual tabs in the ribbon with VBA RRS feed

  • Question

  • I have some VBA code that updates a chart and then makes the chart sheet visible and activates the chart. 

    But when I do so, the chart tools contextual tabs do not appear.

    The user apparently can't select and modify any chart features when the chart tools tabs aren't visible.

    My current workaround is (after the code has finished running) to select a different sheet and then re-select the chart sheet.  When this is done, the chart tools tabs automatically appear upon return to the sheet.

    How can I force the ribbon to recognize that a chart was activated in the VBA code, and/or display the appropriate contextual tabs?

    Wednesday, February 4, 2015 9:05 PM

Answers

  • I have no idea why chart activation inside a userform routine has this problem, but moving the activation out to the calling routine eliminated the issue.

    So a button calls the userform, and when that form is done, the button routine activates the chart.

    I'll still need to check the error traps; if the userform routine cancels, trying to activate the chart could cause a new problem.

    • Marked as answer by Pete Kies Wednesday, February 4, 2015 10:07 PM
    Wednesday, February 4, 2015 10:07 PM

All replies

  • Hello Pete,

    What code do you use to activate the chart? Could you be more specific?

    Wednesday, February 4, 2015 9:08 PM
  • The chart is its own sheet, and can be referred to either as Chart1 or Sheets("Detail").

    Here is the end of the routine, which is initiated from a Userform:

        Sheets("Detail").Visible = True
        Chart1.Activate
    86  Me.Hide
        Application.ScreenUpdating = True
    End Sub

    Wednesday, February 4, 2015 9:14 PM
  • I remarked out the screenupdating statements.  I see the same behavior whether these are in the code or not.
    Wednesday, February 4, 2015 9:16 PM
  • Try to call the Actovate method after all operations like hiding a form etc.
    Wednesday, February 4, 2015 9:19 PM
  • No difference if I switch the order of those statements.
    Wednesday, February 4, 2015 9:21 PM
  • It appears that the ribbon will not recognize what is selected and display contextual tabs if the selection occurs as part of VBA code.  Unless there is a command to force those tabs to appear, I think I can only get them by manually selecting something else and re-selecting the chart after the code is finished executing.

    Wednesday, February 4, 2015 9:23 PM
  • Although changing the order of the statements had no effect, you may be onto something with the userform.  I have 3 separate chart sheets in the workbook: 2 which are updated from userforms and one that updates from a button click.  The ones that are updated by userforms have this problem, but the one that's updated with a button click does not.  I suppose I could try unloading the userform...
    Wednesday, February 4, 2015 9:48 PM
  • Unloading the userform instead of hiding it makes no difference.

    I get the same problem with:

        Sheets("Detail").Visible = True
    86  Unload Me
        Application.ScreenUpdating = True
        Chart1.Activate
    End Sub

    I suppose I could try moving the chart activation out to the routine that initially shows the userform.  That is called by a button, which in the example of my third chart did not have this problem.

    • Marked as answer by Pete Kies Wednesday, February 4, 2015 10:02 PM
    • Unmarked as answer by Pete Kies Wednesday, February 4, 2015 10:03 PM
    Wednesday, February 4, 2015 10:00 PM
  • I have no idea why chart activation inside a userform routine has this problem, but moving the activation out to the calling routine eliminated the issue.

    So a button calls the userform, and when that form is done, the button routine activates the chart.

    I'll still need to check the error traps; if the userform routine cancels, trying to activate the chart could cause a new problem.

    • Marked as answer by Pete Kies Wednesday, February 4, 2015 10:07 PM
    Wednesday, February 4, 2015 10:07 PM