CODEWITHSUNDEEP
excelvba
Bastien
Bastien

Reputation: 11

How to use information from a ComboBox in another one?

I'm trying to make a UserForm with comboboxes and textboxes. I have two combobox that are working together. In the first one you choose the right sheet and in the second you choose the right column in the selected sheet.

My problem is that even though my code is working, the second combobox doesn't use the moving information from the first one. It always displays the columns from the first sheet whatever my choice. So how do I get the data from the first one to use it in the second one?

Here's my code:

Private Sub UserForm_Initialize()
    Dim I As Long
    Me.ComboBox1.Clear
    For I = 7 To Sheets.Count
        Me.ComboBox1.AddItem Sheets(I).Name
    Next
    Me.ComboBox1.Value = ActiveSheet.Name

    
 Me.ComboBox2.Clear
 
Dim j As Integer
Dim puits As String

j = 3

Do While Worksheets(ComboBox1.Text).Cells(1, j).Value <> ""
     Me.ComboBox2.AddItem Worksheets(Me.ComboBox1.Text).Cells(1, j).Value
     j = j + 3
Loop

End Sub```

EDIT
[USF is to automate the change of the selected cell in this screenshort, same tables on different sheets][1]


  [1]: https://i.sstatic.net/7bbQG.png

Upvotes: 0

Views: 72

Answers (1)

MGP
MGP

Reputation: 2551

You need to use the Combobox_Change-Event. This Example shows what I mean:

Private Sub ComboBox1_Change()
    Dim ws As Worksheet
    Dim lCol As Long, i As Long
    
    Set ws = ThisWorkbook.Worksheets(UserForm1.ComboBox1.Value)
    
    lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    For i = 1 To lCol
        UserForm1.ComboBox2.AddItem ws.Cells(1, i).Value
    Next
End Sub

Private Sub UserForm_Initialize()
    Me.ComboBox1.Clear
    Me.ComboBox2.Clear
    
    Dim ws As Worksheet
    Dim i As Long
    i = 1
    For Each ws In ThisWorkbook.Worksheets
        Me.ComboBox1.AddItem ws.Name
        i = i + 1
    Next ws
End Sub

When I select the Sheet, I change the first Combobox, which triggers the Change-Event. And I then populate the second Combobox according to the selected sheet.

EDIT You could insert a CommandButton and use code like the following:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = ThisWorkbook.Worksheets(UserForm1.ComboBox1.Value)
    Set rng = ws.Range(UserForm1.ComboBox2.Value)
    
    rng.Value = "Your Date"
End Sub

Upvotes: 1

Related Questions