Updating a dynamic range for a chart

[VBAnewb2master]

I am trying to have a excel chart that would be controled by a scollbar and combobox
I have an existing chart with dynamic ranges so for example dataSetA is defined as

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.63636302947998px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">     =OFFSET([COLOR=#808080]'Starting Sheet'!$B$2,0,0,37,1)[/COLOR]</code>

and I reference this range in the chart with the following

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.63636302947998px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">     =trans_test.xls!filmThickness</code>

so I have my scrollbar and combobox drawn and named and I am trying to fill out the vba for it. Since I have a named range I was just trying to edit that value with the following and hoping Excel would do the rest but it is not working. My code snip is below. I was hoping to just have the range updated and it would filter through to the chart.

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.63636302947998px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">[COLOR=#00008B]Dim[/COLOR] timePosition [COLOR=#00008B]As[/COLOR] MSForms.ScrollBar
[COLOR=#00008B]Dim[/COLOR] dataSet [COLOR=#00008B]As[/COLOR] MSForms.ComboBox
[COLOR=#00008B]Set[/COLOR] timePosition = ActiveSheet.timeControl
[COLOR=#00008B]Set[/COLOR] dataSet = ActiveSheet.itemList
[COLOR=#00008B]Dim[/COLOR] datavalues [COLOR=#00008B]As[/COLOR] Range
[COLOR=#00008B]Set[/COLOR] datavalues = Range(Range([COLOR=#800000]"B1$1"[/COLOR]), Range([COLOR=#800000]"C22"[/COLOR]).Offset(timePosition.Value, [COLOR=#800000]0[/COLOR]).[COLOR=#00008B]End[/COLOR](xlT</code>oRight))

I want to be able to use the combobox to move from sheet to sheet and the scroll bar to move between datasets on that sheet. I just need a general idea how I can alter the offset formula that I used to first define the range. If I could update that I can make this work. Thanks in advance.

 

[Worf]

Hello and welcome to the Board

See if the following page is useful, and tell us if you need further help adapting it to your project:

Dynamic Chart Source Data - Peltier Tech Blog