Solved: Userform to Display a Range

[compariniaa]

I have a userform that asks the user for an exchange rate and the dates the exchange rate applies to. Then the userform dumps that info into a sheet.

When the user clicks "OK - I'm Done" (a button), I'd like another userform to pop up showing the user the values and dates he/she entered and ask for confirmation to proceed.

Unfortunately, I cannot create a fixed number of labels/text boxes because, depending on the dates specified, there may be only 1 exchange rate or more, so the userform would have to be dynamic.

it doesn't seem like it would be too hard to do because the info has already been dumped into a sheet and the userform would then just take the proper range's values

I've searched everywhere online and I can't find any examples to learn from. Can anybody help?

[lucas]

Something like this to get you started:

Label1.Caption = Range("C5").Value

[compariniaa]

Hi lucas,
that's the idea, but the trick (what's stumping me) is how to do that with an unknown number of items. the range to show on the userform could be Range("C5:C20") one time and Range("C5:C30") another time.

and it doesn't necessarily have to be a label, a textbox would work too.

[OBP]

If it always starts at C5 and there is at least one blank row under your range then it shouldn't be a problem, put them in to a "List" box. build the list box source as you step through the range. I do not know Excel list boxes, but in Access it s easy.

[compariniaa]

what if the range to show on the form was on more than one column, such as C5 to D10? would that affect anything?

[OBP]

Andre, I have just looked it up, you just use
for row = 1 to (however many rows you find)
userform1.Listbox1.additem sheets("sheet").cells(row.1)
next row

for more than one row I assume you change the
cells(row.1) to cells(row.2)

[mdmackillop]

You can add the controls to the form, setting visible to false, and use your code to change this to True as required. If you're more ambitious, you can add controls using code. I'm think there is a KB item about this.

[lucas]

This can be put into a for each-next statement but this will get you started........if there is nothing in the cell then nothing shows in the listbox

Private Sub UserForm_Initialize()
ListBox1.AddItem Range("C5").Value
ListBox1.AddItem Range("C6").Value
'etc
ListBox1.AddItem Range("C30").Value
End Sub

[mdmackillop]

For a ListBox, you want the List function

Private Sub UserForm_Initialize()
'For one column
ListBox1.List() = Range([C5], [C5].End(xlDown)).Value
'For multi columns
ListBox2.List() = Range([C5], [D5].End(xlDown)).Value
End Sub

[lucas]

That looks like the best solution Malcolm...

[compariniaa]

mdmackillop, the first part of the code worked (for one column), but when I tried the bit about multiple columns it only returned the values from the first column. am i doing something wrong?

[mdmackillop]

Have you set your listbox ColumnCount to 2?

[compariniaa]

no, I didn't, but now that I did, it works perfectly! that's exactly what I needed! thank you all for your help

[compariniaa]

is there any way to make it autosize the listbox and the userform so the user doesn't have to scroll?

[mdmackillop]

Adjust the numbers to suit

Private Sub UserForm_Initialize()
Dim a As Range, rw As Long
Set a = Range([C5], [C5].End(xlDown))
rw = a.Rows.Count
'set heights
UserForm1.Height = 60 + 16 * rw
ListBox1.Height = 16 * rw
'add data
ListBox1.List() = a.Value
End Sub

[compariniaa]

again, thank you mdmackillop!