ComboBox1 in sheet1 list source in sheet2 how to do vba code

[Patriot2879]

Hi please can you help I have a combobox with a dropdown in sheet1, the data I want it to list is in sheet2 G2 to last row, please can you look at my code below and advise how to correct it. thanks again

Private Sub Workbook_Open()
With Sheet1.ComboBox1
    ComboBox1.RowSource = ("Sheet2")
    ComboBox1.List = .Range("G2:G" & .Range("G" & Rows.Count).End(xlUp).Row).Value
End Sub

 

[Patriot2879]

Hi, I got it working now I changed the sub worksheet to activate . an you advise tough how can I add anther ComboBox2 with range of A2 to last row please?

Private Sub Workbook_Activate()
Dim Rng As Range, Dn As Range
With Sheets("Sheet2")
    Set Rng = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
End With
With Sheets("Sheet1").ComboBox1
    .Clear
    .List = Rng.Value
End With

End Sub

 

[MickG]

Something Like this:-
NB:- Make sure you have "Combobox2 in sheet1.

Private [COLOR="Navy"]Sub[/COLOR] Workbook_Open()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
    .ComboBox1.Clear
    .ComboBox1.List = Rng.Value
    .ComboBox2.Clear
    .ComboBox2.List = Rng2.Value
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Patriot2879]

hi thanks for your help, I have entered the code below but combobox2 has no list in it but combobox1 has, combobox2 is in sheet1

Private Sub Workbook_Open()
Dim Rng As Range, Dn As Range, Rng2 As Range
With Sheets("Sheet2")
    Set Rng = .Range(.Range("G2"), .Range("G" & Rows.Count).End(xlUp))
    Set Rng2 = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Sheet1")
    .ComboBox1.Clear
    .ComboBox1.List = Rng.Value
    .ComboBox2.Clear
    .ComboBox2.List = Rng2.Value
End With
End Sub

 

[Patriot2879]

hi just realized what I did wrong it works now many thanks, I copied the last combobox instead of adding a new one

 

[MickG]

You're welcome

 

[Patriot2879]

Is it the same process if I want to add more comboboxes? Thanks again

 

[MickG]

Yes, just repeat what I did with the next combobox

 

[Patriot2879]

Ok great thanks again for the help. Much appreciated