Adding code to worksheet

  • Thread starter Thread starter Rich Wallace
  • Start date Start date
R

Rich Wallace

Hi all,

Curious on how to add code to a specific sheet within a workbook.
Currently, I can add a new module and then add code to that module by using
this:

oModule =
oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModul
e)

sCode = "sub VBAMacro()" & vbCr & _
" msgbox ""VBA Macro called"" " & vbCr & _
"end sub"

oModule.CodeModule.AddFromString(sCode)

But is there a way I can place code into a sheet rather than to a module?

TIA
-Rich
 
Rich,

Set your code module to that worksheet's code module. E.g.,

Dim CodeMod As VBIDE.CodeModule
Set CodeMod =
ThisWorkbook.VBProject.VBComponents(Worksheets("Sheet1").CodeName
).CodeModule


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Rich,

Try this

scode = "sub VBAMacro()" & vbCr & _
" msgbox ""VBA Macro called"" " & vbCr & _
"end sub"

Set VbProj = obook.VBProject
Set VbComp = VbProj.VBComponents(ActiveSheet.CodeName)
Set omodule = VbComp.CodeModule

omodule.InsertLines omodule.CountOfLines + 1, scode

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you all....works like a charm!

Bob Phillips said:
Rich,

Try this

scode = "sub VBAMacro()" & vbCr & _
" msgbox ""VBA Macro called"" " & vbCr & _
"end sub"

Set VbProj = obook.VBProject
Set VbComp = VbProj.VBComponents(ActiveSheet.CodeName)
Set omodule = VbComp.CodeModule

omodule.InsertLines omodule.CountOfLines + 1, scode

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)


oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModul
Click to expand...
 
Back
Top