PDA

View Full Version : [SOLVED] Selection not displayed when combobox item is selected



sllaksvb
08-28-2017, 10:54 AM
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
08-28-2017, 01:14 PM
Can you post a sample workbook?

sllaksvb
08-28-2017, 02:03 PM
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. 20198

Paul_Hossler
08-28-2017, 02:59 PM
Try commenting out cmbcontact.Clear line and see if that's what you want




Private Sub cmbcontact_DropButtonClick()

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

offthelip
08-28-2017, 03:10 PM
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
08-28-2017, 03:42 PM
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
08-29-2017, 06:35 AM
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
08-29-2017, 06:38 AM
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
08-29-2017, 08:05 AM
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
08-29-2017, 08:07 AM
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
08-29-2017, 11:05 AM
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
08-29-2017, 03:59 PM
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
08-30-2017, 01:47 AM
I forgot to add the link array definition at the top of the module:


Dim link(0 To 5) As Integer

sllaksvb
08-30-2017, 09:05 AM
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
08-30-2017, 09:28 AM
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.