How to invert scrollbar properties in Excel/VBA

[Ria]

I have excel line chart and a vertical scrollbar connected with it. Right now it is working fine. =.> scrolling up decrease the display range on chart (min & max values). And scrolling down increases the chart display range on chart (min & max values).

But sometime I get data and we want to display inversely and want: if scroll up then should increase the display range on chart (min & max values) and scrolling down decreases the chart display range (min & max values).

If I uncheck the option: VALUES IN REVERS ORDER (Format chart axis) then it reverse the values on chart (this part is right) but how to invert scrollbar properties I AM STUCK HERE (scroll should set to go down & decrease the value and scroll should to go up and increase value). Only option I can think is physically rotate the scrollbar at 360°. This option will not work because we get data and need to switch the option. I spent enough time and burn my mind. I need expert help please.

Attach I sample file. I can provide more details if needs.

 

[vletm]

A sample file.

 

[p45cal]

This is just a general suggestion because although I understand what you want to happen, because the scrollbar value is both read and written to in vba code and because its linked cell, BG1, is referred to in several places in the code and is referred to by at least one other cell on the sheet, it will take me some time to understand how BG1's value affects other things.
However, I'm thinking that instead of BG1 being directly affected by the scrollbar (through it being the scroll bar's linked cell), link it indirectly. Link the scroll bar value to a different cell, say cell A1, and then have a formula in cell BG1 which modifies cell A1's value, maybe, for example, at its most simple-stupid:
=-A1
Depending on which way you want the scroll bar to work, you could either have a different formula in cell BG1 (aircode):
=if(x ,-A1,A1)
of have the vba code either change the formula in BG1 or write a plain value to BG1.

 

[p45cal]

I've just seen @vletm's post and to demonstrate what I was saying in my last message, I've adjusted his file so that you can choose how the scroll bar behaves. I changed:

• The scroll bar's linked cell from H3 to I3
• Added a TRUE/FALSE data validation to cell J3 (to allow you to choose how the scroll bar operates)
• Added a formula to cell H3 (the original linked cell) to be =IF(J3,15-I3,I3)

Now you can just choose true/false in cell J3 to change how the scroll bar operates.

 

[Ria]

I've just seen @vletm's post and to demonstrate what I was saying in my last message, I've adjusted his file so that you can choose how the scroll bar behaves. I changed:

• The scroll bar's linked cell from H3 to I3
• Added a TRUE/FALSE data validation to cell J3 (to allow you to choose how the scroll bar operates)
• Added a formula to cell H3 (the original linked cell) to be =IF(J3,15-I3,I3)

Now you can just choose true/false in cell J3 to change how the scroll bar operates.

Thanks for response. Actually I my knowledge is basic and trying to learn as I hit the problem. BG1 is current value of scroll position. We can fill it indirectly either through vba or referencing other cell value. No rush, please help. Thanks.

 

[Ria]

A sample file.

Thanks. I will try this.

 

[p45cal]

Actually I my knowledge is basic and trying to learn as I hit the problem.

So this code is not written by you?

 

[Ria]

So this code is not written by you?

Some other expert helped me then I modified the code with my requirements. I spent months to understand and make it working for first step. Now I am stuck at next step. cell BG1 is connected with scrollbar and then scrollbar is connected with chart.

 

[p45cal]

In the attached, hopefully as you wanted scroll bar to behave:

• Linked cell changed to cell BG13
• VBA code changes, look for 'p45cal change in the code comments; one of them adds a formula to cell BG1.

Does this mess something else up? Very, very likely. I'll leave you to:

modify the code with my requirements

 

[Ria]

In the attached, hopefully as you wanted scroll bar to behave:

• Linked cell changed to cell BG13
• VBA code changes, look for 'p45cal change in the code comments; one of them adds a formula to cell BG1.

Does this mess something else up? Very, very likely. I'll leave you to:

Thanks. I will integrate it into my master file and will let you know if any question.

One more question I am struggling with: in same chart, How can vba read .value and .Xvalue of chart series. Reason of reading is to update the ranges when new data comes in.

 

[p45cal]

How can vba read .value and .Xvalue of chart series.

myXvalues = .SeriesCollection(1).XValues
myYvalues = .SeriesCollection(1).Values
but how that will help you update the ranges I don't know. Isn't that already done in the Worksheet_Activate() code? I don't know. I'm not going to code your entire project for you.

 

[Ria]

myXvalues = .SeriesCollection(1).XValues
myYvalues = .SeriesCollection(1).Values
but how that will help you update the ranges I don't know. Isn't that already done in the Worksheet_Activate() code? I don't know. I'm not going to code your entire project for you.

I have more charts on this sheet and need to update ranges with new data. stuck at reading ranges from collection. I keep getting message type mismatch or block with block missing. chatGPT wasted 2 days but could not read it. only able to read whole series but not range and niether can extract ranges from series.

 

[p45cal]

There is no built-in way for vba to get the ranges of a chart's x-values and y-values.
There's a very hit-and-miss way if the chart's series are simple:

zz = Split(.SeriesCollection(1).Formula, ",")
  Set xrange = Evaluate(zz(1))
  Set yrange = Evaluate(zz(2))

 

[Ria]

There is no built-in way for vba to get the ranges of a chart's x-values and y-values.
There's a very hit-and-miss way if the chart's series are simple:

zz = Split(.SeriesCollection(1).Formula, ",")
  Set xrange = Evaluate(zz(1))
  Set yrange = Evaluate(zz(2))

I am back again. This is a continuation of last issue. Last issue was fixed, no complaints. Thanks a lot for that. When tested with more data then it fails.

Here is the problem with updated statement. Excel line chart and a vertical scrollbar connected with it. Right now it is working fine (scrolling up increase the numbers and scrolling down decrease the numbers). Scrollbar is connected with cell: BF2 and with chart. It works when data range min & max value are positive but fails when data range: min values are below zero or negative numbers e.g. -175.

This is another situation when I get data and we have mixed data range (negative & positive numbers).

I spent enough time to manipulate the formula and burn my mind. I need expert help please. Only thing I figure out is scrollbar does not take negative values (less than zero).

Attach is sample file. I can provide more details if needs. No rush.

 

[p45cal]

It's going to take me far more time than I'm prepared to give to work out all the convolutions that your code goes through.
To help you to debug this properly, you MUST disable the On Error Resume lines and go through the code, line by line, with F8 on the keyboard.
Then you'll see that your code errors all over the place; you need to fix these first (for example, there's no Prog_Offsets sheet).

 

[Ria]

It's going to take me far more time than I'm prepared to give to work out all the convolutions that your code goes through.
To help you to debug this properly, you MUST disable the On Error Resume lines and go through the code, line by line, with F8 on the keyboard.
Then you'll see that your code errors all over the place; you need to fix these first (for example, there's no Prog_Offsets sheet).

Thanks for guiding. I adjusted the file based on sheets in here. Stepped through, no error but displaying negative values does not work. Scrollbar dos not take min values less than zero. Updated file is attached.

 

[Ria]

It's going to take me far more time than I'm prepared to give to work out all the convolutions that your code goes through.
To help you to debug this properly, you MUST disable the On Error Resume lines and go through the code, line by line, with F8 on the keyboard.
Then you'll see that your code errors all over the place; you need to fix these first (for example, there's no Prog_Offsets sheet).

Thanks a lot for help and time. Accidently, I found the solution.
Only changed the scrollbar settings to:
.ScrollBars("ScrollBar1").Min = 0 'its was set to: yy_min
.ScrollBars("ScrollBar1").max = yy_max + Abs(yy_min) 'it was set to: yy_max

Again thanks.