Excel VBA 最终用户选择图表对象

我想生成一些代码，允许最终用户从工作表中选择多个图表之一，之后我将根据该选择进行一系列操作。

我正在寻找类似于 Application.Inputbox Type:=8 的东西，它允许对象选择而不是范围选择。

我对不起眼的老VBA要求太多了吗？

首先选择图表，然后在选定的图表上运行代码，这比暂停代码并尝试从代码中选择图表要容易得多。

但这是可以完成的。

您需要一个用户表单，称为F_ChartChooser有两个按钮，btnCancel and btnContinue.

中的代码F_ChartChooser module:

Option Explicit

Private Sub btnCancel_Click()
  CancelProcedure
End Sub

Private Sub btnContinue_Click()
  ContinueProcedure
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  ' so clicking red X doesn't crash
  If CloseMode = 0 Then
    Cancel = True
    CancelProcedure
  End If
End Sub

常规模块中的代码包含一个必须从用户处获取图表的主程序。它必须以非模式方式调用用户窗体，以便用户可以选择工作表中的图表。这意味着代码在显示表单时继续运行，因此要做的就是在调用用户表单时结束子程序。

根据用户表单发生的情况，代码要么继续CancelProcedure或与ContinueProcedure。这是代码：

Option Explicit

Dim mfrmChartChooser As F_ChartChooser

Sub Main()
  ' code here

  ' need to select chart(s) here
  Application.Goto ActiveCell
  Set mfrmChartChooser = New F_ChartChooser
  mfrmChartChooser.Show vbModeless
End Sub

Sub CancelProcedure()
  Unload mfrmChartChooser
  Set mfrmChartChooser = Nothing
  MsgBox "User canceled.", vbExclamation
End Sub

Sub ContinueProcedure()
  Unload mfrmChartChooser
  Set mfrmChartChooser = Nothing
  If Not ActiveChart Is Nothing Then
    ' do something with active chart

    ' this demo is announcing that it was selected
    MsgBox """" & ActiveChart.ChartTitle.Text & """ was selected.", vbExclamation
    ' end of demo code
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    Dim sh As Shape
    Dim vCharts As Variant
    Dim nChart As Long
    ReDim vCharts(0 To nChart)
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        ' do something here with each chart

        ' this demo is building a list of selected charts
        nChart = nChart + 1
        ReDim Preserve vCharts(0 To nChart)
        vCharts(nChart) = sh.Chart.ChartTitle.Text
        ' end of demo code
      End If
    Next

    ' this demo now is showing the list of selected charts
    If nChart = 0 Then
      MsgBox "No chart selected.", vbExclamation
    Else
      If nChart = 1 Then
        MsgBox """" & vCharts(nChart) & """ was selected.", vbExclamation
      Else
        Dim sPrompt As String
        sPrompt = nChart & " charts selected:" & vbNewLine & vbNewLine
        Dim iChart As Long
        For iChart = 1 To nChart
          sPrompt = sPrompt & """" & vCharts(iChart) & """" & IIf(iChart < nChart, vbNewLine, "")
        Next
        MsgBox sPrompt, vbExclamation
      End If
    End If
    ' end of demo code
  Else
    ' do nothing because no chart was selected

    ' this demo is announcing that nothing was selected
    MsgBox "No chart selected.", vbExclamation
    ' end of demo code
  End If
End Sub

The CancelProcedure and ContinueProcedure上面的例程中有多余的代码只是为了帮助演示。在真实的代码中，我会像这样简化它们，甚至可能懒得在没有选择任何内容时通知用户（他们知道他们取消了，对吧？），并且只处理选定的图表：

Sub CancelProcedure()
  Unload mfrmChartChooser
  Set mfrmChartChooser = Nothing
End Sub

Sub ContinueProcedure()
  Unload mfrmChartChooser
  Set mfrmChartChooser = Nothing
  If Not ActiveChart Is Nothing Then
    ' do something with active chart
    ProcessChart ActiveChart
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    Dim sh As Shape
    Dim vCharts As Variant
    Dim nChart As Long
    ReDim vCharts(0 To nChart)
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        ' do something here with each chart
        ProcessChart sh.Chart
    Next
End Sub