copy macros using VBA

hulei

New Member
Joined
Jun 23, 2010
Messages
11
hey !

I try to copy some macro codes into a new worksheet. This new worksheet is added and names after NewSheetName. I found a code, but this one was used to enter code in "tabelle1", which seems not to be a worksheet.

HTML:
http://vba1.de/vba/020makrorein.php

I tried to change "tabelle1" and insert the NewSheetName, but got the error "Subscript out of range"

Code:
Sub AddNewSheet

Dim NewSheetName as string

NewSheetName = Worksheets("blank").Range("B1").Value

´( .... )

Dim x As Integer

With ThisWorkbook.VBProject.VBComponents(NewSheetName).CodeModule
     x = .CreateEventProc("SelectionChange", "Worksheet") 
    .InsertLines x + 1, "'Test"
 
End With
End Sub

Do u have an idea what I have to change the code?

Thanks!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
thanks for the quick reply!

I ve been on that site before, but I couldnt figure out how to copy the code to a specific worksheet ..

Am i overseeing something ?
 
Upvote 0
This worked for me (just as an example)

Code:
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

With CodeMod
    LineNum = .CreateEventProc("Activate", "Worksheet")
    LineNum = LineNum + 1
    .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
End With
End Sub
 
Upvote 0
Sorry, still facing a hopefully minor problem

I tried to call the sheets I want to add the code then by a variable NewsheetName

I get the value for "NewSheetName" from a range on an other sheet.

If i check it using msgbox, it appears without " ", so I add parantheses and create a new name.

VBA somehow still cant address the sheet, and i get a "Subscript out of range" error. This doesnt happen when I use eg "Sheet31" as a reference.

The error occurs in the third line


Code:
NewSheetNameVBA = Quotes & Worksheets("help").Range("A31").Value & Quotes
 
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(newsheetname)
Set CodeMod = VBComp.CodeModule
With CodeMod
 
 
x = .CreateEventProc("change", "Worksheet")
    
    .insertLines x + 1, "ok le"
 
 End With

thanks
 
Upvote 0
I think that you need the codename of the sheet. If you have just added a sheet, it should be the active sheet so maybe try

Rich (BB code):
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(ActiveSheet.CodeName)
Set CodeMod = VBComp.CodeModule
With CodeMod
 
 
x = .CreateEventProc("change", "Worksheet")
    
    .insertLines x + 1, "ok le"
 
 End With
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top