Hello,
First time user here.
Background: I have a two combo boxes that allows a user to pick performance indicators (PIs). Choosing different PIs updates a chart that has two axes. I want the chart axis formatting to change when a user selects a different PI from the drop down list (thus changing the linked cell). I am using Form Controls type combo boxes located on Sheet1, pointing to a list named PerfIndList on Sheet2 and linking to a cell named "IndFocus1" and a cell named "IndFocus2" on Sheet1.

Attempt at a solution: I tried the approach used when you want to run some code when a cell changes (in this case cell IndFocus1 and IndFocus2), but this approach doesn't work, probably because my cell is not actually activated and edited by the user. This is a simplified version of code I tried, only doing things based on changes to IndFocus1. This code does indeed work when I manually change the cell IndFocus1, without using the drop down:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim chosen_indicator As String
   
    If Target.Address = Range("IndFocus1").Address Then
          Application.EnableEvents = False
          chosen_indicator = Range("IndFocus1").Value

          Select Case chosen_indicator
                 Case 2, 3, 5, 6, 7, 8, 9, 10
                        ' do some things
                 Case 1, 4
                        ' do some other things
           End Select

           Application.EnableEvents = True
    End If
End Sub

During my search for a solution, some suggestions involved using a different type of combo box that doesn't use a cell link. I would like to keep my current type of combo box if I can, because I understand how to use what's in the linked cell to chose which data points to plot on the chart. I have never used any other type of combo box, which means I would need even more help on using a different type! Sorry I don't have an attachment, but I can make one if necessary.

Thank you,

Crystal