multiple scroll areas on sheet?? and combo box values
 

Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve

multiple scroll areas on sheet?? and combo box values
 

I assume we're talking about ActiveX controls from the Controls
Toolbox toolbar. Here are some ideas:

Scrollbars: In a new blank workbook, place a scrollbar (named
ScrollBar1 by default) on Sheet1 so that it fits the range B5:G5. Turn
off design mode (setsquare button on Controls Toolbox). Put the
following code in the Sheet1 code module:

'<In Sheet1 code module------------
Option Explicit

Private Const lngITEMS As Long = 19
Private Const strRNG As String = "B4:G4"

Private Sub ScrollBar1_Change()

Dim vntArray As Variant
Dim lngCounter As Long

ScrollBar1.Max = 14

vntArray = NaturalLongsArray(lngITEMS)

For lngCounter = 0 To (Sheet1.Range(strRNG).Cells.Count - 1)

Sheet1.Range(strRNG).Cells(1, lngCounter + 1) = _
vntArray(lngCounter + Sheet1.ScrollBar1.Value)

Next

End Sub

Private Function NaturalLongsArray(ByVal UpperBound As Long) _
As Variant

Dim alngValues() As Long
Dim lngCounter As Long

ReDim alngValues(UpperBound)

For lngCounter = 0 To UpperBound
alngValues(lngCounter) = lngCounter
Next

NaturalLongsArray = alngValues

End Function
'</In Sheet1 code module-----------

Scroll the scrollbar and see the effect. This is a simple example but
I hope you can see the potential of creating your own scrollable
ranges.

Combobox: Enable the combo in the Workbook_Open event, populate the
combo in its _DropButtonClick event and disable the combo in its
_Click event e.g.

'<In ThisWorkbook code module------
Option Explicit
Private Sub Workbook_Open()
Sheet1.ComboBox1.Enabled = True
End Sub
'</In ThisWorkbook code module-----

'<In Sheet1 code module------------
Option Explicit
Private Sub ComboBox1_DropButtonClick()
ComboBox1.List = Array("One", "Day", "When")
End Sub

Private Sub ComboBox1_Click()
ComboBox1.Enabled = False
End Sub
'</In Sheet1 code module-----------

--

(foamfollower) wrote in message . com...
Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve

multiple scroll areas on sheet?? and combo box values
 

There is only one scrollarea setting per worksheet. You could probably use
the selection change event to adjust the scrollarea to achieve what you
want.

--
Regards,
Tom Ogilvy

"foamfollower" wrote in message
om...
Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve

multiple scroll areas on sheet?? and combo box values
 

foamfollower,
How about using Panes on the window. You can either set the splits either
beforehand or in code.
Depending on the amount of screen space you have to play with and the amount
of data required, this may work.
You would still have to restrict movement outside those areas, on the
Selection_Change event maybe.
Removing the scroll bars for that windows would help as well.

NickHK


"foamfollower" wrote in message
om...
Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve

multiple scroll areas on sheet?? and combo box values
 

foamfollower,
Regarding your cbo problem, look into the ListFillRange and LinkedCell
properties of the cbo.

NickHK


"foamfollower" wrote in message
om...
Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve

multiple scroll areas on sheet?? and combo box values
 

Thanks for the help.
combo box thing is fixed now. all i needed was, i guess, to use the
'array' method of defining combobox entries. the 'additem' method
wasn't working the same way. works great now.

as for the scrollarea issue:
You guys will have to forgive me. it will take me some time to figure
out how to adapt those methods to be used with cell ranges. I am
unfamiliar with both,
but will certainly try. any help is welcome.

Steve
"Tom Ogilvy" wrote in message ...
There is only one scrollarea setting per worksheet. You could probably use
the selection change event to adjust the scrollarea to achieve what you
want.

--
Regards,
Tom Ogilvy

"foamfollower" wrote in message
om...
Hello,
My first question is hopefully an easy one:
is there a way to set multiple scroll areas on one sheet. i'm
building "The Big Sheet" among other worksheets in the workbook. i'd
like the user to use navigation buttons and hyperlinks to get around
and ALSO to limit the ScrollArea in each little part of the sheet. If
there is a way to link the ranges, i can't figure it out.
Thanks on that question.

I also have many combo boxes. i can't figure out how to populate the
boxes
once, and then stay that way throughout the life of the file.
All i have is a button to press to populate the boxes using AddItem's,
etc.
which works good, but i don't want to have to use a button. I also
don't want the user's selection to disappear when they click on list
button a second time.
I guess my question is, how can i set the combobox values permanently.
So, when the user opens my template application, the boxes are
populated and in there "initialized" state. But, from then on, after
SaveAs, and multiple open/closes, the boxes stay the way the user left
them. At the end of
file use, the last step will copy the combobox values where needed.
It seems simple, what event to use: worksheet_click (then, all boxes
are zeroed
when populated)
I may need an IF statement to clear and populate the comboboxes ONLY
if
they are empty??

Greatly appreciate any Help on either of these?
Thanks!

Steve