[RESOLVED] [Excel 2003] Programtically Insert Module into new Workbook

[Torc]

I am running a workbook which is processing data, and placing it in a new workbook.
The new workbook has two sheets, and it needs to have VBA code loaded for the Worksheet_Change macro, and into a module.

The code is loading correctly, using

    ' Copy this code into Worksheet Main
    TheItem = 2
    If WB.VBProject.VBComponents.Item(TheItem).Properties("Name").Value <> "Main" Then
        TheItem = 3
    End If

    WB.VBProject.VBComponents.Item(TheItem).CodeModule.InsertLines 2, "Private Sub Worksheet_Change(ByVal Target As Range)"
    WB.VBProject.VBComponents.Item(TheItem).CodeModule.InsertLines 3, "    If Not ThisWorkbook.Worksheets(Module1.CMSht).Cells(2,11).Value Then ' WorkInProgress"
    WB.VBProject.VBComponents.Item(TheItem).CodeModule.InsertLines 4, "        ThisWorkbook.Worksheets(Module1.CMSht).Cells(2,11).Value = True ' WorkInProgress"

However when I have the new workbook open, and make a change on the relevant sheet I have a problem.
The first line of the below macro executes and it moves onto the next line
ThisWorkbook.Worksheets(Module1.CMSht).Cells(2, 11).Value = True ' WorkInProgress
however when I press F8 on this line the debugger then halts.
The same happens if I run it normally.

I have tried inserting a line reading Debug.Print "Test", which works fine, but it still fails on the original line

Any suggestions?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not ThisWorkbook.Worksheets(Module1.CMSht).Cells(2, 11).Value Then ' WorkInProgress
        ThisWorkbook.Worksheets(Module1.CMSht).Cells(2, 11).Value = True ' WorkInProgress
        Select Case Target.Column
        Case 3, 4
            If Target.Row > 2 Then
                Target.Interior.ColorIndex = ColourMapping(Target.Value)
            End If
        Case 5
            If Target.Row > 2 Then
                If Target.Value = "" Then
                    Target.Interior.ColorIndex = 0
                Else
                    Target.Interior.ColorIndex = ColourMapping("Red")
                End If
            End If
        Case 6
            If Target.Row > 2 Then
                If Target.Value = "" Then
                    Target.Interior.ColorIndex = 0
                Else
                    If Target.Offset(0, -1).Value <> "" Then
                        Target.Offset(0, -1).Value = ""
                        Target.Offset(0, -1).Interior.ColorIndex = 0
                    End If
                    Target.Interior.ColorIndex = ColourMapping("Amber")
                End If
            End If
        Case 7
            If Target.Row > 2 Then
                If Target.Value = "" Then
                    Target.Interior.ColorIndex = 0
                Else
                    If Target.Offset(0, -1).Value <> "" Then
                        Target.Offset(0, -1).Value = ""
                        Target.Offset(0, -1).Interior.ColorIndex = 0
                    End If
                    If Target.Offset(0, -2).Value <> "" Then
                        Target.Offset(0, -2).Value = ""
                        Target.Offset(0, -2).Interior.ColorIndex = 0
                    End If
                    Target.Interior.ColorIndex = ColourMapping("Green")
                    Target.Offset(0, 1).Value = Module1.CurrentUser()
                    Target.Offset(0, 2).Value = Format(Now(), Module1.DF)
                End If
            End If
        End Select
       ThisWorkbook.Worksheets(Module1.CMSht).Cells(2, 11).Value = False ' WorkInProgress
    End If
End Sub

[westconn1]

if you create the proceedure manually, does it work?
want to post a sample book with your module creation code?

[Torc]

Hi westconn1,

Apologies for not getting back sooner, I was diverted into some Testing which had to take priority.

I deleted the code, and then created the following manually.

Private Sub Worksheet_Change(ByVal Target As Range)
        Select Case Target.Column
        Case 6
                ' Blank column tothe left
                Target.Offset(0, -1).Interior.ColorIndex = 0
                Target.Offset(0, -1).Value = ""
                ' Set the Colour
                Target.Interior.ColorIndex = 7
        Case 7
                ' Blank the 2 Columns to the Left
                Target.Offset(0, -1).Interior.ColorIndex = 0
                Target.Offset(0, -1).Value = ""
                Target.Offset(0, -2).Interior.ColorIndex = 0
                Target.Offset(0, -2).Value = ""
                ' Set the Colour
                Target.Interior.ColorIndex = 9
                'Set the user and the Date/Time
                Target.Offset(0, 1).Value = Module1.CurrentUser()
                Target.Offset(0, 2).Value = Format(Now(), Module1.DF)
        End Select
    End If
End Sub

I changed a value in the Sixth column (G) and used the Debugger to step through, the process failed on when I was setting the .Value

I have attached the a copy of the spreadsheet CC2 I have also included a spreadsheet CCSource which it is run on.

Please note that the system creates a directory based on the name you give
it, and the date; it will create the spreadsheet which is giving problems in a sub directory, and move the source file into the directory as well.

[westconn1]

i tested the above sub it seemed to work ok, though, as it edits cells in the work sheet it is called recursively, so when you set the value the sub starts again from then beginning, before continuing

i was unable to test the workbooks as they, or rather, the code in them are not really compatible with excel 2000

[Torc]

Hi westconn1,

On the Settings sheet I have a cell Row 2 Column 11 which is set to a 0.
If it is Zero then the code will execute, setting it to 1 to prevent it from
recursing; at least that is the theory.

The problem seems to be that when I set a value of a cell the execution of
the code halts.

[westconn1]

yeah, i see that in your original post, but not in the one i tested

i tested again using a static variable also a cell in another sheet, seems to work ok

[Torc]

Weird,

What is weirder is that I have now twice got it to work.

Each time I closed the workbook and opended it to try again and it still worked.

Then I closed Excel and started it again and tried the workbook and it was back to not working again.

I will try running it on a colleagues machine to see if they get a problem.

[Torc]

Found out what is happening and the solution, although it is weird and I do
not completely comprehend it.

My spreadsheet was created with code on the Worksheet for the
Worksheet_Change sub and code in a Module.

My problem was stemming from the fact that the Module was sometimes lost,
thus the reference to Module1.CMSht was losing its' value.

Having corrected this by replacing Module1.CMSht with "Settings" it all is working
fine now.

[westconn1]

great!!!