doug's combo box code
phil
you posted this code for populating a combobox which I have used but I'm
unable to get the 4th combo box to load and cant figure out how to do it can
you please provide help on this
thanks phil
If you have the information in the sourcedoc arranged as follows:
Company SalesRep Product
CompanyA SalesRepAAś AAA,AAB,AACś
SalesRepAB ABA,ABB,ABC
CompanyB SalesRepBAś BAA,BAB,BACś
SalesRepBB BBA, BBB, BBC
and with the following code in the userform, a third combobox cmbProducts
would be populated with
AAA
AAB
AAC
if CompanyA is selected in cmbCompany and then SalesRepAA is selected in
cmbSalesRep. If SalesRepAB was selected, cmbProducts would be populated
with
ABA
ABB
ABC
and so on.
If the arrangement in the sourcedoc of the SalesRep data and the Product
data was reversed, and method used in the Change() code for the comboboxes
was also reversed, it should be possible to populate a fourth combobox
cmbComponents based on the selection in cmbProduct. I think that is
probably the limit.
Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Long, myitem As Range
' Modify the path in the following line so that it matches where you saved
Suppliers.doc
Set sourcedoc = Documents.Open(FileName:="d:\documents\Suppliers.doc")
For i = 2 To sourcedoc.Tables(1).Rows.Count
Set myitem = sourcedoc.Tables(1).Cell(i, 1).Range
myitem.End = myitem.End - 1
cmbCompany.AddItem myitem.Text
Next i
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub cmbCompany_Change()
' This code populates the SalesRep combo with the relevant sales reps
based on the company
' that has been selected.
cmbSalesRep.Clear
cmbProduct.Clear
Dim sourcedoc As Document, i As Long, myitem As Range
' Modify the path in the following line so that it matches where you saved
Suppliers.doc
Set sourcedoc = Documents.Open(FileName:="d:\documents\Suppliers.doc")
For i = 1 To sourcedoc.Tables(1).Cell(cmbCompany.ListIndex + 2,
2).Range.Paragraphs.Count
Set myitem = sourcedoc.Tables(1).Cell(cmbCompany.ListIndex + 2,
2).Range.Paragraphs(i).Range
myitem.End = myitem.End - 1
cmbSalesRep.AddItem myitem.Text
Next i
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Private Sub cmbSalesRep_Change()
' This code populates the SalesRep combo with the relevant sales reps
based on the company
' that has been selected.
cmbProduct.Clear
Dim sourcedoc As Document, i As Long, myitem As Range
' Modify the path in the following line so that it matches where you saved
Suppliers.doc
Set sourcedoc = Documents.Open(FileName:="d:\documents\Suppliers.doc")
Set myitem = sourcedoc.Tables(1).Cell(cmbCompany.ListIndex + 2,
3).Range.Paragraphs(cmbSalesRep.ListIndex + 1).Range
cmbProduct.List = Split(myitem, ",")
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub
Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
As I recall, it was late at night when I suggested that and thinking about
it some more, I think that 3 comboboxes is the maximum number that you can
populate in this way. If you really need to go to a fourth level, I think
that you should be using a relational database.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP
"phil" <pw...@bigpond.com> wrote in message
news:een3eaYF...@TK2MSFTNGP12.phx.gbl...
phil
which relational database would you recomend
thanks phil
"Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS"
<d...@mOSTvALUABLEpROFESSIONALs.org> wrote in message
news:OfivNGZF...@tk2msftngp13.phx.gbl...
Doug Robbins - Word MVP
Probably Access. Do you really need to go to four levels?
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP
"phil" <> wrote in message news:uGiZ7CkF...@tk2msftngp13.phx.gbl...
>> > CompanyA SalesRepAA¶ AAA,AAB,AAC¶
>> > SalesRepAB ABA,ABB,ABC
>> > CompanyB SalesRepBA¶ BAA,BAB,BAC¶
phil
four levels would make the form work best as I dealing with a product that
can have upto 6 variables so access looks like the way to go
can you point me in a direction where I can find out the code required to
complete the four level
cheers phil
"Doug Robbins - Word MVP" <d...@NOmvpsSPAM.org> wrote in message
news:u3zQm7vF...@TK2MSFTNGP11.phx.gbl...
> >> > CompanyA SalesRepAAś AAA,AAB,AACś
> >> > SalesRepAB ABA,ABB,ABC
> >> > CompanyB SalesRepBAś BAA,BAB,BACś
Doug Robbins - Word MVP
Giving you the best advice would be easier if I know more about the data and
the relationships between it.
Is there some sort of document/spreadsheet/whatever that you can email to
me.
Or, give me a call on 0417 714 723 - I'm in Brisbane.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP
"phil" <pw...@bigpond.com> wrote in message
news:%23oDbiNw...@TK2MSFTNGP10.phx.gbl...
>> >> > CompanyA SalesRepAA¶ AAA,AAB,AAC¶
>> >> > SalesRepAB ABA,ABB,ABC
>> >> > CompanyB SalesRepBA¶ BAA,BAB,BAC¶