Dynamic updating of linked ControlSource cells

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.

Please Login or Register  to view this content.

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).

Please Login or Register  to view this content.

Can anyone help me with the correct way to do this?

Many thanks,
Ad

Originally Posted by snb

Do you mean optionbuttons when mentioning 'tickboxes' ?

There's no need to use controlsource; you can refer to the optionbutton value itself.

Well, I have OptionButtons and CheckBoxes in my UserForm... Are you saying that I can refer to these objects directly in my Excel formulae?

Thx
AdLoki

Originally Posted by snb

Only if you let VBA produce your charts (and why would'nt you if you are using a userform ?)

Unfortunately i only started working with VBA last week and I don't really have the time to learn how to code charts right now. If you know of any good online guidance or tutorials for doing so, I'd be inetrested to look into it at a later date but, right now, I need to finalise this project... I guess I'll just have to put the code in my OP into each routine separately, I just thought there would be a more elegant, dynamic way of referencing it.

Many thanks for your input.