Excel Sheet Selection

[mllex]

I have an application that prompts users for instructions via a dialog box and listboxes, then adds a worksheet with specific formatting (a different form for each scenario).

My problem is this...

The dialog box opens when the file opens, and the user can make a selection. But...if the user wants to make another selection (i.e. create another form), they have to select a specific worksheet and click on the command button.

I have built a menu option for the users, which is preferable as it is accessible from any worksheet, but the instructions to populate the list boxes for the dialog box live on a particular worksheet, so I need to activate that sheet before the dialog box pops up. What happens is that after the form is created, the wrong worksheet is selected.

How do I select the worksheet that was just added to the file without having to specifically name it as "Sheet4" or "Sheet12", etc.?

TIA

 

[xlbo]

If, when you create a new worksheet, you put it at the end of the others, its excel sheet number (you can see these on the VBEditor) will be the last in the workbook.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Selectshould then work
HTH
Geoff

 

[IrishJim]

I've had that problem, and think I worked through it....
In Excel, I sometimes create a new sheet with:
Sheets.Add
I might give it a name:
ActiveSheet.Name = "MyNewSheet"

If I later am at a point where the focus is on a sheet, and I don't know the name, necessarily, I can find it:
StartSheetName = ActiveSheet.Name
Maybe I will move to another sheet, and want to come back:
Sheets(StartSheetName).Select

P.S. StartSheetName is a variable I defined as text.

 

[SkipVought]

Hey, BK!

Way back on April 23, you posted a question and got a quick and fine response from xlbo.

You ought to know by now, that expert and helpful contributions deserve a STAR, not only as a "Thank you!" but also as a guide for other Tek-Tip members to identify such expert and helpful postings.

So as a couresy to xlbo and all others who wander by, please show proper recognition of this expert and helpful posting.

Skip,
metzgsk@voughtaircraft.com