Create a user form with listbox

[RodP]

Hi everyone,

I'm trying to set up a small userform which is opened up upon clicking a button from within excel. The userform basically allows the user to select a named range to do some work on (copying and pasting for example)

I've created a simple userform (Formm_Rec_cell_Fmt) and setup a listbox. The userform has the following code:

Private Sub UserForm_Initialize()

Dim ListBox1items As Variant, i As Integer

With Me.ListBox1

ListBox1items = ActiveWorkbook.Names
'ListBox1items = Application.WorksheetFunction.Transpose(ListBox1items)

For i = 1 To unbound(ListBox1items)
.AddItem = ListBox1items(i) 'populates the listbox
Next i

.ListIndex = -1 ' no items selected, set to 0 to select the first item

End With

End Sub

I'm now stuck though as I'm not sure how to load up the form nor return the selection made in a string to be used in later code. I've tried 'load Formm_Rec_cell_Fmt' from within the button code but i am getting a error around the 'unbound' part of the userform code which says:

Compile error, sub or function not defined.

Is it something to do with:
'ListBox1items = ActiveWorkbook.Names'

I'm obviously missing some parts and so would be grateful if some one was to point me in the right direction - perhaps a good webpage which describes step by step creation of userforms as I've never really done this before.

Thanks alot in advance!

RodP

 

[littlewoman]

you get the error indeed since you have here an array named unbound with (listboxitems) dimensions as far as I can tell

I'm not all sure but I think you tried here to use the function ubound (no 'n').

hoped this helped.

 

[rmikesmith]

RodP,

Inside your button click handler simply use the line:

Formm_Rec_cell_Fmt.Show

LittleWoman pegged your other problem as I'm sure you meant UBound() to dynamically determine the upper limit for ListBox1items.


Regards,
Mike

 

[RodP]

Thanks very much to you both - doh! I should have spotted that little pesky 'n' getting in there - must check my eyesight!!!

All is going well with the addin I'm creating - I'll soon be ready to share it with everyone! It's a cheepo (but still quite comprehensive) multiple conditional formatting macro which will also be able to restore original formatting of the cells.

Thanks again

Rodp