I'm trying to figure out how to do this in a user form and have no idea how
to do so/manipulate the output, so looking for suggestions.

I want to put out a list of 10 numbers, with some sort of separator as one
of the 'rows'. Then have a spinbutton which will move the separator up or
down a single row. The 'amateur' way would be to have a large label where I
put in 11 rows - 10 numbers with a '---------' as one of the rows. Then
redraw it everytime one of the spin buttons is pushed, moving the '--------'
row. But this is looks ugly.

I'm looking for suggestions on how to display the data on a user form in a
much nicer format - some way I can display 10 rows of numbers w/ some form of
separator which will move up or down. I could work something out w/ variable
size label boxes and some separator graphic line on a user form, but don't
know how to do this either.

Thanks,

Perry

P.S. For those who are curious, this is for a calculation which is being
done on each group of an ordered list of numbers - a calculated value will be
displayed for each group, and when the separator is moved/scrolled up or
down, the values for the top/bottom group will be recalculated. AFter the
user finds where they want the separator, based on the results of the
calculated values, they will hit an 'OK' button which will transfer all the
data to a spread sheet. I don't want to do this on a spread sheet because
there's no good area to do so, and I don't want to do so on a separate spread
sheet. Otherwise I could use the spin buttons to 'redraw' boarder styles on
a list. I'd really prefer to do this in a user form. I don't have
objections to using a list box or something similar, but again not sure how
to present the separator in this.

Thanks again.

Here is a way I came up with to do it:
Create 2 "big" empty labels (no text) for the background of your list. Set
both to have the same background color and they must have borders. Label1
will be a fixed size box surrounding the entire list. Label2 will sit on top
of that but will be a "resizable" label to surround only the top part of the
list. The top of Label2 should be right at the top of Label 1 and the width
should be the same. Hope this makes sense!

Now create the list over the top of these labels using either more labels or
textboxes. Use transparent background, no borders, no special effects
(flat). I will call these List1 through List10.

The trick is to resize Label2 so its bottom coincides with the bottom of the
last item in the "top" list or - easier to put in code - make it coincide
with the top of the first item in the "bottom" list. Here is a calculation to
do that where I am using i to identify the list item selected:
Me.Label2.Height = Me.Controls("List" & i).Top - Me.Label2.Top

So use your spinbutton to set the value of i (and don't allow it to go below
1 or above 10) and this should work. Hope this all makes sense - it can be
hard to describe in words only!
--
- K Dales


"pschmidt" wrote:


I'm trying to figure out how to do this in a user form and have no idea how
to do so/manipulate the output, so looking for suggestions.

I want to put out a list of 10 numbers, with some sort of separator as one
of the 'rows'. Then have a spinbutton which will move the separator up or
down a single row. The 'amateur' way would be to have a large label where I
put in 11 rows - 10 numbers with a '---------' as one of the rows. Then
redraw it everytime one of the spin buttons is pushed, moving the '--------'
row. But this is looks ugly.

I'm looking for suggestions on how to display the data on a user form in a
much nicer format - some way I can display 10 rows of numbers w/ some form of
separator which will move up or down. I could work something out w/ variable
size label boxes and some separator graphic line on a user form, but don't
know how to do this either.

Thanks,

Perry

P.S. For those who are curious, this is for a calculation which is being
done on each group of an ordered list of numbers - a calculated value will be
displayed for each group, and when the separator is moved/scrolled up or
down, the values for the top/bottom group will be recalculated. AFter the
user finds where they want the separator, based on the results of the
calculated values, they will hit an 'OK' button which will transfer all the
data to a spread sheet. I don't want to do this on a spread sheet because
there's no good area to do so, and I don't want to do so on a separate spread
sheet. Otherwise I could use the spin buttons to 'redraw' boarder styles on
a list. I'd really prefer to do this in a user form. I don't have
objections to using a list box or something similar, but again not sure how
to present the separator in this.

Thanks again.

This is good - I was wondering if there was a way to dynamically resize a
label box but didn't know how. Now I have an idea.

I like the idea, and think it's doable, and accomplishes what I want nicely.
The only question or thing I'm not sure about is in the calculation, exactly
what the 2nd part does:

Me.Label2.Height = Me.Controls("List" & i).Top - Me.Label2.Top
^^^^^^^^^^^^^^^^^

Not sure what the .controls is returning or the ("list" &i) does. (Being
mostly a C/C++ programmer background, I look at this and assume bit-wise
and...and w/ VBE, I'm sure that's not what it's doing. I'm relatively new to
VBE... :)

But I understand what you're doing, and was thinking along similar lines -
just don't know how to pull some of these value (like box.top) and resize
them. But so far very helpful! Gives me direction on what to play with.

Thanks!

(And could you explain that one part of your calculation a bit more? Thanks.)


"K Dales" wrote:

Here is a way I came up with to do it:
Create 2 "big" empty labels (no text) for the background of your list. Set
both to have the same background color and they must have borders. Label1
will be a fixed size box surrounding the entire list. Label2 will sit on top
of that but will be a "resizable" label to surround only the top part of the
list. The top of Label2 should be right at the top of Label 1 and the width
should be the same. Hope this makes sense!

Now create the list over the top of these labels using either more labels or
textboxes. Use transparent background, no borders, no special effects
(flat). I will call these List1 through List10.

The trick is to resize Label2 so its bottom coincides with the bottom of the
last item in the "top" list or - easier to put in code - make it coincide
with the top of the first item in the "bottom" list. Here is a calculation to
do that where I am using i to identify the list item selected:
Me.Label2.Height = Me.Controls("List" & i).Top - Me.Label2.Top

So use your spinbutton to set the value of i (and don't allow it to go below
1 or above 10) and this should work. Hope this all makes sense - it can be
hard to describe in words only!
--
- K Dales


"pschmidt" wrote:


I'm trying to figure out how to do this in a user form and have no idea how
to do so/manipulate the output, so looking for suggestions.

I want to put out a list of 10 numbers, with some sort of separator as one
of the 'rows'. Then have a spinbutton which will move the separator up or
down a single row. The 'amateur' way would be to have a large label where I
put in 11 rows - 10 numbers with a '---------' as one of the rows. Then
redraw it everytime one of the spin buttons is pushed, moving the '--------'
row. But this is looks ugly.

I'm looking for suggestions on how to display the data on a user form in a
much nicer format - some way I can display 10 rows of numbers w/ some form of
separator which will move up or down. I could work something out w/ variable
size label boxes and some separator graphic line on a user form, but don't
know how to do this either.

Thanks,

Perry

P.S. For those who are curious, this is for a calculation which is being
done on each group of an ordered list of numbers - a calculated value will be
displayed for each group, and when the separator is moved/scrolled up or
down, the values for the top/bottom group will be recalculated. AFter the
user finds where they want the separator, based on the results of the
calculated values, they will hit an 'OK' button which will transfer all the
data to a spread sheet. I don't want to do this on a spread sheet because
there's no good area to do so, and I don't want to do so on a separate spread
sheet. Otherwise I could use the spin buttons to 'redraw' boarder styles on
a list. I'd really prefer to do this in a user form. I don't have
objections to using a list box or something similar, but again not sure how
to present the separator in this.

Thanks again.

I deliberately named the list items with a common prefix and numeric suffix
so I could use them with a numeric index as a "pointer" to the correct list
item. Me is, of course, the userform, the Controls collection is a
collection of all controls on the form (which includes command buttons, text
boxes, etc... including labels). So if i is the number set by your routine
to indicate a particular item in the list, the label containing its value is
named "List" & i (in VBA the & concatenates strings).

Hope this explains.
--
- K Dales


"pschmidt" wrote:


This is good - I was wondering if there was a way to dynamically resize a
label box but didn't know how. Now I have an idea.

I like the idea, and think it's doable, and accomplishes what I want nicely.
The only question or thing I'm not sure about is in the calculation, exactly
what the 2nd part does:

Me.Label2.Height = Me.Controls("List" & i).Top - Me.Label2.Top
^^^^^^^^^^^^^^^^^

Not sure what the .controls is returning or the ("list" &i) does. (Being
mostly a C/C++ programmer background, I look at this and assume bit-wise
and...and w/ VBE, I'm sure that's not what it's doing. I'm relatively new to
VBE... :)

But I understand what you're doing, and was thinking along similar lines -
just don't know how to pull some of these value (like box.top) and resize
them. But so far very helpful! Gives me direction on what to play with.

Thanks!

(And could you explain that one part of your calculation a bit more? Thanks.)


"K Dales" wrote:

Here is a way I came up with to do it:
Create 2 "big" empty labels (no text) for the background of your list. Set
both to have the same background color and they must have borders. Label1
will be a fixed size box surrounding the entire list. Label2 will sit on top
of that but will be a "resizable" label to surround only the top part of the
list. The top of Label2 should be right at the top of Label 1 and the width
should be the same. Hope this makes sense!

Now create the list over the top of these labels using either more labels or
textboxes. Use transparent background, no borders, no special effects
(flat). I will call these List1 through List10.

The trick is to resize Label2 so its bottom coincides with the bottom of the
last item in the "top" list or - easier to put in code - make it coincide
with the top of the first item in the "bottom" list. Here is a calculation to
do that where I am using i to identify the list item selected:
Me.Label2.Height = Me.Controls("List" & i).Top - Me.Label2.Top

So use your spinbutton to set the value of i (and don't allow it to go below
1 or above 10) and this should work. Hope this all makes sense - it can be
hard to describe in words only!
--
- K Dales


"pschmidt" wrote:


I'm trying to figure out how to do this in a user form and have no idea how
to do so/manipulate the output, so looking for suggestions.

I want to put out a list of 10 numbers, with some sort of separator as one
of the 'rows'. Then have a spinbutton which will move the separator up or
down a single row. The 'amateur' way would be to have a large label where I
put in 11 rows - 10 numbers with a '---------' as one of the rows. Then
redraw it everytime one of the spin buttons is pushed, moving the '--------'
row. But this is looks ugly.

I'm looking for suggestions on how to display the data on a user form in a
much nicer format - some way I can display 10 rows of numbers w/ some form of
separator which will move up or down. I could work something out w/ variable
size label boxes and some separator graphic line on a user form, but don't
know how to do this either.

Thanks,

Perry

P.S. For those who are curious, this is for a calculation which is being
done on each group of an ordered list of numbers - a calculated value will be
displayed for each group, and when the separator is moved/scrolled up or
down, the values for the top/bottom group will be recalculated. AFter the
user finds where they want the separator, based on the results of the
calculated values, they will hit an 'OK' button which will transfer all the
data to a spread sheet. I don't want to do this on a spread sheet because
there's no good area to do so, and I don't want to do so on a separate spread
sheet. Otherwise I could use the spin buttons to 'redraw' boarder styles on
a list. I'd really prefer to do this in a user form. I don't have
objections to using a list box or something similar, but again not sure how
to present the separator in this.

Thanks again.