Can you fill an array from the contents of a single cell

[Dan Troxell]

I am trying to populate a Combobox list based on the choice of the previous
combobox. I can store the choices in a table in the proper array format.

Example "CS8", "CS8M" in the cell. But it will not let me assign that to
an array field. Is this possible. Now matter how I try I seem to get Type
Mismatch.

See code below.

Dim CtlrArray() As String ' Defined as string array for ComboBox List
Dim Ctlrs() As Variant ' Defined as Variant array fo move
Dim N As Single 'Index value
N = 0
Dim CtlrUp As Single ' Variable for upper boundry of array

Range("ARMList").Find(What:=ArmSelect1.Text, LookIn:=xlValues,
Lookat:=xlWhole, SearchOrder:=xlColumns).Activate

MsgBox "Active Cell Value = " & ActiveCell.Offset(0, 2) ' used to verify data

' Tried to set a variant array to the value of the cell as described above

Ctlrs() = ActiveCell.Offset(0, 2).Value

For N = 0 To CtlrUp Step 1
CtlrArray(N) = Ctlrs(N)
Next N
ArmDimension = ActiveCell.Offset(0, 3).Value
Arm1Dim = ArmDimension
UserForm1.CtlrSelect1.List(0, 1) = CtlrArray

Spreadsheet looks like this:
ARM WEIGHT CONTROLLERS DIMENSIONS
0
TX40 57 CS8C,CS8CTrans, 48 X 52 X 40
TX60 86 "CS8C","CS8CTrans", 48 X 52 X 60

Note: I was trying both with and without " " on strings.

Thanks for any direction.

Dan Troxell - Staubli Corp

 

[Tom Ogilvy]

Unless this is a multicolumn Combobox try this:

Range("ARMList").Find(What:=ArmSelect1.Text, _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlColumns).Activate
UserForm1.CtlrSelect1.List = Split(ActiveCell.Offset(0, 2).Value,",")

 

[Dan Troxell]

Thanks Tom,

That did get rid of errors, but I don't have any choices in the list box. I
have this code in the
Sub ArmSelect1_Change() routine. It was private, but I changed it to public
and still nothing. So then I moved it to the Private Sub CtlrSelect1_Click()
routine, but still no list.

While I am asking, do you have any recommendations for goog VBA reference &
how to books.

 

[Tom Ogilvy]

I would use the exit event of the textbox

Private Sub ArmSelect1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng as Range
set rng = Range("ARMList").Find(What:=ArmSelect1.Text, _
LookIn:=xlValues, _
Lookat:=xlWhole, _
SearchOrder:=xlColumns)
if not rng is nothing then
UserForm1.CtlrSelect1.List = Split(rng.Offset(0, 2).Value,",")
msgbox "rng.value"
else
msgbox " value not found, try again"
Cancel = False
End if
End Sub

 

[Tom Ogilvy]

for books, it depends on your background and experience.

For a beginner, I would start with
http://www.amazon.com/gp/product/0735613591/104-0937512-4703902?v=glance&n=283155
Excel 2002 Visual Basic for Applications Step by Step by Reed Jacobson.

A bit more advanced, then John Walkenbach's Power Programming Book
http://www.j-walk.com/ss/excel see the book link on the left.
He also did a Excel vba programming for dummies - but I am not as familiar
with that although I am sure it would be commensurate with Jacobson's book.

Also look at Green and Bullen's Excel 2002 VBA Programmer's Reference
http://www.amazon.com/gp/product/07...104-0937512-4703902?s=books&v=glance&n=283155
There is a 2003 version, but it was updated by a different author than the
originals although they are listed as authors.

For advanced, see
Professional Excel Development : The Definitive Guide to Developing
Applications Using Microsoft(R) Excel and VBA(R)
by Bullen, Green, Bovey
http://www.amazon.com/gp/product/03...104-0937512-4703902?s=books&v=glance&n=283155

of course there are many other books on the market, but these are the ones I
am most familar with.

--
Regards,
Tom Ogilvy

 

[Dan Troxell]

Thank you for the advice and the book list. I will take a look at them. I
have quite a bit of RPG on AS/400 (iSeries) and took a structured programming
C class. But no training really or experience in object oreinted or VB.
Knowing what you want to do but learning syntax and how things work by trial
& error could make me bald. I did buy Excel Programming by Visual - Jinjer
Simon and that helped get me going.

Anyway back to my problem.
But I am still not getting the list for the controllers based on the arm
selection. I get a blank list that seems to have the right amount of lines
(some have 2 choices and others only 1)

And before I got your answer I was able to fill a named range and use the
RowSelect property for the ComboBox. The cells were updated but I would
still get a blank list on my form.

At this point I am clueless as to how to see the updated information. Do I
have to activate the form again? Because my code sets a worksheet active so
that I know where I am at. It is like the code is executing, but the list is
not seeing the data.

Thanks again.

 

[Tom Ogilvy]

If you want to send a workbook that illustrates the problem, I will take a
look.

(e-mail address removed)

Include a valid address

If it isn't a culled down version focused specifically on the problem, then
tell me what I need to do and what modules/forms are involved.