Rowwsourceproblem

[Stift]

Hi everyone.Is is possible to set a rowwsource for a combobox
in a userform to : =C9:C63,C68:C110,B117:B166,B173:B187
?????

How?


Thanks in Advance for the hel

 

[keepitcool]

forget it.

Multiarea range will not work as rowsource.
Make 1 range with the values you need to have in your form.
Move your originals there and in their original location in the worksheet
add a few simple formulas to look 'm up in the new 'solid' range.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Leo Heuser]

Hi Stift

One way to do it:

Private Sub UserForm_Initialize()
'Leo Heuser, 28-5-2004
Dim Cell As Range
Dim Counter As Long
Dim ListRange As Range
Dim ListRangeValue() As Variant


Set ListRange = _
ActiveSheet.Range("C9:C63,C68:C110,B117:B166,B173:B187")

ReDim ListRangeValue(0 To ListRange.Cells.Count - 1)

For Each Cell In ListRange.Cells
ListRangeValue(Counter) = Cell.Value
Counter = Counter + 1
Next Cell

Me.ComboBox1.List = ListRangeValue

End Sub

 

[Leo Heuser]

You could also name the ranges:

In the name box (far left on the formula bar) enter
C9:C63,C68:C110,B117:B166,B173:B187
Press <Enter>
While the cells are selected enter "RowSourceRange"
(without quotes) in the name box.

In the sub enter
Range("RowSourceRange") (*With* quotes)
instead of
Range("C9:C63,C68:C110,B117:B166,B173:B187")


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hi Stift

One way to do it:

Private Sub UserForm_Initialize()
'Leo Heuser, 28-5-2004
Dim Cell As Range
Dim Counter As Long
Dim ListRange As Range
Dim ListRangeValue() As Variant


Set ListRange = _
ActiveSheet.Range("C9:C63,C68:C110,B117:B166,B173:B187")

ReDim ListRangeValue(0 To ListRange.Cells.Count - 1)

For Each Cell In ListRange.Cells
ListRangeValue(Counter) = Cell.Value
Counter = Counter + 1
Next Cell

Me.ComboBox1.List = ListRangeValue

End Sub

 

[Soo Cheon Jheong]

Stift,
Another way to do it:

Option Explicit
Private Sub UserForm_Initialize()

Dim CL As Range
For Each CL In Range("C9:C63,C68:C110,B117:B166,B173:B187")
ComboBox1.AddItem CL.Value
Next

End Sub


--
Soo Cheon Jheong
Seoul, South Korea
_ _
^¢¯^
--

 

[Stift]

Got It Fixed Thanks a lot for all your help!!