Add VBA code to worksheet

[kapfrank]

How can i use VBA to put a programcode to a worksheet

For example
- I have a worksheet called "A700".
- There is no VBA code in it.
- Now i want to use VBA to add the next code to the worksheet "A700".
Private Sub Worksheet_Activate()
ActiveWorkbook.Names("Persoon").Delete
End Sub

Somebody an idea?

 

[VoG]

Right click the Sheet tab > View Code. Copy and paste it in.

 

[erik.van.geit]

Hi, kapfrank,

can you rephrase your question?
not sure what you mean here?
perhaps "at" = "add"?

kind regards,
Erik

 

[kapfrank]

Yes your right Erik,
It's 'add' i mean.

I made a workbook called ‘VCS v3.1’, people are using this.
In this workbook there is a worksheet called “A700”.

After I released this version I made some improvements.

One of the improvement is this VBA code, witch is standing in ‘VCS v3.2’ worksheet “A700”:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Names("Persoon").Delete
    ThisWorkbook.Names("Naam").RefersToRange.Copy
    Range("O5").PasteSpecial xlPasteValues
    Selection.Sort Key1:=Range("O5")
    laatste_rij = Cells(Rows.Count, "O").End(xlUp).Row
    ActiveWorkbook.Names.Add Name:="Persoon", RefersToR1C1:=Range("O5:O" & laatste_rij & "")
End Sub

So I made another workbook called ‘Conversion’.
People will push a button and a macro will run. This macro will upgrade ‘VCS v3.1’ to ‘VCS v3.2’

My problem is how to put the code, witch is standing in worksheet ‘VCS v3.2’ “A700” into worksheet ‘VCS v3.1’ “A700”.

When I copy the worksheet from ‘VCS v3.2’ to ‘VCS v3.1’ the formulas are not correct, because the formulas are pointing to the workbook ‘VCS v3.2’ and not to ‘VCS v3.1’

 

[erik.van.geit]

try this

Option Explicit

Sub ReplaceProcedure()
'set reference to Microsoft Visual Basic for Applications.Extensibility x.x

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = Workbooks("VCS v3.2.xls").VBProject.VBComponents("A700").CodeModule

    With VBCodeMod
    .DeleteLines 1, .CountOfLines
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
    "Private Sub Worksheet_Activate()" & vbCr & _
    "ActiveWorkbook.Names(""Persoon"").Delete" & vbCr & _
    "End Sub"
    End With

End Sub

see http://www.cpearson.com/excel/vbe.htm#AddProcedure

 

[kapfrank]

Thank you Eric,

But when i put the code in a workbook i get an error.

Dim VBCodeMod As CodeModule

The error i get is a "Copilation Error" in the line above

Could you explain the next code.

Sub ReplaceProcedure() 
'set reference to Microsoft Visual Basic for Applications.Extensibility x.x 

Dim VBCodeMod As CodeModule 
Dim LineNum As Long 

Set VBCodeMod = Workbooks("VCS v3.2.xls").VBProject.VBComponents("A700").CodeModule 

    With VBCodeMod 
    ...........   
 End With 

End Sub