Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning a macro to a button | Excel Worksheet Functions | |||
Assigning A Macro to A Button | Excel Worksheet Functions | |||
assigning a macro to a button | Excel Discussion (Misc queries) | |||
Assigning a module to a button macro | Excel Discussion (Misc queries) | |||
Assigning macro to button | Excel Discussion (Misc queries) |