Trying to use 1 axtive x button to select 1 of 2 charts.

[Frncis]

I am using MS excel 365. I am trying to develop code to call a particular clustered column chart. Both the raw data & the chart are on the same worksheet "Unique_Vets". A friend tried to help me find code to select & call a chart from one button. I have googeled & have found code to create, copy, print charts, but none to accomplish my goal. A friend tried to help me find code to select & call a chart from one button. However when we ran the code stated below:

Dim MyValue
Dim i As String
'MsgBox prompt:="Do you want Veterans Seen Monthly or Quarterly?",Title:="Voc. Rehab. - Career Link"
i = MsgBox("Do you want Veterans Seen Monthly or Quarterly?",vbYesNo, "Voc. Rehab. - Career Link")

If Not i = vbYes Then Exit Sub

'First message shows in the body of the box, message 2 shows at the top of thebox.
Do
MyValue = Application.InputBox("Click Ok or Cancel after yourSelection!" & vbCrLf & _
"1 = Monthly Chart" & vbCrLf & _
"2 = Quarterly Chart", "Voc. Rehab - Career Link DataEntry")
' Sub messaage box exit.
If MyValue = False Then
Exit Sub
ElseIf (MyValue = 1) Or (MyValue = 2) Then
Exit Do
Else
MsgBox "You have not made a valid entry. Please try again.",vbInformation, "Voc. Rehab. - Career Link"
End If
Loop
'Code to Execute When Condition = value_1
Select Case MyValue
Case 1

ChartObjects("Veterans Seen Quarterly").Visible = False
ChartObjects("Veterans Seen Monthly").Visible = True

'Code to Execute When Condition = value_2
Case 2

ChartObjects("Veterans Seen Monthly").Visible = False
ChartObjects("Veterans Seen Quarterly").Visible = True
End sub

IF you selected 1. Nothing happened (literally nothing). I you selected 2 The charts disappeared (as in gone). the 1st chart is located @ K28, width is K28: P28, height is K28:K45. The second chart is located @ K79, width is K79: P79, height is K79:K 95.
I found a work around but it is messy :

Sub Vets_Seen_Charts()
   Dim MyValue
    Dim i As String
    
   'MsgBox prompt:="Continue to October Career Link Meeting List?", Title:="Voc. Rehab. - Career Link"
   i = MsgBox("Do you want the Vets seen Monthly or Quartrly Chart?", vbYesNo, "Voc. Rehab. - Career Link")
   
   If Not i = vbYes Then Exit Sub
   
   'First message shows in the body of the box, message 2 shows at the top of the box.
   Do
       MyValue = Application.InputBox("Which chart doyou want?" & vbCrLf & _
                              "1 = Vets seen Mounthly" & vbCrLf & _
                              "2 = Vets seen Quarterly" & vbCrLf, "Voc. Rehab. - Career Link")
       ' Sub messaage box exit.
       If MyValue = False Then
           Exit Sub
       ElseIf (MyValue = 1) Or (MyValue = 2) Then
           Exit Do
       Else
           MsgBox "You have not made a valid entry.  Please try again.", vbInformation, "Voc. Rehab. - Career Link"
       End If
   Loop    'Code to Execute When Condition = value_1
   Select Case MyValue
       Case 1
                    If ActiveSheet.Range("A28").Select Then
       
                   Else
                       End If
       'Code to Execute When Condition = value_2
       Case 2
                    If ActiveSheet.Range("A79").Select Then

                   Else
                   
                   End If
           End Select
End Sub

Any help is appreciated.

 

[Chihiro]

Welcome to the forum.

It would help, if you upload desensitized sample workbook, containing enough data to demonstrate your issue.

 

[Frncis]

Hopefully that works. I tried originally to upload & I was told the file was too large. The button titled Vets seen is the first chart. Once we get the proper code, I can use for the other charts. Again Thank you!

 

[Chihiro]

Something like below?

Sub vetseen()
Dim MyValue
Dim i As String
'MsgBox prompt:="Do you want Veterans Seen Monthly or Quarterly?",Title:="Voc. Rehab. - Career Link"
i = MsgBox("Do you want Veterans Seen Monthly or Quarterly?", vbYesNo, "Voc. Rehab. - Career Link")

If Not i = vbYes Then Exit Sub

'First message shows in the body of the box, message 2 shows at the top of thebox.
Do
    MyValue = Application.InputBox("Click Ok or Cancel after yourSelection!" & vbCrLf & _
            "1 = Monthly Chart" & vbCrLf & _
            "2 = Quarterly Chart", "Voc. Rehab - Career Link DataEntry")
    ' Sub messaage box exit.
    If MyValue = False Then
        Exit Sub
    ElseIf (MyValue = 1) Or (MyValue = 2) Then
        Exit Do
    Else
        MsgBox "You have not made a valid entry. Please try again.", vbInformation, "Voc. Rehab. - Career Link"
    End If
Loop
'Code to Execute When Condition = value_1
Select Case MyValue
    Case 1
        ActiveSheet.ChartObjects("Veterans Seen Quarterly").Visible = False
        ActiveSheet.ChartObjects("Veterans Seen Monthly").Visible = True
        [K29].Activate
'Code to Execute When Condition = value_2
    Case 2
        ActiveSheet.ChartObjects("Veterans Seen Monthly").Visible = False
        ActiveSheet.ChartObjects("Veterans Seen Quarterly").Visible = True
        [K79].Activate
End Select
End Sub

 

[Frncis]

Exactly what I was trying to accomplish.
Chihiro Thanks for both your assistance & patiently teaching me!!!

 

[Frncis]

Chihiro I just noticed that when I call 1 of the charts, the other one is there, but not visible. Is it possible to call a chart & still have the other chart remain visible?

 

[Chihiro]

Then try something like below.

Sub vetseen()
Dim MyValue
Dim i As String
'MsgBox prompt:="Do you want Veterans Seen Monthly or Quarterly?",Title:="Voc. Rehab. - Career Link"
i = MsgBox("Do you want Veterans Seen Monthly or Quarterly?", vbYesNo, "Voc. Rehab. - Career Link")

If Not i = vbYes Then Exit Sub

'First message shows in the body of the box, message 2 shows at the top of thebox.
Do
    MyValue = Application.InputBox("Click Ok or Cancel after yourSelection!" & vbCrLf & _
            "1 = Monthly Chart" & vbCrLf & _
            "2 = Quarterly Chart", "Voc. Rehab - Career Link DataEntry")
    ' Sub messaage box exit.
  If MyValue = False Then
        Exit Sub
    ElseIf (MyValue = 1) Or (MyValue = 2) Then
        Exit Do
    Else
        MsgBox "You have not made a valid entry. Please try again.", vbInformation, "Voc. Rehab. - Career Link"
    End If
Loop
'Code to Execute When Condition = value_1
Select Case MyValue
    Case 1
        Application.Goto ActiveSheet.ChartObjects("Veterans Seen Monthly").TopLeftCell, True
'Code to Execute When Condition = value_2
  Case 2
        Application.Goto ActiveSheet.ChartObjects("Veterans Seen Quarterly").TopLeftCell, True
End Select
End Sub