Simple Listbox Question

[Sayth]

If I create a listbox how can I specify "named entries into the list".

So say I have a 3 named range which comprise 4 different cells.

So
Range 1 = (A2:A4:A5:A7)
Range 2 = (A3:A4:A6:A7)
Range 3 = (A2:A5:A6:A8)

If I want these ranges represented in the listbox as Range 1 = "Monday", Range 2 = "Tuesday", Range 3 = "Wednesday" how can I go about this. I can create listboxes based of ranges, but cannot get a named range displayed in the listbox.

Any help or links to good info appreciated.

 

[Sayth]

Has anyone seen an example of this done before, Google isn't being my friend at finding a similar listbox example.

 

[VoG]

Try something like

Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = Range("Monday").Address(external:=True)
End Sub

 

[Sayth]

Try something like

Private Sub UserForm_Initialize()
Me.ListBox1.RowSource = Range("Monday").Address(external:=True)
End Sub

so by address do you mean range named or defined?

 

[Norie]

If you did have a named range you could simply set the RowSource to "Monday" or whatever the named range is but I think you've got that part.

What is it you actually want to do?

List named ranges in a listbox?

If you do then I'm not quite sure you can.

You could defiinitely list the names of the ranges but to actually use the ranges would need a little more work.

Not much work but a little, it would really depends what you want to do with these names/named ranges/ranges.

 

[Sayth]

If you did have a named range you could simply set the RowSource to "Monday" or whatever the named range is but I think you've got that part.

What is it you actually want to do?

List named ranges in a listbox?

If you do then I'm not quite sure you can.

You could defiinitely list the names of the ranges but to actually use the ranges would need a little more work.

Not much work but a little, it would really depends what you want to do with these names/named ranges/ranges.

As simple as I can I have a list of exercises, different groups of exercises will create the workouts. Its these workouts I want listed in the listbox. I don't want to create a defined sheet of workouts as ultimately I will want users to be able to create their own workouts from the excersises.

This is a rough spreadsheet to give an idea of where I am going http://www.mediafire.com/file/ytdr2yuyz5d/First Split Routine.xlsm

 

[Norie]

Sayth

So what exactly is it you want listed?

You have 2 named ranges, Exercises and RoutineType.

Do you want a combobox/listbox for the RoutineType and when an item is chosen from that the corresponding Exercises will be listed in another listbox/combobox?

The user then picks the Exercises they want from that RoutineType, hit's a button and they are added to another listbox.

Then the user can go back and pick another type, pick exercises and add them to the workout.

Is that anywhere near what you are looking for?

 

[Sayth]

Similar, however routine type is not necessaryat this stage. I am simply aiming at the moment that if I have a workout named in the listbox and the user selects it the applicable exercises that relate to that work out will show in the textboxes below. The only part routinetype plays at the moment is that I have broken the exercise fields on the userform into category type.

Allowing the user to end how many of the exercises they did that day and have this update the sheet. From there will later work on other features creating workouts etc.

 

[Norie]

What textboxes?

There doesn't seem to be any on your userform, except in the Cardio frame.

By the way wouldn't a combobox be better for the Exercises?

You only seem to be showing one value at a time, listboxes are usually used to show more items and/or allow the user to choose from a list of items.

 

[Sayth]

Thanks for looking at it Norie. Yeah the listbox doesn't work, it is just showing exercises by default. Its this listbox that I want to show workouts. Rest of textboxes are to be added to form frames.

I think I figured out how to get a defined name to display such as workouts in the listbox.

If I declare a dynamic array and then reference the array varibale as the name it could work, but I will have to wait unitl I get home.

Dim Workout1()
Workout1 = Cells(A1,A3,A5:A7)
listbox.Rowsource = [Workout1]

of course could just do it the easier way and and use a sheet cell say D1:D4 and fill with workout1, workout2 etc and then use a variant of the help example

<CODE>Dim MyArray(6, 3)</CODE>
<CODE>'Array containing column values for </CODE>
<CODE>ListBox.Private Sub UserForm_Initialize()</CODE>
<CODE>    Dim i As Single</CODE>
<CODE>    ListBox1.ColumnCount = 3</CODE>
<CODE>        'This list box contains 3 data columns    'Load integer values MyArray    For i = 0 To 5        MyArray(i, 0) = i</CODE>
<CODE>       MyArray(i, 1) = Rnd</CODE>
<CODE>       MyArray(i, 2) = Rnd    </CODE>
<CODE>Next i    </CODE>
<CODE>'Load ListBox1    </CODE>
<CODE>ListBox1.List() = MyArrayEnd Sub</CODE>
</PRE>