Programatically adding worksheet macros through workbook macros in Excel 2003

Posted on 2008-11-12
Last Modified: 2013-11-26
I'm currently stuck trying to figure out how to create worksheet level macros through workbook macros (modules) in Excel 2003. In my workbook macro, I create additional sheets depending on the results of a form. I then want to add to that sheet a macro to check for changes within certain cells. The attached code snippet is an example of what the changed cell macro would look like. I basically want this in a sheet that I create on the fly through one of my modules. Does anyone know how to add worksheet specific macros through another macro?
Private Sub Worksheet_Change(ByVal Target As Range)


'Upon a change in the worksheet, this process is run. If the changed cell happens to be in one

'of the specified columns(A-E), then createTable is called to update the database type list in Backup Data


'None, because it works off the worksheet object and reacts to events

    Dim KeyCells As Range

    Dim x As Range

    ' The variable KeyCells contains the cells that will

    ' cause an alert when they are changed.

    Set KeyCells = Range("A:F")


    Application.ScreenUpdating = False

    If (Cells(1, 1).Value = "Established Rate Schedules") Then

        If Not Application.Intersect(KeyCells, Range(Target.Address)) _

               Is Nothing Then

            If ((Target.Column = 4) And (Target.Value <> "") And (Target.Offset(0, 1).Value <> "")) Then

                If (MsgBox("Would you like to overwrite the data in Annual Salary?", vbYesNo) = vbYes) Then

                    Target.Offset(0, 1).ClearContents



                End If

            ElseIf ((Target.Column = 5) And (Target.Value <> "") And (Target.Offset(0, -1).Value <> "")) Then

                If (MsgBox("Would you like to overwrite the data in Total Rate?", vbYesNo) = vbYes) Then

                    Target.Offset(0, -1).ClearContents



                End If

            End If

            Set x = ActiveCell

            Call createTable

            Sheets("Rate Schedules").Select


        End If

    End If

    Application.ScreenUpdating = True


End Sub

Open in new window

Question by:akgarcia
    LVL 11

    Expert Comment

    the only way I can think of to accomplish this is to create a template sheet with your macro code, perhaps hidden.

    then when you need to create additional sheets via your form, use this template sheet instead of a new sheet. The code will remain in the new copied template sheet.

    Author Comment

    I actually didn't think of this, so I gave it a try. The problem with this is that as I generate the sheet, I create formulas and default items within that same cell range. So, as the macro runs, copies the template sheet, and then populates the new sheet, I get my error messages all over the place. So specifically, I want to add the worksheet change macro at the very end of the procedure for populating the new sheet.
    LVL 11

    Accepted Solution

    not too sure I understand, but i assume you mean the macro behind the template sheet is running before the sheet is populated?

    use a flag in the template sheet that signals all data copying is complete, when the code sees this flag then only it runs.

    Author Closing Comment

    This was a good trick and I used it. I would still like to know if it's possible through all just code though. This works for now. Thank you so much.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now