How do I change the work/active sheet using a varible? 2

[jadadad]

I am using a listbox (Forms not Activex) to make a selection. The listbox output is a number. The number it outputs will be (with a little manipulation) used to ID the sheet number (i.e. sheet1, sheet2, etc.). I can get to the sheet by using Sheets(Cells(r,c)).Select, but it does not make it the active sheet. The command ActiveSheet.Name = Sheets(Cells(r,c)).Select will not work as well as WorkSheet(Sheets(Cells(r,c)).Select).

Anyway, I need to take this number (varible) which changes with each selection from the listbox, and use it to pick the correct worksheet and make it the active worksheet.

Thanks,
Mike

 

[CajunCenturion]

Welcome to Tek-Tips jadadad. I invite you to read FAQ181-2886 to get the most out of your Tek-Tips experience.

In answer to your question, the full statement would be as follows:

WorkBook.WorkSheets.Item(<SheetNumber>).Activate

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jadadad]

Thanks for the input, but still having an issue...
My code for all practical purposes is...
.
.
Dim sheetnumber As Integer
sheetnumber = Cells(35, 2)
Workbook.Worksheets.Item(sheetnumber).Activate
etc...

When I execute the code, I get a "424 error" stating "Object required" at the Worbook.Worksheet......statement

Sorry, but I'm having a difficult time seeing the forest because of the trees. Any suggetions?

 

[CajunCenturion]

The code that I provided references the workbook object, which is one of the main Excel automation objects. But it is not necessarily named "WorkBook.". You establish that name with your Dim statement, and when you actually instantiate the object. I used the name "WorkBook", but you'll need to substitute in the actual name that you used for the workbook object.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[idbr]

Jadadad,

I'm assuming you're using an object variable to control Excel?

If so:

objExcel.Worksheets(intListBox).Activate

should do the trick, where intListBox is the value returned from your listbox (unsurprisingly).

Iain

 

[PHV]

If you can do this:
Sheets(Cells(r,c)).Select
Then you can do this too:
Sheets(Cells(r,c)).Activate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244