none
List conditional with autocomplete

    Question

  • Hello , 

    I need to have a conditional detachable list that can be autocompleted due to the large amount of data that this list has, for example, if in field G I have a value the list should show certain content. I will show an example below and the code I have so far.

    Sheet

    Column G

    Column H

    103

    Show list A

     401

    Show list B

    102

    Show list C

    Code in sheet 

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'if the cell select is B13:B18 show form.
        If Not Intersect(Target, Range("B13:B18")) Is Nothing Then
            UserForm1.Show
            
        Else
        End If
    End Sub
    

    code useform

    Private Sub CommandButton1_Click()
    Unload Me
    End Sub
    
    Private Sub CommandButton2_Click()
    ActiveCell.Value = Me.ComboBox1.Value
    Unload Me
    End Sub
    
    Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = "lstA"
    End Sub
    

    I can not make the list be conditioned depending on the column G

    please help me 

    Regards,

    Tuesday, February 26, 2019 2:48 PM

All replies

  • Something like this?

    Private Sub UserForm_Initialize()
        Select Case ActiveCell.value
            Case 103
                Me.ComboBox1.RowSource = "lstA"
            Case 401
                Me.ComboBox1.RowSource = "lstB"
            Case 102
                Me.ComboBox1.RowSource = "lstC"
        End Select
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 26, 2019 9:30 PM
  • yeah! , thnks for inspiring me 

    i resolve the problem like this

    Private Sub UserForm_Initialize()
    Select Case ActiveCell.Offset(0, -1)
    
    Case 103
        Me.ComboBox1.RowSource = "lstA"
    Case 401
        Me.ComboBox1.RowSource = "lstB"
    
    End Select
    End Sub
    

    Wednesday, February 27, 2019 12:26 PM