Selection not displayed when combobox item is selected

[sllaksvb]

Hi all,

I have a combobox that is supposed to generate items based on the values of a range of cells on a different worksheet. However, when the items are generated and selected, it does display the right information, but the combobox remains blank instead of displaying the current selection that I made. I was wondering if there was a way to fix this?

My code is:

Private Sub cmbcontact_DropButtonClick()
cmbcontact.Clear
    If Sheets("Contact Details").Range("A4").Value <> "" Then
        cmbcontact.AddItem "Client 1"
    End If
    If Sheets("Contact Details").Range("A5").Value <> "" Then
        cmbcontact.AddItem "Client 2"
    End If
    If Sheets("Contact Details").Range("A7").Value <> "" Then
        cmbcontact.AddItem "FA 1"
    End If
    If Sheets("Contact Details").Range("A8").Value <> "" Then
        cmbcontact.AddItem "FA 2"
    End If
    If Sheets("Contact Details").Range("A10").Value <> "" Then
        cmbcontact.AddItem "Cus 1"
    End If
    If Sheets("Contact Details").Range("A11").Value <> "" Then
        cmbcontact.AddItem "Cus 2"
    End If

Private Sub cmbcontact_Change()
    
    Select Case cmbcontact.Value
        Case "Client 1"
            Call ClientContact1
        Case "Client 2"
            Call ClientContact2
        Case "FA 1"
            Call FAContact1
        Case "FA 2"
            Call FAContact2
        Case "Cus 1"
            Call CusContact1
        Case "Cus 2"
            Call CusContact2
    End Select
End Sub

Sub ClientContact1()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(3, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(3, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(4, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(4, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(4, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(4, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(4, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(4, 7)
    End With
End Sub
Sub ClientContact2()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(3, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(3, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(5, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(5, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(5, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(5, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(5, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(5, 7)
    End With
End Sub
Sub FAContact1()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(6, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(6, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(7, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(7, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(7, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(7, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(7, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(7, 7)
    End With
End Sub
Sub FAContact2()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(6, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(6, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(8, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(8, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(8, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(8, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(8, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(8, 7)
    End With
End Sub
Sub CusContact1()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(9, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(9, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(10, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(10, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(10, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(10, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(10, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(10, 7)
    End With
End Sub
Sub CusContact2()
    With Sheets("Summary")
        .Range("J9").Value = Sheets("Contact Details").Cells(9, 6)
        .Range("J10").Value = Sheets("Contact Details").Cells(9, 7)
        .Range("K5").Value = Sheets("Contact Details").Cells(11, 1)
        .Range("K6").Value = Sheets("Contact Details").Cells(11, 3)
        .Range("K7").Value = Sheets("Contact Details").Cells(11, 4)
        .Range("K8").Value = Sheets("Contact Details").Cells(11, 5)
        .Range("K9").Value = Sheets("Contact Details").Cells(11, 6)
        .Range("K10").Value = Sheets("Contact Details").Cells(11, 7)
    End With
End Sub

Also, I was wondering if there was a way to condense the code! Any help would be greatly appreciated! Thank you.

[mdmackillop]

Can you post a sample workbook?

[sllaksvb]

The problem I am having is that when I select an item on the combobox on the "Summary" page, it disappears after being selected although the values are filled in the cells below. Contact Test.xlsm

[Paul_Hossler]

Try commenting out cmbcontact.Clear line and see if that's what you want

Private Sub cmbcontact_DropButtonClick()

'    cmbcontact.Clear   <<<<<<<<<<<<<<<<<<

[offthelip]

you can solve the disappearing selection by moving your code to the worksheet activate event instead of the dropbutton click

Private Sub Worksheet_Activate()   
  cmbcontact.Clear
    If Sheets("Contact Details").Range("A4").Value <> "" Then
        cmbcontact.AddItem "Client 1"
    End If
    If Sheets("Contact Details").Range("A5").Value <> "" Then
        cmbcontact.AddItem "Client 2"
    End If
    If Sheets("Contact Details").Range("A7").Value <> "" Then
        cmbcontact.AddItem "FA 1"
    End If
    If Sheets("Contact Details").Range("A8").Value <> "" Then
        cmbcontact.AddItem "FA 2"
    End If
    If Sheets("Contact Details").Range("A10").Value <> "" Then
        cmbcontact.AddItem "Cus 1"
    End If
    If Sheets("Contact Details").Range("A11").Value <> "" Then
        cmbcontact.AddItem "Cus 2"
     End If


End Sub

[offthelip]

you can compact your code by using the listindex property to generalise the routine:

   Private Sub cmbcontact_Change()
    Dim kk As Integer
    
        kk = cmbcontact.ListIndex
        Call ClientContact(kk)
    End Sub


     Sub ClientContact(kk As Integer)
     Dim ra(1 To 6) As Integer
       ra(1) = 3
       ra(2) = 3
       ra(3) = 6
       ra(4) = 6
       ra(5) = 9
       ra(6) = 9
   If kk > -1 Then
     With Sheets("Contact Details")
      inarr = .Range(.Cells(1, 1), .Cells(11, 7))
     End With
     With Sheets("Summary")
      For i = 1 To 2
       .Range("J" & 8 + i) = inarr(ra(kk + 1) + i - 1, 5 + i)
      Next i
      addi = 0
      For i = 1 To 6
       .Range("K" & 4 + i) = inarr(4 + kk, addi + i)
       addi = 1
      Next i
     End With
   End If
     End Sub

[sllaksvb]

Hi Paul, I tried doing that earlier, the problem with that is that the items in the combobox duplicates if it is not cleared.

Hi offthelip, I'm not sure I understand your compacted code, is there anyway you can annotate what some of the code means?
I moved my code to worksheet_Activate and it works perfectly!
Also, can I ask how would I clear the range("K5:K10") when there is nothing filled in the contact details page?

[Simon Lloyd]

Also, can I ask how would I clear the range("K5:K10") when there is nothing filled in the contact details page?

Activesheet.Range("K5:K10").ClearContents

[sllaksvb]

Hi Simon,

Thank you for your reply. I understand that code clears the content within the range. However, what I am looking to do is for it to automatically clear the contents within K5:K10 of the "Summary" Sheet when there are no values in the "Contact Details" Sheet.

[offthelip]

The key to the way my code works is to notice that there are various patterns to the numbers of the cells that you want to copy data to and from.

Firstly the destination cells are the same for all clients.: J9, J10 and K5 to 10
Since the numbers 9 and 10 are successive numbers these can be accessed easily in a loop of 2
Similarly number 5 to 10 can be done in a loop of 6
For the source cells:
Firstly to save multiple accesses to another sheet I load all the contents of sheet contact details into an array “inarr”, the addressing of this in terms of columns and rows is the same as the sheet ( because I picked up the whole sheet)
For the J cells the column number are always the same (6 and 7) for all clients/FA/Cuscontacts
The row numbers go in pairs : 3, 3 for clients, 6,6 for FA 9,9 for cuscontacts.
I have put these values into the array “ra”
For the k Cells the columns are always the same for client/fs/cus being 1, 3 then successive numbers up to 7. The rows are just successive numbers depending on the client number with a one row gap between the clients, the FA and the Cuscontacts. i.e 4,5 7,8 10,11
So what I am trying to do with my code is for each destination cell calculate which column and row to copy the data from. I have used a variety of techniques in order to show you various ways of solving the problem .
Instead of using the commandbutton name I am using the listindex value which is an integer which tells which of the items in the list has been selected in this case a number between 0 and 5
The first technique is using a lookup table (ra) this is very powerful and is the way to deal with random cell addresses. I use this in the loop for the j Cell in a loop of 2:

For i = 1 To 2
       .Range("J" & 8 + i) = inarr(ra(kk + 1), 5 + i)
      Next i

The second technique is to use an algorithm depending on the client number and the loop index to calculate the columns and rows. ( Note that my first posting wasn’t quite correct because I hadn’t taken account of the gap between the clients the FA and cuscontact.
On the K cells you can see that after the K5 value of 1 the K6 value is 3 and then successive up to 7, so before the loop I set addi to 0 and then in the first loop I set it to 1 and add this into the loop count address for the column , so we get 1 , 3 ,4,5, ,6,7.
Similarly for the row we increment by 1 for each client and then I have calculated addr which is goes 0 , 0 ,1,1, 2,2 through the six clients.

the updated code which I hope really does work now is:

     Sub ClientContact(kk As Integer)     Dim addr As Integer
     Dim ra(1 To 6) As Integer
       ra(1) = 3
       ra(2) = 3
       ra(3) = 6
       ra(4) = 6
       ra(5) = 9
       ra(6) = 9
   If kk > -1 Then
     With Sheets("Contact Details")
      inarr = .Range(.Cells(1, 1), .Cells(11, 7))
     End With
     With Sheets("Summary")
      For i = 1 To 2
       .Range("J" & 8 + i) = inarr(ra(kk + 1), 5 + i)
      Next i
      addi = 0
      addr = ((kk - 0.01) / 2)
     
      For i = 1 To 6
     
       .Range("K" & 4 + i) = inarr(4 + kk + addr, addi + i)
       addi = 1
       
      Next i
     End With
   End If
     End Sub

[sllaksvb]

Wow, thank you for your detailed explanation!

I understand it much better now. However, the issue with using the listindex is that although the combobox items populate according to the fields that are filled in "Contact Details", if the form is not entirely filled out, the corresponding values that will be reflected in "Summary" would still be in order.
(e.g. if I fill out details for Client 1, FA 1, and Cuscontact 1 only, the combobox will populate "Client 1", "FA 1", Cuscontact 1". However, when the item "FA 1" is selected, "Summary" sheet will populate the values that are supposed to be for "Client 2" (Row 5 in "Contact Details"), causing the "Summary" sheet to return blank values.)

It would be great if you could advise me on how I could overcome this.
Regardless, I really appreciate you taking the time to explain your code to me! You taught me to think analytically when creating codes and exposed me to uses of different methods such as an array.

[offthelip]

You can over come this by using another link table which you build when you build the drop down list, so declare the array "link" right at the top of the module so that it is retained across all the subs then change the worhsheet activate and the cnbcontact_change subs as follows:

    Private Sub cmbcontact_Change()
    Dim kk As Integer
    Dim linkin As Integer
        linkin = cmbcontact.ListIndex
        If linkin > -1 Then
        kk = link(linkin)
        Call ClientContact(kk)
        End If
    End Sub

Private Sub Worksheet_Activate()
    cmbcontact.Clear
    linkindex = 0
    If Sheets("Contact Details").Range("A4").Value <> "" Then
        cmbcontact.AddItem "Client 1"
        link(linkindex) = 0
        linkindex = linkindex + 1
    End If
    If Sheets("Contact Details").Range("A5").Value <> "" Then
        cmbcontact.AddItem "Client 2"
        link(linkindex) = 1
        linkindex = linkindex + 1
    End If
    If Sheets("Contact Details").Range("A7").Value <> "" Then
        cmbcontact.AddItem "FA 1"
        link(linkindex) = 2
        linkindex = linkindex + 1
    End If
    If Sheets("Contact Details").Range("A8").Value <> "" Then
        cmbcontact.AddItem "FA 2"
        link(linkindex) = 3
        linkindex = linkindex + 1
    End If
    If Sheets("Contact Details").Range("A10").Value <> "" Then
        cmbcontact.AddItem "Cus 1"
        link(linkindex) = 4
        linkindex = linkindex + 1
    End If
    If Sheets("Contact Details").Range("A11").Value <> "" Then
        cmbcontact.AddItem "Cus 2"
        link(linkindex) = 5
        linkindex = linkindex + 1
     End If


End Sub

There is no change to the Clientcontact sub.

What you could try yourself is to condense the worksheet activate routine which now looks very repetitive, by defining an array of 6 strings to hold the text for the drop down box, then loop round A5 to A11 with the gaps as I have demonstrated. Once you have grasped this technique moving anything to anywhere gets really easy.

[offthelip]

I forgot to add the link array definition at the top of the module:

Dim link(0 To 5) As Integer

[sllaksvb]

Thank you for your reply. I was not aware of the linkindex function. Thank you so much, I've put your code into my workbook and it works perfectly. Also, your explanations really helped in my understanding of the code, hopefully I'll be able to produce such codes myself in the future.

Thanks!

[offthelip]

Be careful linkindex is not a function, it is just the name of variable, I have not used option explicit so I haven't declared all the variables that I have used, if I had declared it, it should have been in the worksheet activate sub as:

Dim  linkindex as integer

when you are learning how to write code, I would recommend using option explicit at the top of every module, it will probably help.

I don't use it because I find the types in VBA very limited and I find it more of a hinderance than a help. This is because I have spent so many years writing code in languages that didn't have the option explicit facility anyway, so I have got used to being very careful with variable names, and I prefer to just use sensible names.
e.g. Linkindex is the index into the Link array which is defined at the top of the module.