Need help to fix the syntax of my code

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!

judgeh59, it gave me type mismatch - run time error 13...

Just for your info, its defined as this:

Dim wsAllPrecedents As Worksheet
Dim sAllPrecedents As String

Here you go..

Thanks judgeh59,

I pasted the code in the loop and it works fine. Actually I'm trying to get list of precedents and dependents of the cell in the form.

Thanks for your help!

Hi judgeh59,

Wanted one help to tweak the same code. Actually can you get the output for Dependents like the same output I get for Precedents. Both the output differs from each other, I wanted the output just like Precedents for Dependents. Can you get your hands on it? Will be a great help.

Here is the latest modified version attached.

Yes, correct. It should be the same output like we get for precedents. Thanks!

Judgeh59, Thanks for your hardwork on this, but this is not the way actually i wanted. I wanted it level wise, as how we get the output of precedents, the same output we want to get in dependents.

for example:
[ Level:1] [ Address: [Dependents.xlsm]Sheet1!B7 ]
[ Level:1] [ Address: [Dependents.xlsm]Sheet1!A7 ]

Same like this in dependents. It only shows just this in two columns:
Original Cell.......Dependents
Sheet1'!C7..........Sheet1'!E7

You got the idea?