Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reference Problem
I have a macro that generates a sub() that I use as a "macro template".
I then try to go and change the newly created sub template's name using this code (below) - I borrowed and adapted from somewhere in the internet. The worksheet "FileTrackingSheet" is opened. I jump from the macro that just renerated the template to this RenameSub code. The macro would not compile by itselft, but if I run it, it removes the name of from the template sub and leaves the new name as "sub ()" which gives me another compiling error. I think I am not able to reference the workbook FileTrackingSheet correctly. Can someone take a look at my code. Thanks. ~Pablo Sub RenameSub() Dim OldMacroName As String Dim findtext As Long Dim NewMacroName As String Dim wbXL As Excel.Workbook Set wbXL = Workbooks(FileTrackingSheet.xls).Select Sheets(MSBs_Tracking).Select NewMacroName = Range("B6").Text OldMacroName = "ChangeNameofThisMacro" With ActiveWorkbook.VBProject.VBComponents("FileTrackin gCompanyList").CodeModule On Error GoTo notfound findtext = .ProcBodyLine(OldMacroName, prockind:=vbext_pk_Proc) .ReplaceLine Line:=findtext, String:="Sub " & NewMacroName & "()" End With Exit Sub notfound: MsgBox "not found" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook Reference Problem
I figure it out. Thanks you all.
Pablo wrote: I have a macro that generates a sub() that I use as a "macro template". I then try to go and change the newly created sub template's name using this code (below) - I borrowed and adapted from somewhere in the internet. The worksheet "FileTrackingSheet" is opened. I jump from the macro that just renerated the template to this RenameSub code. The macro would not compile by itselft, but if I run it, it removes the name of from the template sub and leaves the new name as "sub ()" which gives me another compiling error. I think I am not able to reference the workbook FileTrackingSheet correctly. Can someone take a look at my code. Thanks. ~Pablo Sub RenameSub() Dim OldMacroName As String Dim findtext As Long Dim NewMacroName As String Dim wbXL As Excel.Workbook Set wbXL = Workbooks(FileTrackingSheet.xls).Select Sheets(MSBs_Tracking).Select NewMacroName = Range("B6").Text OldMacroName = "ChangeNameofThisMacro" With ActiveWorkbook.VBProject.VBComponents("FileTrackin gCompanyList").CodeModule On Error GoTo notfound findtext = .ProcBodyLine(OldMacroName, prockind:=vbext_pk_Proc) .ReplaceLine Line:=findtext, String:="Sub " & NewMacroName & "()" End With Exit Sub notfound: MsgBox "not found" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting Reference to another WorkBook-Problem | Excel Programming | |||
problem sharing an excel workbook when my formulas reference user. | Excel Programming | |||
Reference code in another workbook from a calling workbook | Excel Programming | |||
Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Reference to other workbook problem | Excel Programming |