How to add data lists to Combo Boxes through VBA
I have few doubts in terms of adding data lists to a combo boxes based on the filtered excel sheet.
Below are my questions.
Please find attached excel sheet in which I have created a user form with few tool controls. My requirement is as below:
When the form gets activated (or when the excel sheet opens), the "Region" combo box should be filled with the data list. The other two Combo boxes "City" and "Sales Person Name" should be filled with the data list depending on the selection of "Region".
The Text box value of "Sales Value" - should automatically change when the user click on "Show Details" command button
As these data lists values may change (for different countries) I would like to keep these in a master sheet.
I have a few other points like:
how to restrict the user to
enter only "Text" in name column (ISTEXT)
enter only numeric in phone number column (ISNUMERIC) and how to restrict the user to enter exactly 10 numbers
enter valid email address in email ID field.
Can you please provide me the codes for these questions.
Regards,
Dinesh
| Attachment | Size |
|---|---|
| test1.xlsm | 15.19 KB |
Filling comboboxes
Hi,
Since it may help for future questions, i tried to answer this. I could fill the dependant comboboxes with an inelegant solutions.
''' Code below in a module
Public rgRegions As Range
Public rgCities As Range
Public rgPersons As Range
Sub FillCombo2()
Dim wsSheet As Worksheet
Dim wbBook As Workbook
Dim rnData As Range
Dim vaData As Variant 'the list, stored in a variant
Dim ncData As New VBA.Collection 'the list, stored in a collection
Dim lnCount As Long 'the count used in the On Error Resume Next loop.
Dim vaItem As Variant 'a variant representing the type of items in ncData
'Instantiate the Excel objects.
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.ActiveSheet
Load UserForm1
With wsSheet
' Fill regions
Set rgRegions = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
Set rgCities = .Range(.Range("B2"), .Range("B" & .Rows.Count).End(xlUp))
Set rgPersons = .Range(.Range("C2"), .Range("C" & .Rows.Count).End(xlUp))
'Call UniqueVal__2(rgRegions, UserForm1.CbRegion)
'Call UniqueVal__2(rgRegions, UserForm1.CbCity)
'Call UniqueVal__2(rgRegions, UserForm1.CbSPName)
UserForm1.CbRegion.List = UniqueVal__2(rgRegions)
UserForm1.CbCity.List = UniqueVal__2(rgCities)
UserForm1.CbSPName.List = UniqueVal__2(rgPersons)
End With
UserForm1.Show
End Sub
Public Sub Cb_GetDependants(ParentValue As Variant, Ctl As Control, SrcRange As Range, OffsetColumn As Long)
Dim i As Integer
Dim cl As Range
'Do While Ctl.ListIndex > 0
' Ctl.RemoveItem Ctl.ListIndex
'Loop
For Each cl In SrcRange
If cl.Value = ParentValue Then
Debug.Print cl.Offset(, OffsetColumn).Address
Ctl.AddItem cl.Offset(, OffsetColumn).Value
End If
Next
End Sub
''' code below in the userform
Private Sub CbCity_AfterUpdate()
Dim cl As Range
CbSPName.Clear
With Me.CbSPName
For Each cl In rgRegions
If cl.Value = CbRegion.Value And cl.Offset(, 1).Value = CbCity.Value Then
.AddItem cl.Offset(, 2).Value
'.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End If
Next cl
End With
End Sub
Private Sub CbRegion_AfterUpdate()
CbCity.Clear: Call Cb_GetDependants(CbRegion.Value, Me.CbCity, rgRegions, 1)
CbSPName.Clear: Call Cb_GetDependants(CbRegion.Value, Me.CbSPName, rgRegions, 2)
End Sub
Private Sub TbName_afterupdate()
If IsNumeric(TbName) = True Then MsgBox "Please made an alphabetic typing"
End Sub
Private Sub TbPhone_AfterUpdate()
If IsNumeric(TbPhone) = False Then MsgBox "Please use only numeric characters"
End Sub