Programatically adding worksheet macros through workbook macros in Excel 2003

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)
'Description:
'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
 
'Dependencies
'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
                Else
                    Target.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
                Else
                    Target.ClearContents
                End If
            End If
            Set x = ActiveCell
            Call createTable
            Sheets("Rate Schedules").Select
            x.Select
        End If
    End If
    Application.ScreenUpdating = True
    
End Sub

Open in new window

akgarciaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TravisCommented:
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.
0
akgarciaAuthor Commented:
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.
0
TravisCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akgarciaAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.