CODEWITHSUNDEEP
vbaexcel
Hebbeskebbe
Hebbeskebbe

Reputation: 31

Importing a .cls Macro into Sheet1 when creating a new excel file

I'm running a background macro that's based on changes in cells. When I create a new file, I want to make a macro that imports the .csl-file with my background macro to replace "Sheet1" in "Microsoft Excel Objects". I've searched the web, but I can't seem to find a solution that fits my problem. Right now I just copy and paste the code into Sheet1 each time I open a new file, but that's kind of annoying..

The code that I want to move is the following :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column > 1 Then

        If InStr(1, Range(Cells(Target.Row, Target.Column - 1), Cells(Target.Row, Target.Column - 1)).Value, "pos from Rapid") Then

            If Not IsEmpty(Target.Value) Then

                Application.DisplayAlerts = False
                Range(Target.Address).Select
                Selection.TextToColumns Destination:=ActiveCell.Offset(0, 3).Range("A1"), _
                DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False _
                , Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:= _
                False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), DecimalSeparator _
                :=".", TrailingMinusNumbers:=True
                Application.DisplayAlerts = True

            Else

                Range(Cells(Target.Row, Target.Column + 3), Cells(Target.Row, Target.Column + 5)).Value = ""

            End If
        End If
    End If
End Sub

Upvotes: 1

Views: 2446

Answers (1)

FreeMan
FreeMan

Reputation: 5687

I'm sure that what you want to do is possible, however, for simplicity, I would recommend starting with a new, empty Excel workbook, import your .cls file, then File | Save As with a name something like My Classy Workbook and set the Save As Type to Excel Macro-Enabled Template (*.xltm). Note: Be aware when you're saving the file, as Excel wants to put all templates in the default template directory - you'll have to override the directory location if you'd like to put it elsewhere.

Then, when you need a new workbook with the class in it, you simply open your template, and the Class module will already be there. This has the added benefit of opening a copy of the template (named by default My Classy Workbook1.xlsm), so any additional work you do in the new file won't overwrite your original template.

Opening the template (with the Class preloaded) will work either directly from Windows Explorer or from within VBA code.

If you need to edit the template itself (for example to fix or update the class file), you would right-click the file in Windows Explorer, then select Open. Otherwise, you'll simply get a copy of your template to work in.

Upvotes: 1

Related Questions