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.
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.