Adding module - assigning macro to button
 

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.A dd(vbext_ct_StdModule)
VBComp.Name = "CarAllowance"
Application.Visible = True
'End Try

Set SourceModule = _
Workbooks("AddingModule2.xls").VBProject.VBCompone nts("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 Befo=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"

Adding module - assigning macro to button
 

You want to call the macro that's in the same workbook as your button?

xxx.onaction = thisworkbook.name & "!benefits"

Maybe?????


a wrote:

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.A dd(vbext_ct_StdModule)
VBComp.Name = "CarAllowance"
Application.Visible = True
'End Try

Set SourceModule = _
Workbooks("AddingModule2.xls").VBProject.VBCompone nts("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 Befo=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"

--

Dave Peterson