Excel 2007 VBA ActiveSheet.Index issues

[luanub]

I am using the following code to insert an Event Handler into a specific sheet. I am getting inconsistent results in what sheet actually get the code inserted.

The sheet I am placing the event handler onto is added to the sheet by the code. The new sheets name is Always Sheet1 and the code name is always Sheet9. Sometime the index returned is 1 and sometimes it is 9. Why am I getting these different results and is there a better way to have it detect the index to use?

Code to add the event handler(Code is the var that contains the event handler code):

With ActiveWorkbook.VBProject.VBComponents("Sheet" & ActiveSheet.Index).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

The sheet is added using the following:

Sheets("CTB Summary").Select 'This sheet is the first sheet in the workbook.
Sheets.Add

The CTB Summary sheet is the sheet that the code will be placed on in error.

When I look in the VBA editor I see the sheets listed as Sheet1(CTB Summary) and Sheet9(Sheet1).

Any help would be greatly appreciated. I am using Excel 2007.

 

[Norie]

Why don't you create a reference to the worksheet you are adding?

Dim wsNew As Worksheet

    Set wsNew = Sheets.Add

You can then use that reference in subsequent code.

With ActiveWorkbook.VBProject.VBComponents(wsNew.Name).CodeModule
    .Insertlines .CountOfLines + 1, Code
End With

 

[luanub]

Why don't you create a reference to the worksheet you are adding?

Dim wsNew As Worksheet

    Set wsNew = Sheets.Add

You can then use that reference in subsequent code.

With ActiveWorkbook.VBProject.VBComponents(wsNew.Name).CodeModule
    .Insertlines .CountOfLines + 1, Code
End With

I receive a "Run-Time error '9': Subscript out of range" error when i try using that method.

 

[Norie]

Where did you put the code I suggested?

Do you have more than one workbook open?

 

[luanub]

Oops the error was my fault I renamed the sheet after the Sheet.Add and before the .Insertline.

I corrected that error and the code runs. Only issue is the event handler still ends up on the wrong sheet.

I have no other sheets open. I am completely closing excel after each change to the VBA and re-opening only with the workbook I am running the code on.

 

[Norie]

It works for me, the code is added to the newly created worksheet.

By the way, what code are you adding and are you adding it to multiple sheets?

 

[luanub]

The code is an Calculate event handler that takes the value the user has entered, checks a data table and if the value is found list the associated values in an adjacent cell. The reason I'm using an event handler is to dynamically control the size of the cell the data is being placed in as autofit will not work once the row height has been adjusted.

 

[Norie]

Are you doing this for multiple sheets?

 

[luanub]

No just the first sheet. I may have to just add it to others to be safe? Not sure why this is happening now. Sometimes the ActiveSheet.Index method works with no issues. I will keep playing with it. Thank you for your help!!!

 

[luanub]

Okay really strange happenings...

I came up with the following solution yesterday

'Changed
'Sheets.Add
Sheets.Add After:=Worksheets(Worksheets.Count)

'And then for the code placement changed to:

With ActiveWorkbook.VBProject.VBComponents("Sheet" & ActiveSheet.Index).CodeModule
    .insertlines .CountOfLines + 1, Code
End With
Sheets("Sheet1").Move Before:=Sheets(1)

And all seemed great in the world. Came in the this morning, published the report to my peeps and behold. The new sheet is now showing as Sheet1(Sheet1) with an index of 9 so the script is failing again.

New fix seems to be to change the code placement to:

    Sheets("Sheet1").Move Before:=Sheets(1)
    With ActiveWorkbook.VBProject.VBComponents("Sheet" & ActiveSheet.Index).CodeModule
        .insertlines .CountOfLines + 1, Code
    End With

Does anyone know why this keeps changing and if there is anything more I can do to further ensure that this will work properly going forward?