Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
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,",") -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
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. -- Dan Troxell - Staubli Corp "Tom Ogilvy" wrote: 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,",") -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
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 -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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. -- Dan Troxell - Staubli Corp "Tom Ogilvy" wrote: 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,",") -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
for books, it depends on your background and experience.
For a beginner, I would start with http://www.amazon.com/gp/product/073...lance&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/076...lance&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/032...lance&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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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. -- Dan Troxell - Staubli Corp "Tom Ogilvy" wrote: 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,",") -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
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. -- Dan Troxell - Staubli Corp "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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. -- Dan Troxell - Staubli Corp "Tom Ogilvy" wrote: 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,",") -- Regards, Tom Ogilvy "Dan Troxell" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can you fill an array from the contents of a single cell
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Max of a Single-Cell Array | Excel Worksheet Functions | |||
Very Basic Problem - Merged Cell Equals Contents of a Single Cell | Excel Worksheet Functions | |||
I cannot select a single cell or pull down cell contents | Excel Worksheet Functions | |||
Open multiple text files and paste contents to single cell | Excel Programming | |||
Entering array in single cell | Excel Discussion (Misc queries) |