How to create events for custom control?

Macro to create a footer entry

• As you suggested, I had previously tried the Record Macro process to create the code but the results didn't turn out right. To make sure, I tried it again and still I got bad results. Please look at the following to better understand the steps I took and let me know what I need to do to get the code working properly. Here¡¯s what happened the first time when I tried recording a macro. I had the MacroMaster file opened on Sheet2. I had File1BB file opened on Sheet2. I started the Record Macro process. I accessed Sheet1 of MacroMaster, clicked on cell A14, and did a control-C command. I went to File1BB and opened Sheet4, selected Header and Footer from the View selection, clicked Custom Footer, selected the Center section for the footer location, clicked on the Font button and selected Bold, and then clicked OK. Then I did a control-V to copy the value into the footer box but nothing happened, so I stopped the macro recorder and tried another test. Here¡¯s what the code looked like at the end of the first test.: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2/9/2010 by Stan ' ' Sheets("Sheet1").Select Range("A14").Select Selection.Copy Windows("File1BB.xls").Activate Sheets("Sheet4").Select Application.CutCopyMode = False With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Now for the 2nd Macro Record process I performed, the process was pretty much like the first one except that when I selected cell A14, I also ¡°highlighted the cell contents¡± before I did a control-C command. Here are the steps I followed: I had the MacroMaster file opened on Sheet2 again. I had File1BB file opened on Sheet3. I started the Record Macro process. I accessed Sheet1 of MacroMaster, clicked on cell A14, highlighted the contents of A14, and did a control-C. I went to File1BB and opened Sheet4, selected Header and Footer from the View selection, clicked Custom Footer, selected the Center section for the footer location, clicked on the Font button and selected Bold, clicked OK. Then I did a control-V to copy the value into the footer box. The value copied properly into the Footer area selected. I clicked the OK button twice to close the Footer box and the Page Setup box. Here¡¯s what the code looked like after I stop the macro recorder: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 2/9/2010 by Stan ' ' Sheets("Sheet1").Select Range("A14").Select ActiveCell.FormulaR1C1 = "Test Footer - 2/2/2009 to 2/2/2010" Windows("File1BB.xls").Activate Sheets("Sheet3").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "&""Arial,Bold""Test Footer - 2/2/2009 to 2/2/2010" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = -3 .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub So the 2nd macro seems to have worked. But when I looked at the macro code that was generated by the Record Macro process, I see that the code syntax shows the actual contents of cell A14 as opposed to it being a pointer to cell A14. So I went to Sheet1 of MacroMaster, changed the contents of cell A14 and then executed Macro2 again. But there was a problem. No matter what I change the value in cell A14 to become, when I ran the macro (Macro2), it always inserted the value found in the macro into the header and didn¡¯t use the value from cell A14. As a matter of fact, it also overlaid the value in A14 with the value found from Macro2. Please let me know how I can fix this code to function properly. Stan

• Answer:

  Stan, Unless you are changing all the settings you recorded, you don't need those. Also, no Reason to select anything. Sub AAA_Tester1() Dim r As Range Set r = Workbooks("MacroMaster.xls").Sheets("Sheet1").Range("A14") With Workbooks("File1BB.xls").Sheets("Sheet3") .PageSetup.CenterFooter = "&""Arial,Bold""&10" & r.Text 'End With End Sub Hope that gets you going. Just a caution. If r.Text starts with a number, then you need to put a space between it and the &10" So if A14 starts with numbers, the code would be: Sub AAA_Tester1() Dim r As Range Set r = Workbooks("MacroMaster.xls").Sheets("Sheet1").Range("A14") With Workbooks("File1BB.xls").Sheets("Sheet3") .PageSetup.CenterFooter = "&""Arial,Bold""&10 " & r.Text ' 'End With End Sub As I recall, you said you wanted the font to be 10 points. If not, change the 10 to whatever you want.