Programattically add scrollbar with linked cell

[jdallen]

I need to add two scroll bars to each of five new sheets, each with an embedded chart.

The scrollbars will move a line on the chart by changing the "X" values, on a different sheet, of one of the lines on the chart.

I can add a scrollbar with code, but am so far unable to link it to a cell.

I do not want to use a template, where the scrollbars and plots always exist, and simply change the data. That's easy, yeah, but sloppy and memory wasteful, when each plot has roughly 7K datapoints.

Any suggestions?

 

[xlbo]

a quick google search gives this:

http://www.excelexpert.angelawolff.co.uk/scrolling.htm

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jdallen]

I appreciate the response. I already know how to do that, though. I need to put VB code in a module that embeds the scroll bars, then links them.

I can create them in VB, no problem. I am having problems referring to them to set the links up. I can do this with a form which is called from a command button on another page. I have done that, and I could do it again. I want to do it differently this time.

What I want to do, to repeat myself again, is (in VB code): 1. Create a new sheet. 2. Embed a chart which uses data from two other worksheets in the same workbook to make four XY Scatter plots. 3. put two scroll bars in to manipulate two of the XY scatter plots, separately.

I am at step number three. I can get the bars in there, but setting the scroll bar values - I am stuck.

I admit I always holler for help just before I figure out something for myself. I have several crayons, some of which are sharp. You may consider - the sharpest crayons are the nasty ones that you never use.

 

[xlbo]

What exactly are you stuck on ?

You say you are stuck on referring to the new contols but surely that would simply be a case of giving them an object variable:

Set ScrollBar1 = Sheets("SheetName").ScrollbarObj.Add

Can you not just follow the steps outlined in the link but do it programatically ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jdallen]

I am packing to leave for a week today, and I did not bring my flash drive home with the code I’m having trouble with, in it’s entirety, but I recorded this:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=237.75, Top:=137.25, Width:=102, Height:= _
25.5).Select

so you can see where I am. After that, I try and refer to the scrollbar using code I am familiar with, and I get errors. I’m not an object oriented person, just recently exposed to that type of stuff, and I feel like that is where I am erring. What I want to do is set a max, min, and link a cell to the scrollbar value.

Sorry, I’m in a rush or I’d have a better line on how to state the problem. I went ahead and banged out the code for a form that does what I wanted to do yesterday. I just didn’t want to do it that way this time, I wanted to use an embedded scrollbar that was added with VB.

 

[xlbo]

As a starter for 10 when you get back:

dim scrBar as object

Set ScrBar = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1", Link:=False, _
        DisplayAsIcon:=False, Left:=237.75, Top:=137.25, Width:=102, Height:= _
        25.5)

scrBar.Select

You may now work with the scrollbar object "scrBar"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question