I have this vb
[VBA]With Me
With Me.CmbBoxDesc1
.AddItem "2500"
.AddItem "1000 BASE SX SHORT WAVE"
.AddItem "10 FT.PATCH CABLE"
.AddItem "10 FT.PRINTER CABLE"[/VBA]
it continues with about 150+ items. My question is how can I have it assigned
to 18 CmbBox's and not have to have one of these long lists for each CmbBox. Something like
[VBA]With Me
With Me.CmbBoxDesc1 - Me.CmbBoxDesc18[/VBA]
[vba]
Dim ary(1 To 200)
ary(1) = "2500"
ary(2) = "1000 BASE SX SHORT WAVE"
ary(3) = "10 FT.PATCH CABLE"
ary(4) = "10 FT.PRINTER CABLE"
'etc
ComboBox1.List = ary
ComboBox2.List = ary
ComboBox3.List = ary
'etc.
[/vba]
but why?
he wants to remove items as they are selected down the list...so if an item is selected in the first combobox...then it won't be available in any of the other comboboxes....and so on down the line...if I understand correctly.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
With a list that size, I would consider storing it in the workbook, or if that is not appropriate, in a text file. Much easier for maintenance if you have to add/delete/modify items.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
This Cmbbox will have a list of items that can be shipped so I want them to be typed the correct way so they will pick from list and the description would be correct.
mcmack that save in a file sounds great for this list will be updated often. How would I be able to make that work. How can I call that file to appear in each cmbbox?
where does it say that?Originally Posted by lucas
I'm not sure I understood but I took it from this...which I admittedly don't understand:
[VBA]
With Me[/VBA]
With Me.CmbBoxDesc1 - Me.CmbBoxDesc18
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
If that's not the case then why not just put them in a dynamic named range on a hidden sheet and use the rowsource? No code at all.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
what i wanted to say was if there was a way for me to just have the list entered once and distribute it through 18 cmbboxs.
I guess I misunderstood....you can use the array/additem method that Bob has suggested..I would just hide a sheet with the data so it's with the workbook...see attached.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Why 18 comboboxes?
If it's to allow the user to make multiple choices then surely one multiselect listbox would be better?
This will allocate 10 items to each of 18 comboboxes. It uses a dynamic range to store the data.
[vba]Private Sub UserForm_Initialize()
Dim i As Long, j As Long, k As Long
For i = 1 To 18
For j = 1 To 10
k = k + 1
If k > Range("MyList").Cells.Count Then Exit Sub
Me.Controls("ComboBox" & i).AddItem Range("MyList")(k)
Next
Next
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ok for that to work for me I would need to change the 10 to the total items. Also change "ComboBox" to "CmbBoxDesc" right? Is there a way to use the text file like originally said?
And initialise k each time through.
[VBA]Private Sub UserForm_Initialize()
Dim i As Long, j As Long
Dim InputData As String
Open "G:\MyData.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
Line Input #1, InputData ' Read line of data.
If i Mod 10 = 0 Then j = j + 1
Me.Controls("ComboBox" & j).AddItem InputData
i = i + 1
Loop
Close #1 ' Close file.
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ok so what I did I created the MyData.txt file entered the List put it in my C:\ Drive . Now edited your code to match my info. First gives me unable to locate file, then it gives me File already open even though it's not. How can i make this work?
Can you zip and post a copy of your workbook and text file?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hope this helps
[VBA]Private Sub UserForm_Initialize()
Dim i As Long, j As Long
Dim InputData As String
For j = 1 To 18
Close #1
Open "C:\MyData.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
Line Input #1, InputData ' Read line of data.
Me.Controls("CmbBoxDesc" & j).AddItem InputData
Loop
Close #1 ' Close file.
Next
With Me
With CmbBoxLocation
.AddItem "ORLANDO, FL"
.AddItem "RICHMOND, VA"
.AddItem "ATLANTA, GA"
.AddItem "MIAMI, FL"
.AddItem "DAYTONA BEACH, FL"
.AddItem "ROANOKE, VA"
.AddItem "TALLAHASSE, FL"
.AddItem "HOMOSASSA, FL"
.AddItem "MACON, GA"
.AddItem "JACKSONVILLE, FL"
.AddItem "MEMPHIS, TN"
.AddItem "STOCKBRIDGE, GA"
.AddItem "FORT MYERS, FL"
.AddItem "CHATTANOOGA, TN"
.AddItem "KNOXVILLE, TN"
.AddItem "ALCOA, TN"
.AddItem "MULBERRY, FL"
.AddItem "CHAMBLEE, GA"
.AddItem "GREENSBORO, NC"
.AddItem "CALIFORNIA, MD"
.AddItem "GAMBRILLS, MD"
.AddItem "SMYRNA, GA"
End With
End With
With Me
With CmbBoxProject
.AddItem "DEPOT"
.AddItem "VAM"
.AddItem "PCAR 2007"
.AddItem "UPGF"
.AddItem "SERVICE REQUEST"
End With
End With
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
