Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding code to worksheets
I have a large number of similar spreadsheets which have
the following code in the code modules of all worksheets in the workbook that are not named "Overview": Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.Run "ESCode.xls!WorksheetCode.PanelsChange" End Sub I want to add ", Target" to the end of this line, and have tried the following code: For Each sh In Worksheets If sh.name < "Overview" Then Set VBCodeMod = ActiveWorkbook.VBProject.VBComponents_ (sh.index).CodeModule With VBCodeMod .DeleteLines 2 .InsertLines 2, " Application.Run ""ESCode.xls! _WorksheetCode.PanelsChange"", Target" End With End If Next At first, all seemed well, but then I discovered that the code in the last worksheet had not changed (all others had). Further, a section of code that is called when closing the file stopped working at the following line: If Workbooks(counter).Worksheets(1).name = "Overview" Then Msgboxing Workbooks(counter).name and Worksheets(1).name, I found that Workbooks(counter).name correctly gave the name of the workbook, but Worksheets(1).name gave a run- time error. As far as I can tell, this section of code is in no way related to the code above, but it has worked for years until I made the changes to the worksheet code. If anyone can shed some light on this...? Windows 2000, Excel 97. mgs |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding an autoshape to vba code | Excel Discussion (Misc queries) | |||
code to prevent users from adding new worksheets | Excel Worksheet Functions | |||
Adding code to a macro | Excel Worksheet Functions | |||
adding and subtracting using vb code | Excel Discussion (Misc queries) | |||
Need code to protect worksheets - amount of worksheets varies | Excel Programming |