Userform: Populating Cells based on the information selected in the Combobox

D

Darlie247

New Member
Joined
Apr 2, 2022
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello!

I'm still a newbie to the VBA and Userform usage.
I have developed a Userform that will input the information from the Userform into the cells I have designated. I'm still trying to understand Arrays. But I have it setup in array for Vendor and Probe Type:

VBA Code: 
Private Sub VendorBox_DropButtonClick()
'Add a NEW Vendor into Array below using , and " marks surrounding name
 vendor = Array("Metasystems", "Cytocell", "Vysis")

 FISHValform.VendorBox.List = vendor


End Sub
VBA Code: 
Private Sub VendorBox_AfterUpdate()


Select Case VendorBox.Value
'Add NEW Section entered above as a NEW Case down below following the same format. List the Reagents for that section

 Case "Vysis":

  probetype = Array("Dual Color, Dual Fusion", "Breakapart")
  
 Case "Cytocell":

 probetype = Array("Dual Color, Dual Fusion", "Deletion Probe", "Breakapart", "Alpha Satellite/Centromere")
 
 Case "Metasystems":

 probetype = Array("Dual Color, Dual Fusion", "Deletion Probe", "Breakapart", "Alpha Satellite/Centromere")
  
End Select
   

ComboBox2.List = probetype

  

End Sub


The issues I am trying to figure out are:
1) Since all 3 vendors have the same probe types, how do I setup the "Probe" Combox array in such a way as to differentiate between each Vendor? Or is there an easier way to do that?

2) Each "Probe" has its own different "Probe Regions" that can fill 1-3 cells. How do I get it to auto-populate these based on the Probe selected? More arrays? The list of probes is decently long to want to type each of these up :S (but I can do it, this list will not grow).



1711393392541.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Hi,

Not sure I have fully understood but if you want to select different lists for each probe based on vendor selection, include the probe arrays in your select case statement

Untested but something like this maybe?

Code: 
Private Sub VendorBox_Change()
    Dim probetype   As Variant, probe As Variant
 
    Select Case VendorBox.Value
        Case "Vysis":
         
            probetype = Array("Dual Color, Dual Fusion", "Breakapart")
            probe = Array("Probe7", "Probe8", "Probe9")
         
        Case "Cytocell":
         
            probetype = Array("Dual Color, Dual Fusion", "Deletion Probe", "Breakapart", "Alpha Satellite/Centromere")
            probe = Array("Probe4", "Probe5", "Probe6")
         
        Case "Metasystems":
         
            probetype = Array("Dual Color, Dual Fusion", "Deletion Probe", "Breakapart", "Alpha Satellite/Centromere")
            probe = Array("Probe1", "Probe2", "Probe3")
         
    End Select
 
    With Me.cboProbeType
        .Clear
        If IsArray(probetype) Then .List = probetype
    End With
 
    With Me.cboProbe
        .Clear
        If IsArray(probe) Then .List = probe
    End With
 
End Sub

Personally, rather than hard code the arrays I would create tables & have the code read from them. Using tables makes code shorter & easier for you to maintain the lists.

Also, suggest that you consider
If you are new to userforms then maybe you will find this article of some help: Introduction to UserForms

Hope Helpful

Dave
 
Last edited:
Upvote 0
Using ranges as Dave suggests gives you flexibility:
E.g.
MrExcelSolution Userform.xlsm
BCDEFGHIJKLMNOPQRS
3Probe RegionsVendorsProbe TypesProbesMetasystems Cytocell VysisMetasystems Cytocell VysisProbe 100Probe 101Probe 102Probe 103Probe 104
4Region 1Metasystems Dual Color, Dual FusionProbe 100BreakapartDual Color, Dual FusionDual Color, Dual FusionProbe 100Probe 102Probe 101Region 2Region 7Region 4Region 8Region 1
5Region 2Cytocell Deletion ProbeProbe 101 Deletion Probe Deletion ProbeProbe 101Probe 104Probe 103Region 6Region 9Region 8Region 4Region 4
6Region 3VysisBreakapartProbe 102BreakapartBreakapartProbe 102Probe 104Region 10Region 5
7Region 4 Alpha Satellite/CentromereProbe 103 Alpha Satellite/Centromere Alpha Satellite/CentromereProbe 103
8Region 5Probe 104Probe 104
9Region 6
10Region 7
11Region 8
12Region 9
13Region 10
Lookups
Cells with Data Validation
CellAllowCriteria
G4:I7List=$D$4:$D$7
O3:S3List=$E$4:$E$8
O4:S6List=$B$4:$B$13



The associated code for the data formcan include:
VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = Sheets("Lookups")
   
    Me.cboVendor.List = ws.Range("Range_Vendors").Value  '  C4:C6 -  Populate vendorscombo
    Me.cboVendor.ListIndex = 0
   
    Me.cboProbeType.List = getVendorProbeTypes(Me.cboVendor.Value)  ' populate probetype combo
    Me.cboProbeType.ListIndex = 0
   
    Me.cboProbe.List = getVendorProbe(Me.cboVendor.Value) ' populate probe combo
    Me.cboProbe.ListIndex = 0
End Sub


Private Sub cboProbe_Change()
    Dim arr As Variant
    arr = getRegions(Me.cboProbe.Value) ' populate region text boxes
    Me.txtRegion1 = arr(0)
    Me.txtRegion2 = arr(1)
    Me.txtRegion3 = arr(2)
End Sub

Private Sub cboVendor_Change()
    Me.cboProbeType.List = getVendorProbeTypes(Me.cboVendor.Value)  ' populate probe types
    Me.cboProbeType.ListIndex = 0
   
    Me.cboProbe.List = getVendorProbe(Me.cboVendor.Value)  ' populate probe
    Me.cboProbe.ListIndex = 0
End Sub



This code goes in a standard module:
VBA Code: 
Option Explicit

Function getRegions(sProbe As String) As Variant
   
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 2)
   
    Set R = ws.Range("Range_Probes").Find(sProbe)  ' P3:T3 .. expand as needed
    If Not R Is Nothing Then
        For j = 0 To 2
            arr(j) = R.Offset(j + 1).Value
        Next j
    End If
    getRegions = arr
End Function

Function getVendorProbeTypes(sVendor As String) As Variant
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String, cnt As Integer
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 0)
   
    Set R = ws.Range("Range_Vendor_Probetypes").Find(sVendor) ' G3:I3
    If Not R Is Nothing Then
        cnt = IIf(R.Offset(1).Value <> "", R.End(xlDown).Row - R.Row, 0)
        If cnt <= 1 Then ' only one value, or no values in column
            arr(0) = R.Offset(1)
        Else
            ReDim arr(0 To cnt - 1)
            For j = 0 To cnt - 1
                arr(j) = R.Offset(j + 1).Value
            Next j
        End If
    End If
    getVendorProbeTypes = arr
   
End Function

Function getVendorProbe(sVendor As String) As Variant
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String, cnt As Integer
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 0)
   
    Set R = ws.Range("Range_Vendor_Probes").Find(sVendor) ' K3:M3
    If Not R Is Nothing Then
        cnt = IIf(R.Offset(1).Value <> "", R.End(xlDown).Row - R.Row, 0)
        If cnt <= 1 Then ' only one value, or no values in column
            arr(0) = R.Offset(1)
        Else
            ReDim arr(0 To cnt - 1)
            For j = 0 To cnt - 1
                arr(j) = R.Offset(j + 1).Value
            Next j
        End If
    End If
    getVendorProbe = arr
   
End Function

I didn't use listObjects (tables) which I would normally do, but I think you already have enough to digest.
 
Last edited:
Upvote 0
Solution
In the listings for post #3 my assumptions are that as the vendor is changed, the "probe types" and "probes" comboboxes are updated. Also, when the "probe" combobox"selection changes, the 3 textboxes for "probe regions" will get updtaed.

Also a correction to the inline code comments:
Set R = ws.Range("Range_Probes").Find(sProbe) ' P3:T3 .. expand as needed
should read
Set R = ws.Range("Range_Probes").Find(sProbe) ' O3:S3 .. expand as needed

I inserted or deleted a column but didn't update the comment. The code will stiill work of course.
It's good to use named ranges rather than explicit cell references !!.
 
Upvote 0
iantech said:
Using ranges as Dave suggests gives you flexibility:
E.g.
MrExcelSolution Userform.xlsm
BCDEFGHIJKLMNOPQRS
3Probe RegionsVendorsProbe TypesProbesMetasystems Cytocell VysisMetasystems Cytocell VysisProbe 100Probe 101Probe 102Probe 103Probe 104
4Region 1Metasystems Dual Color, Dual FusionProbe 100BreakapartDual Color, Dual FusionDual Color, Dual FusionProbe 100Probe 102Probe 101Region 2Region 7Region 4Region 8Region 1
5Region 2Cytocell Deletion ProbeProbe 101 Deletion Probe Deletion ProbeProbe 101Probe 104Probe 103Region 6Region 9Region 8Region 4Region 4
6Region 3VysisBreakapartProbe 102BreakapartBreakapartProbe 102Probe 104Region 10Region 5
7Region 4 Alpha Satellite/CentromereProbe 103 Alpha Satellite/Centromere Alpha Satellite/CentromereProbe 103
8Region 5Probe 104Probe 104
9Region 6
10Region 7
11Region 8
12Region 9
13Region 10
Lookups
Cells with Data Validation
CellAllowCriteria
G4:I7List=$D$4:$D$7
O3:S3List=$E$4:$E$8
O4:S6List=$B$4:$B$13



The associated code for the data formcan include:
VBA Code: 
Option Explicit

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = Sheets("Lookups")
 
    Me.cboVendor.List = ws.Range("Range_Vendors").Value  '  C4:C6 -  Populate vendorscombo
    Me.cboVendor.ListIndex = 0
 
    Me.cboProbeType.List = getVendorProbeTypes(Me.cboVendor.Value)  ' populate probetype combo
    Me.cboProbeType.ListIndex = 0
 
    Me.cboProbe.List = getVendorProbe(Me.cboVendor.Value) ' populate probe combo
    Me.cboProbe.ListIndex = 0
End Sub


Private Sub cboProbe_Change()
    Dim arr As Variant
    arr = getRegions(Me.cboProbe.Value) ' populate region text boxes
    Me.txtRegion1 = arr(0)
    Me.txtRegion2 = arr(1)
    Me.txtRegion3 = arr(2)
End Sub

Private Sub cboVendor_Change()
    Me.cboProbeType.List = getVendorProbeTypes(Me.cboVendor.Value)  ' populate probe types
    Me.cboProbeType.ListIndex = 0
 
    Me.cboProbe.List = getVendorProbe(Me.cboVendor.Value)  ' populate probe
    Me.cboProbe.ListIndex = 0
End Sub



This code goes in a standard module:
VBA Code: 
Option Explicit

Function getRegions(sProbe As String) As Variant
 
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 2)
 
    Set R = ws.Range("Range_Probes").Find(sProbe)  ' P3:T3 .. expand as needed
    If Not R Is Nothing Then
        For j = 0 To 2
            arr(j) = R.Offset(j + 1).Value
        Next j
    End If
    getRegions = arr
End Function

Function getVendorProbeTypes(sVendor As String) As Variant
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String, cnt As Integer
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 0)
 
    Set R = ws.Range("Range_Vendor_Probetypes").Find(sVendor) ' G3:I3
    If Not R Is Nothing Then
        cnt = IIf(R.Offset(1).Value <> "", R.End(xlDown).Row - R.Row, 0)
        If cnt <= 1 Then ' only one value, or no values in column
            arr(0) = R.Offset(1)
        Else
            ReDim arr(0 To cnt - 1)
            For j = 0 To cnt - 1
                arr(j) = R.Offset(j + 1).Value
            Next j
        End If
    End If
    getVendorProbeTypes = arr
 
End Function

Function getVendorProbe(sVendor As String) As Variant
    Dim R As Range, j As Integer, ws As Worksheet, arr() As String, cnt As Integer
    Set ws = Sheets("Lookups")
    ReDim arr(0 To 0)
 
    Set R = ws.Range("Range_Vendor_Probes").Find(sVendor) ' K3:M3
    If Not R Is Nothing Then
        cnt = IIf(R.Offset(1).Value <> "", R.End(xlDown).Row - R.Row, 0)
        If cnt <= 1 Then ' only one value, or no values in column
            arr(0) = R.Offset(1)
        Else
            ReDim arr(0 To cnt - 1)
            For j = 0 To cnt - 1
                arr(j) = R.Offset(j + 1).Value
            Next j
        End If
    End If
    getVendorProbe = arr
 
End Function

I didn't use listObjects (tables) which I would normally do, but I think you already have enough to digest.
Click to expand...
Thank you for your help! :)

I am reproducing your table/sheet/codes exactly as you presented them, but I am running into an error. I am getting a "Compile Error: Method or data member not found" for ".cboVendor". I've made sure I've renamed all my userform boxes and sheets according to your example and copied naming all the ranges in the exact format as you have them. Unsure why this error is happening :S (double checked spelling, double checked that "Vendor" is the name of the combobox, and that it is in fact a combobox, etc)

(Doing this as a copy/practice worksheet(s) before I change the info of the table to be the exact information that I need for the official workbook)
 
Upvote 0
The solution as presented is testing and working, so there's defiitely some disconnect between what you typed and the control names on your form.
When in the editor, in one of the subs in the userform, type me. and the autocomplete should bring up a list of control names on the form. The list should include :
cboVendor
cboProbeType
cboProbe
txtRegion1
etc etc

if those don't show up on autocomplete then the controls are probably incorrectly named
confirm what controls you see when typing me.
 
Upvote 0
iantech said:
The solution as presented is testing and working, so there's defiitely some disconnect between what you typed and the control names on your form.
When in the editor, in one of the subs in the userform, type me. and the autocomplete should bring up a list of control names on the form. The list should include :
cboVendor
cboProbeType
cboProbe
txtRegion1
etc etc

if those don't show up on autocomplete then the controls are probably incorrectly named
confirm what controls you see when typing me.
Click to expand...

I am now getting an error involving:

VBA Code: 
Me.cboProbeType.List = getVendorProbeTypes(Me.cboVendor.Value)  ' populate probe types

where the error is 1004 Method 'Range' of object '_Worksheet' failed
 
Upvote 0

Runtime error 1004: Method 'Range' of object '_Worksheet' failed​

Click to expand...
This error suggests you have incorrectly named or omitted one of the named ranges.
Firstly, confirm that the worksheet name is Lookups
Secondly, at a minimum you should have the named ranges shown here - (Press Control and F3 to bring up the name manager):
MrExcel Form names.jpg
 
Upvote 0
Here is a picture of the essentials that you describe in post #1:
MrExcel Form Runtime.jpg
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Multiple columns to populate data on userform based on header in combobox
Replies
4
Views
119
abdo meghari
A
J
  • Solved
ComboBox selection want populate my listbox
Replies
5
Views
862
DanteAmor
DanteAmor
J
  • Solved
Populate UserForm ListBox based on ComboBox Selection
Replies
3
Views
1K
Domenic
D
C
  • Question Question
Excel VBA- Viewing and Updating existing worksheet data with Excel UserForm with Dependent Comboboxes
Replies
2
Views
1K
charliechaz
C
B
  • Question Question
Listbox Filter using a combobox
Replies
1
Views
4K
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top