• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 759
  • Last Modified:

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

0
akgarcia
Asked:
akgarcia
  • 2
  • 2
1 Solution
 
thydzikCommented:
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
 
thydzikCommented:
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now