PDA

View Full Version : Solved: AddItem help



Emoncada
05-14-2007, 08:24 AM
I have this vb

With Me
With Me.CmbBoxDesc1
.AddItem "2500"
.AddItem "1000 BASE SX SHORT WAVE"
.AddItem "10 FT.PATCH CABLE"
.AddItem "10 FT.PRINTER CABLE"

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

With Me
With Me.CmbBoxDesc1 - Me.CmbBoxDesc18

Bob Phillips
05-14-2007, 09:08 AM
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.


but why?

lucas
05-14-2007, 09:15 AM
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.

mdmackillop
05-14-2007, 09:17 AM
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.

Emoncada
05-14-2007, 09:26 AM
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.

Emoncada
05-14-2007, 09:28 AM
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?

Bob Phillips
05-14-2007, 09:38 AM
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.

where does it say that?

lucas
05-14-2007, 09:54 AM
I'm not sure I understood but I took it from this...which I admittedly don't understand:


With Me
With Me.CmbBoxDesc1 - Me.CmbBoxDesc18

lucas
05-14-2007, 09:55 AM
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.

Emoncada
05-14-2007, 09:57 AM
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.

lucas
05-14-2007, 10:10 AM
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.

Norie
05-14-2007, 11:23 AM
Why 18 comboboxes?

If it's to allow the user to make multiple choices then surely one multiselect listbox would be better?

mdmackillop
05-14-2007, 11:32 AM
This will allocate 10 items to each of 18 comboboxes. It uses a dynamic range to store the data.
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

Emoncada
05-14-2007, 11:41 AM
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?

Bob Phillips
05-14-2007, 11:55 AM
And initialise k each time through.

mdmackillop
05-14-2007, 12:34 PM
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

Emoncada
05-14-2007, 12:55 PM
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?

mdmackillop
05-14-2007, 12:59 PM
Can you zip and post a copy of your workbook and text file?

Emoncada
05-14-2007, 01:04 PM
Hope this helps

mdmackillop
05-14-2007, 01:34 PM
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

Emoncada
05-14-2007, 01:42 PM
Looks Good Thanks MdMack.

Emoncada
05-14-2007, 01:43 PM
Now I was planning on doing a vlookup in a txtbox depending on this value. Would it be easier just to add it to this .txt file and have it auto populate at the same time?

Emoncada
05-14-2007, 01:45 PM
something like

items
example.
In .txt file

2500,Phone
10FT. Patch Cable,Expense
etc..

What do you think?

mdmackillop
05-14-2007, 02:19 PM
I think this would be much easier in a spreadsheet. If necessary, this could be kept separate from your main book

Emoncada
05-15-2007, 05:25 AM
Ok will keep it in a hidden sheet the list
Column A = Items (For CmbBoxDesc)
Column B = This would tell what kind of item it is. "Phone", "Expense",& "" When needing a Serial Number. (For TxtBoxSN)

I guess a vlookup would work good here. Can someone help with that one.

Emoncada
05-15-2007, 08:49 AM
Ok was able to get this code
Private Sub CmbBoxDesc1_Change()
Dim ws As Worksheet, rngFind As Range
Set ws = ThisWorkbook.Sheets("List")
Set rngFind = ws.Range("A:A").Find(what:=Me.CmbBoxDesc1.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
Me.TxtSN1.Value = rngFind.Offset(0, 1).Value
Else
Me.TxtSN1.Value = "NOT FOUND!"
End If
End Sub

This works good.
My question is now how can I have this work for all 18 cmbboxDesc I have, without having to enter this in each _Change Sub?

Bob Phillips
05-15-2007, 09:17 AM
Private Sub CmbBoxDesc1_Change()
LoadTextbox Me.TxtSN1, Me.CmbBoxDesc1
End Sub

Private Sub CmbBoxDesc2_Change()
LoadTextbox Me.TxtSN2, Me.CmbBoxDesc2
End Sub

'etc.

Private Sub LoadTextbox(ByRef cbo As MSForms.ComboBox, _
ByRef tx As MSForms.TextBox)
Dim ws As Worksheet, rngFind As Range
Set ws = ThisWorkbook.Sheets("List")
Set rngFind = ws.Range("A:A").Find(what:=cbo.Value, MatchCase:=True)
If Not rngFind Is Nothing Then
tx.Value = rngFind.Offset(0, 1).Value
Else
tx.Value = "NOT FOUND!"
End If
End Sub

Emoncada
05-15-2007, 11:15 AM
It's giving me a runtime error '13' Type Mismatch.
Any Ideas?

Bob Phillips
05-15-2007, 11:18 AM
Got the order wrong



Private Sub CmbBoxDesc1_Change()
LoadTextbox Me.CmbBoxDesc1, Me.TxtSN1
End Sub

Private Sub CmbBoxDesc2_Change()
LoadTextbox Me.CmbBoxDesc2, Me.TxtSN2
End Sub

Emoncada
05-15-2007, 12:07 PM
Perfect Thanks A Ton XLD.