Defining sourcerange

[Jaypee]

Could someone perhaps help with the correct code to select a specific range?

Set SourceRange = Range(ListBox2.RowSource).Range("A2:J2")

Thx,

Jaypee

[JimmyTheHand]

In case SourceRange is a Range type variable, the code you gave is correct in syntax. (Whether or not it is the range you want to define, I don't know.)
But you can only select this range if the related worksheet is active.

Jimmy

[Bob Phillips]

So if Jimmy's answer is not it, what is the problem you are getting?

[Jaypee]

Hi xld, this is the code I am using. I saved and closed my worksheet and when starting off again, the problem is solved. The problem now is that it seems not to select that particular sheet's range. Here is some of that code;

worksheets(TextBox29.Value).Select
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, Val6 As String, _
Val7 As String, Val8 As String
If (ListBox2.RowSource <> vbNullString) Then
Set SourceRange = Range(ListBox2.RowSource).Range("A2:J2")
Else
Set SourceRange = Range("A2:C2")
Exit Sub
End If

[Bob Phillips]

What is in the ListBox and what is in ListBox2.RowSource?

Can't you post the workbook, we are groping in the dark here.

[Jaypee]

The further I go the more in trouble I seem to get!
Attached is the file as requested. What I am trying to accomplish is to when I select multipage1 you'll see that it selects a sheet as well
Currently I cannot get listbox2 to read from that sheet.

Hope this makes sense!

Thx.

[Bob Phillips]

Need more help mate, I have no idea what that is doing or is supposed to do (far too much code for me to debug).

Layout in step deatil what you do, and what happens correctly and incorrectly.

ALthough my bet is that it is going to be because you don't qualify the ranges with the appropriate sheet objects, you are relying on the right one being active.

[Jaypee]

You're a brave man just to open that file!!

I'll break it down into another worksheet and repost it!

Thanks so far!

[Jaypee]

xld, I got this code running, thanx.
On the same subject : I am struggeling a bit to get the following working properly. Is there perhaps anything you could see off hand that could cause a problem?

Set SourceRange = Range(ListBox2.RowSource).worksheets(TextBox29.Value).Range("A2:H2")

Thx in advance, J

[Bob Phillips]

Yes, worksheets is not a roperty of range, it is the other way around, so you can't do

Range(...).Worksheets(...)

You can do

Worksheets(...).Range(...)

so you probably mean

[vba]

Set SourceRange = worksheets(TextBox29.Value).Range(ListBox2.RowSource).Range("A2:H2")
[/vba]

[Jaypee]

Hi xld, makes sense hey? An object defined error pops up on that line. This is the code I have used;

Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, Val6 As String, _
Val7 As String, Val8 As String
If (ListBox2.RowSource <> vbNullString) Then
Set SourceRange = worksheets(TextBox29.Value).Range(ListBox2.RowSource).Range("A2:H2")
Else
Set SourceRange = worksheets(TextBox29.Value).Range("A2:C2")
Exit Sub
End If

Could it be that I have not defined something somwhere?

Thx, J