Hi All,

I have a problem. I have a UserForm with tickboxes, each of which is linked to a unique cell via the ControlSource property. The value of each respective cell (TRUE/FALSE) then dictates how data are displayed in a variety of charts.

The problem is that when I click a tickbox the charts are always out of step. For instance, if I check the date '2009/10', the data for this year do not appear on the charts until I tick another box (the data for which are not displayed until I check a third box, and so on).

I can include the following code for each tickbox routine, which solves the problem.

Private Sub Year1_Click()
    Sheets("Dashboard").Range("H3") = Year1 
         'Manually updates the ControlSource cell with the value of the check box
    Sheets("CHART DATA").Calculate
         'Updates the tables that the charts read from
    Application.Run "HZ_Filter"
         'Runs the filter to update the chart displays
End Sub
Rather than include the first two lines for each tickbox routine (there are 30) I want to write a macro that will dynamically update the cell cited in the ControlSource property with the value of whichever tickbox is active. I was thinking something akin to the following but I do not know how to correctly write the VBA syntax (apologies as the following is a mishmash of VBA and Excel formulae).

Sub tBox_UD()
    Dim Str_Source As String
    Dim Rge_Source As Range
    
    Str_Source = Right(Active.ControlSource, _
    Find("!", Active.ControlSource) - Len(Active.ControlSource) - 1)
        'Sets the variable as the cell reference in the ControlSource property
    
    Set Rge_Source = Range("Str_Source")
        'Sets this as the cell to look update
    
    Sheets("Dashboard").Rge_Source = Active
        'Updates the cell with the value of the active tickbox (TRUE/FALSE)
    
    Sheets("CHART DATA").Calculate
End Sub
Can anyone help me with the correct way to do this?

Many thanks,
Ad