A few problems Programming to the Visual Basic Editor
 

Peter,

1. How do I change the name of a worksheet Sheet9(Margin) to
Margin(Margin) as seen in the VBE.

You can change the Name property of the VBComponent. For example,

ActiveWorkbook.VBProject.VBComponents("Sheet9").Na me = "Margin"

2. How can I update the code in Workings sheet and the code in Margin
sheet in the same procedure. Currently I can update One or the Other
but not both using the code below:

I'm not sure I understand you question. I THINK you can just pass the
VBComponet name to you AddWorkingsProcedure procedure.

AddWorkingsProcedure wbTarget, _
ActiveWorkbook.VBProject.VBComponents("Workings"). Name
AddWorkingsProcedure wbTarget, _
ActiveWorkbook.VBProject.VBComponents("Margin").Na me

Perhaps you could provide a bit more detail about what you are trying to
accomplish. You might also find the following page useful:
http://www.cpearson.com/excel/codemods.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"Peter McNaughton" wrote in message
om...
Hi,
I have found Chip Pearson's web pages very helpful but still have a
couple of problems.

1. How do I change the name of a worksheet Sheet9(Margin) to
Margin(Margin) as seen in the VBE.

2. How can I update the code in Workings sheet and the code in Margin
sheet in the same procedure. Currently I can update One or the Other
but not both using the code below:

Thanks in advance
Peter

Sub AddCodeToSheetModule()
'No error handling!!
Dim strShtName As String
Dim wbTarget As Workbook
Dim VBComp As VBIDE.VBComponent

strShtName = "Workings"

Set wbTarget = Workbooks.Open("C:\coster 2\Angus.std")

strShtName = "Workings"
With wbTarget.Worksheets(strShtName)
For Each VBComp In .Parent.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document And VBComp.Name =
strShtName Then
AddWorkingsProcedure wbTarget, strShtName
Exit For
End If
Next
End With

strShtName = "Margin"
With wbTarget.Worksheets(strShtName)
For Each VBComp In .Parent.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document And VBComp.Name =
strShtName Then
AddMarginProcedure wbTarget, strShtName
Exit For
End If
Next
End With
End Sub

A few problems Programming to the Visual Basic Editor
 

Thanks Chip for the answer to No 1.

Perhaps you could provide a bit more detail about what you are trying to
accomplish. You might also find the following page useful:

I'll try and clarify what my problem is with No 2.

Using the Sub AddCodeToSheetModule() module attached in original post
I can successfully add code to a sheet called Workings but have to
comment out the code to modify the sheet called Margin.

I can do the opposite, successfully modify the code in the sheet
called Margin by making sure I comment out the code to change the
Workings sheet.

My problem occurs when I try to change code in the Workings sheet and
then in the Margin sheet.

Peter


Sub AddCodeToSheetModule()
'No error handling!!
Dim strShtName As String
Dim wbTarget As Workbook
Dim VBComp As VBIDE.VBComponent

strShtName = "Workings"

Set wbTarget = Workbooks.Open("C:\coster 2\Angus.std")

strShtName = "Workings"
With wbTarget.Worksheets(strShtName)
For Each VBComp In .Parent.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document And VBComp.Name =
strShtName Then
AddWorkingsProcedure wbTarget, strShtName
Exit For
End If
Next
End With

strShtName = "Margin"
With wbTarget.Worksheets(strShtName)
For Each VBComp In .Parent.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document And VBComp.Name =
strShtName Then
AddMarginProcedure wbTarget, strShtName
Exit For
End If
Next
End With
End Sub

A few problems Programming to the Visual Basic Editor
 

Thanks Chip for help with No 1. I went to another site and found my
original code worked. There might be something wrong with the
installation of Excel so I will repair the application and hope for
the best. Please ignore my earlier post to your message.

Peter