A
a
Thought I'd try again
I'm trying to add a module to a series of workbooks by looping. This
part works. The part that doesn't work is when I try to assign the
macro to a button.
If I try to assign the macro with:
Selection.OnAction = "Benefits",
the macro that gets assigned is from my source files instead of the file
I'm trying to update. I tried using a variable to assign the file I'm
trying to update:
Selection.OnAction = fileWextention & "!Benefits"
but that errors out even though if I use my source file name:
Selection.OnAction = "AddingModule2.xls!Benefits"
everything works fine - still assigning the macro inside my source file.
I've done this before but for (with Tom Ogilvy's help) but I can't seem
to do this now. Can anybody help? Following is a sample of the code
with the file as a variable commented out.
Thanks much in advance,
Anita
Dim VBComp As VBComponent
Set VBComp = _
Workbooks(fileWextention).VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "CarAllowance"
Application.Visible = True
'End Try
Set SourceModule = _
Workbooks("AddingModule2.xls").VBProject.VBComponents("Module3").CodeModule
Set DestModule = _
Workbooks(fileWextention).VBProject.VBComponents("CarAllowance").CodeModule
With SourceModule
DestModule.AddFromString ( _
.Lines(.ProcStartLine("Home_Benefits", vbext_pk_Proc), _
.ProcCountLines("Home_Benefits", vbext_pk_Proc)))
DestModule.AddFromString ( _
.Lines(.ProcStartLine("Benefits", vbext_pk_Proc), _
.ProcCountLines("Benefits", vbext_pk_Proc)))
DestModule.AddFromString ( _
.Lines(.ProcStartLine("BenefitsPrint", vbext_pk_Proc), _
.ProcCountLines("BenefitsPrint", vbext_pk_Proc)))
End With
Sheets("Bank_Charges").Copy Before:=Sheets(3)
Sheets("Bank_Charges (2)").Name = "Benefits"
ActiveSheet.Unprotect password:="nope"
Range("b10").Value = "Notepad for Benefits"
Range("w11").Select
Sheets("xxxx").Select
ActiveSheet.Buttons.Add(1114, 231.6, 129.75, 14.25).Select
Selection.Characters.Text = "Benefits"
With Selection.Characters(Start:=1, Length:=13).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
' "PERSONAL.XLS!indexTest"
' tryThis = fileWextention & "!Benefits"
Selection.OnAction = "AddingModule2.xls!Benefits"
' Selection.OnAction = fileWextention & "!Benefits"
I'm trying to add a module to a series of workbooks by looping. This
part works. The part that doesn't work is when I try to assign the
macro to a button.
If I try to assign the macro with:
Selection.OnAction = "Benefits",
the macro that gets assigned is from my source files instead of the file
I'm trying to update. I tried using a variable to assign the file I'm
trying to update:
Selection.OnAction = fileWextention & "!Benefits"
but that errors out even though if I use my source file name:
Selection.OnAction = "AddingModule2.xls!Benefits"
everything works fine - still assigning the macro inside my source file.
I've done this before but for (with Tom Ogilvy's help) but I can't seem
to do this now. Can anybody help? Following is a sample of the code
with the file as a variable commented out.
Thanks much in advance,
Anita
Dim VBComp As VBComponent
Set VBComp = _
Workbooks(fileWextention).VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "CarAllowance"
Application.Visible = True
'End Try
Set SourceModule = _
Workbooks("AddingModule2.xls").VBProject.VBComponents("Module3").CodeModule
Set DestModule = _
Workbooks(fileWextention).VBProject.VBComponents("CarAllowance").CodeModule
With SourceModule
DestModule.AddFromString ( _
.Lines(.ProcStartLine("Home_Benefits", vbext_pk_Proc), _
.ProcCountLines("Home_Benefits", vbext_pk_Proc)))
DestModule.AddFromString ( _
.Lines(.ProcStartLine("Benefits", vbext_pk_Proc), _
.ProcCountLines("Benefits", vbext_pk_Proc)))
DestModule.AddFromString ( _
.Lines(.ProcStartLine("BenefitsPrint", vbext_pk_Proc), _
.ProcCountLines("BenefitsPrint", vbext_pk_Proc)))
End With
Sheets("Bank_Charges").Copy Before:=Sheets(3)
Sheets("Bank_Charges (2)").Name = "Benefits"
ActiveSheet.Unprotect password:="nope"
Range("b10").Value = "Notepad for Benefits"
Range("w11").Select
Sheets("xxxx").Select
ActiveSheet.Buttons.Add(1114, 231.6, 129.75, 14.25).Select
Selection.Characters.Text = "Benefits"
With Selection.Characters(Start:=1, Length:=13).Font
.Name = "MS Sans Serif"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
' "PERSONAL.XLS!indexTest"
' tryThis = fileWextention & "!Benefits"
Selection.OnAction = "AddingModule2.xls!Benefits"
' Selection.OnAction = fileWextention & "!Benefits"