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 have put the above code in 'This Workbook' if this is correct, hope you can help.

 

[Patriot2879]

hi I have tried this code as well but still don't work. hope you can help

Private Sub Workbook_Open()
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]

Your last code works ,but there is a Quotation Mark missing in the line below !!!

Set Rng = .Range(.Range("G2[COLOR="#FF0000"][B][SIZE=4]"[/SIZE][/B][/COLOR]), .Range("G" & Rows.Count).End(xlUp))

 

[Patriot2879]

hi thank you for that I have amended it now but still no list is going into the combox1 on sheet1

 

[MickG]

I should put a "Breakpoint" in the code then close and reopen to step though it, and see what's happening

 

[Patriot2879]

Sorry what do you mean? I am still learning

 

[MickG]

I assume as you stated previously, your code is in the "ThisWorkbook" Module !!!
In Active worksheet, Click "Alt +F11" to see code page, > click "Ctrl +R" to see Project page
Scroll down "Project Page" until you find "ThisWorkbook" > double Click to see actual related code Page.

Click somewhere at top of code , but in margin, a Brown Dot and line will appear.
Your code now has a Break Point.
When you now run the code it will stop at that line with the Code window open.
If you then start slowly clicking "F8" you will step through the code and be able to see if its working.

 

[Patriot2879]

Hi I have tried that each line went yellow but nothing happened on the main page

 

[MickG]

You could try copying the code, place in basic module or button and try running it there to see that it works then try again.
You could also place a simple code as below in the "Thisworkbook" module to make sure that's working.

Private Sub Workbook_Open()
MsgBox "Hello"
End Sub