Ok, so I'm real new to programming in Excel (VB) (new = Wednesday
But, I'm trying to write code that will display one of 4 charts in Excel depending on a user selection. A scroll-bar type thing with 4 options that I could slide a bar between seems to be the most logical, but I'm guessing Excel can't do slide-bars. I have the code for the visibility of the charts, that was trivial -- but how do I go about implementing a selection? If a slide-bar is not viable, how can I write something so that a user can input a value and the appropriate chart is immediately displayed?

I've programmed in other languages, but this is my first encounter with Excel/VB interaction. So, I don't know how to make a macro automatically run or anything like that

Thanks so much for your patience/help

WintonCW

Not sure exacly what you mean by slide bar; however a list
box or combo box will do what you want. Both give you an
input box with the "arrow" button at the end, pressing the
button displays a list of options (with a scroll bar if
the list is long enough) for the user to select.
Difference between the two: list box means user has to use
list, in a combo box you can also begin typing and it will
find the entry that starts with whatever letters you have
typed. For full info look in Help or search the MSDN
library.

You can set one up easily, though, with minimal coding.
With the View menu, choose toolbars and make sure the
Forms toolbar is displayed. Select the list box or combo
box from the toolbox (icons sorta look like them...
sorta). Draw a rectangle on your worksheet and there it
is. Right click on it, choose Format Control. Various
options in there, but on the Control tab using Input Range
you can link the list that displays to a range of cells on
your worksheet (to give you the options in the list) and
using Cell Link you can also link the output to a
particular cell.

Now build your charts - all of them - in Excel. In VBA
you refer to them like this:

Sheets(Sheetname).ChartObjects(Chartnumber)

(where Chartnumber is an index number; 1 for the first
chart created, 2 for the next, ...) A ChartObject has a
Visible property - use this to hide it from the user or
display it. You want to do this in conjunction with a
change in the list/combo box you created.

If you right-click on your list/combo box again, you can
Assign Macro... Do this and choose New. Write your code,
something like the following (I am assuming the Cell Link
is the cell A1 on Sheet1, and it has a number 1-4 for the
chart you want to display - which is also on Sheet1):

Sub DropDown1_Change()

Dim iChart as Integer

For iChart = 1 to 4

Sheets("Sheet1").ChartObjects(iChart).Visible =
(iChart=Sheets("Sheet1").Range("A1").Value))

Next iChart

End Sub

Hope this little tutorial helps...

K Dales

-----Original Message-----
Ok, so I'm real new to programming in Excel (VB) (new =
Wednesday)
But, I'm trying to write code that will display one of 4
charts in Excel depending on a user selection. A scroll-
bar type thing with 4 options that I could slide a bar
between seems to be the most logical, but I'm guessing
Excel can't do slide-bars. I have the code for the
visibility of the charts, that was trivial -- but how do I
go about implementing a selection? If a slide-bar is not
viable, how can I write something so that a user can input
a value and the appropriate chart is immediately
displayed?

I've programmed in other languages, but this is my first
encounter with Excel/VB interaction. So, I don't know how
to make a macro automatically run or anything like that.

Thanks so much for your patience/help!

WintonCW
.

Thank you so much for your help! That is pretty close to what I was
wanting to do.

By slide bar, I mean a horizontal bar (in this case) with a slider on
it. the slider can be moved left to right and different actions are
taken as a result. It's much like the boxes you mentioned, only without
the drop-down quality (one can see all the options at once and it's just
a matter of moving the slider to the value one wants). Any chance excel
can pull that off? If not, I'll go with what you just told me.

Thanks e'er so much!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

My demography workbook at
http://sunsite.univie.ac.at/Projects/demography/
has examples for slide bars and pull down menus
even without VBA.




K Dales wrote:
Not sure exacly what you mean by slide bar; however a list
box or combo box will do what you want. Both give you an
input box with the "arrow" button at the end, pressing the
button displays a list of options (with a scroll bar if
the list is long enough) for the user to select.
Difference between the two: list box means user has to use
list, in a combo box you can also begin typing and it will
find the entry that starts with whatever letters you have
typed. For full info look in Help or search the MSDN
library.

You can set one up easily, though, with minimal coding.
With the View menu, choose toolbars and make sure the
Forms toolbar is displayed. Select the list box or combo
box from the toolbox (icons sorta look like them...
sorta). Draw a rectangle on your worksheet and there it
is. Right click on it, choose Format Control. Various
options in there, but on the Control tab using Input Range
you can link the list that displays to a range of cells on
your worksheet (to give you the options in the list) and
using Cell Link you can also link the output to a
particular cell.

Now build your charts - all of them - in Excel. In VBA
you refer to them like this:

Sheets(Sheetname).ChartObjects(Chartnumber)

(where Chartnumber is an index number; 1 for the first
chart created, 2 for the next, ...) A ChartObject has a
Visible property - use this to hide it from the user or
display it. You want to do this in conjunction with a
change in the list/combo box you created.

If you right-click on your list/combo box again, you can
Assign Macro... Do this and choose New. Write your code,
something like the following (I am assuming the Cell Link
is the cell A1 on Sheet1, and it has a number 1-4 for the
chart you want to display - which is also on Sheet1):

Sub DropDown1_Change()

Dim iChart as Integer

For iChart = 1 to 4

Sheets("Sheet1").ChartObjects(iChart).Visible =
(iChart=Sheets("Sheet1").Range("A1").Value))

Next iChart

End Sub

Hope this little tutorial helps...

K Dales


-----Original Message-----
Ok, so I'm real new to programming in Excel (VB) (new =

Wednesday)

But, I'm trying to write code that will display one of 4

charts in Excel depending on a user selection. A scroll-
bar type thing with 4 options that I could slide a bar
between seems to be the most logical, but I'm guessing
Excel can't do slide-bars. I have the code for the
visibility of the charts, that was trivial -- but how do I
go about implementing a selection? If a slide-bar is not
viable, how can I write something so that a user can input
a value and the appropriate chart is immediately
displayed?

I've programmed in other languages, but this is my first

encounter with Excel/VB interaction. So, I don't know how
to make a macro automatically run or anything like that.

Thanks so much for your patience/help!

WintonCW
.

That demographic thing is exactly what I was looking for. The only
problem is -- how in the world did you do it? I couldn't find "source
code" or whatever it would be called for those slidebars. That's what
I'm going for, so your help would be greatly appreciated!

Nice job on the sheet, btw -- looks nice.

Winton



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!