Help me with syntax of the code...anyone?

[JackkG]

Hi all,


I'm trying to get the content of cells from a sheet into a listbox in user form. Normally the code goes like this.

Form1.Listbox1.RowSource = "Sheet1!A1:A3"

But I'm adding the sheet at the end and and I'm naming it with combining the name of parent sheet. For example, If I'm at Sheet_List, so it adds the sheet to the end like Sheet_List_Pre. So, for this the code goes like this.

sAllPrecedents = rngToCheck.Parent.Name & "_Pre" 
Set wsAllPrecedents = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) 
wsAllPrecedents.Name = sAllPrecedents

So, my question is how to get the content of this added sheet in listbox. I'm trying something like this, but its giving error.

Form1.Listbox1.RowSource = sAllPrecedents!A1:A3

Can someone help me correct the syntax here.


Thanks!


Cross-posted here:
http://www.excelforum.com/showthread...76#post4092076

[Paul_Hossler]

Try these and see

Form1.Listbox1.RowSource = sAllPrecedents & "!A1:A3"

or

Form1.Listbox1.RowSource = "'" & sAllPrecedents & "'!A1:A3"

[JackkG]

Thanks Paul, first one works fine. Thanks for your help!

[Paul_Hossler]

Glad

Suggest you mark it [Solved] by using ThreadTools at top right

Come back in new thread if you have additional questions

[JackkG]

Sure. Thanks Paul!