Simplify my code please :)

Hi all

I'm going back over some of my old code for a Word document I use for our orders for clients. I was looking for a way to shorten the below code possibly using some form of a loop. I've tried a few different ways but to no avail (my skill in VBA is still limited).

ALB1F is Company1 Contact1 First Name
ALB1L is Company1 Contact1 Last Name
ALB1E is Company1 Contact1 Email Address

Each Company has a range of contacts, from 1 to about 8 (i was going to have an upper limited of 10) and likewise there are about 10 companies to work with. Each company I have based on an acronym of the company, however I'm open to changing or generalising this to something like Company1 or similar.

Thanks in advance.

Code:

---

If ListBox4 = ALB Then
Me.ListBox3.Clear
With ListBox3
.AddItem ALB1F & " " & ALB1L
.AddItem ALB2F & " " & ALB2L
End With

ElseIf ListBox4 = AML Then
Me.ListBox3.Clear
With ListBox3
.AddItem AML1F & " " & AML1L
End With

ElseIf ListBox4 = CON Then
Me.ListBox3.Clear
With ListBox3
.AddItem CON1F & " " & CON1L
.AddItem CON2F & " " & CON2L
End With
End If
End Sub

---

It is probably easier to use select case for the choices and as you have not explained what the abbreviations represent then the following is probably close.

Code:

---

    ListBox3.Clear
    Select Case ListBox4.Text
        Case ALB
            With ListBox3
                .AddItem ALB1 & " " & ALB1L
                .AddItem ALB2F & " " & ALB2L
                .ListIndex = -1
            End With
        Case AML
            With ListBox3
                .AddItem AML1F & " " & AML1L
                .ListIndex = -1
            End With
        Case CON
            With ListBox3
                .AddItem CON1F & " " & CON1L
                .AddItem CON2F & " " & CON2L
                .ListIndex = -1
            End With
        'etc
    End Select

---

Quote:

---

Originally Posted by gmayor

It is probably easier to use select case for the choices and as you have not explained what the abbreviations represent then the following is probably close.

---

Thanks for that gmayor. Case will work perfectly fine however I feel that there is still a lot of manual editing when new companies / contacts become available or having to remove contacts etc.

I'm trying to do a loop through a predefined list of contacts when a selected company is selected (also predefined)

For this part of my coding I am essentially showing a list of contacts from whichever company has been selected in my form. I was looking into a loop while or loop until style of shortening the code but it also may not be possible. The idea was something along the lines of:

Code:

---

    Dim i As Integer, k As Integer
    Dim s As String

    k = 11 ' this determines the exit condition
    For i = 1 To 10
    If k = i Then Exit For
            s = IIf(s = "", i, s & " " & i) ' construct the string
        Next
        Me.ListBox3.Clear
        Me.ListBox3.AddItem ALBF & i & " " & ALBL & i        ' add in listbox
        s = ""

---

or

Code:

---


  Dim LCounter As Integer

  For LCounter = 1 To 10 Step 1
      With ListBox3
      .AddItem Text:=ALBF & LCounter
End With
Next LCounter

      Me.ListBox3.Clear
      Me.ListBox3.AddItem TypeText.Text(ALB) & LCounter & TypeText.F & " " & "ALB" & LCounter & "L"
  Next LCounter

---

So it would go through ALBFx, which would be ALBF1, then ALBF2 and so on, likewise with ALBL1 ...

I'm hoping this helps when the explaining I apologies in advance if I'm not explaining it to well.

If the lists are going to change, you would be better storing the contents in Excel and using the xlfilllist function at http://www.gmayor.com/Userform_ComboBox.html to fill the list-boxes.

Quote:

---

Originally Posted by gmayor

If the lists are going to change, you would be better storing the contents in Excel and using the xlfilllist function at ... to fill the list-boxes.

---

Interesting approach, thank you for that gmayor. I'll look into doing the process via this means.