Select a Sheet Issue in VBA on a very hidden sheet - fails
 

Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) - position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

You can't select a hidden sheet and in most case you don't need to do this
anyaway

Try changing this:

Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")


to this

mycodearray = Sheets(leftstring).Range("C1:C5000")
--
jb


"christopher ward" wrote:

Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) - position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

It's very rarely ever necessary to use Select or Activate

Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")

mycodearray = Worksheets(leftstring).Range("C1:C5000")

Maybe first assign the worksheet to an object variable which you can use
repeatedly until it goes out of scope, eg

Dim ws as Worksheet
Set ws = ActiveWorkbook.Worksheets(leftstring)
mycodearray = ws.Range("C1:C5000")

Regards,
Peter T


"christopher ward" wrote in
message ...
Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture
from
a form in the Leftstring Variable as shown below. I find I cannot then
select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) -
position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

You cannot select a hidden sheet. To get the range

mycodearray = Sheets(leftstring).Range("C1:C5000")

OR

Dim myRange as Range
Set myRange = Sheets(leftstring).Range("C1:C5000")

If this post helps click Yes
---------------
Jacob Skaria


"christopher ward" wrote:

Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) - position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

Unhide the sheet! This may give you some ideas of how ot get started:
http://excel.tips.net/Pages/T002548_...orksheets.html

http://www.teachexcel.com/macros/del...worksheets.php
http://stackoverflow.com/questions/8...s-or-workbooks


HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"john" wrote:

You can't select a hidden sheet and in most case you don't need to do this
anyaway

Try changing this:

Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")


to this

mycodearray = Sheets(leftstring).Range("C1:C5000")
--
jb


"christopher ward" wrote:

Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) - position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

On Jul 21, 10:47*am, christopher ward
wrote:
Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture from
a form in the Leftstring Variable as shown below. I find I cannot then select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
* * * * * *If DT2.ListBox2.Selected(i) Then

* * * * * * * *fullstring = DT2.ListBox2.Value
* * * * * * * *position = InStr(fullstring, "=")

* * * * * * * *leftstring = Left(fullstring, position - 1)

* * * * * * * *rightstring = Right(fullstring, (Len(fullstring) - position))

* * * * * *End If
Next i

Sheets(leftstring).Select *" so error occurs here
mycodearray = Range("C1:C5000")

--
C Ward

A bit long-winded but have you tried turning off screen updating,
unhiding the worksheet, doing your processing and then hiding again
before turning screen updates back on? That way any hidden information
remains unhidden as far as the user can see.

Select a Sheet Issue in VBA on a very hidden sheet - fails
 

very rarely will you need to selecta cell to read/write data
try

mycodearray = Sheets(leftstring).Range("C1:C5000")

"christopher ward" wrote in
message ...
Dear Experts

I am trying to select a very hidden sheet via VBA whose name I capture
from
a form in the Leftstring Variable as shown below. I find I cannot then
select
the sheet to capture a range , not sure how i do this - sorry.

I am using Excel 2003 - if you help many thanks as usual

Chris

For i = 0 To DT2.ListBox2.ListCount - 1
If DT2.ListBox2.Selected(i) Then

fullstring = DT2.ListBox2.Value
position = InStr(fullstring, "=")

leftstring = Left(fullstring, position - 1)

rightstring = Right(fullstring, (Len(fullstring) -
position))

End If
Next i


Sheets(leftstring).Select " so error occurs here
mycodearray = Range("C1:C5000")



--
C Ward