Macro Updated by Macro ?

[ayazgreat]

Hi All

How should I update a macro by a macro ? meaning to say that codes are replaced by new codes in module.

Suppose I have workbook named Ayaz.xls containing module named "TDP" which is protectd by VBA Password(Lock Project). I want codes to be updated/replaced by New codes.

For example I have following codes

Private Sub CopySheet1()
   
    With Sheet1.UsedRange
        Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

want change to

 [/COLOR]
[COLOR=black]Private Sub CopySheet1()
   
    With Sheet1.UsedRange
        [COLOR=red]Sheet3.[/COLOR]Range[COLOR=red]("[/COLOR][COLOR=red]C2").[/COLOR]Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub[/COLOR]
[COLOR=black]

Thanks in advance

 

[ayazgreat]

Could anybody answer it please or any remarks

 

[ayazgreat]

Nothing is impossible but there must be a solution for it any one please guide me

 

[Datsmart]

This site has a lot of suggestions on how to work with modules and macro code.
http://www.cpearson.com/excel/vbe.aspx

I have not personally tried any of this.

 

[ayazgreat]

Thanks Datsmart

The solution I want is not there

 

[GTO]

Greetings ayazgreat,

The link John supplied has both how to programatically create a procedure as well how to delete a procedure.

...Suppose I have workbook named Ayaz.xls containing module named "TDP" which is protectd by VBA Password(Lock Project)....

However, I do not believe anyone here is going to discuss how to programatically thwart password protection.

Mark

 

[ayazgreat]

Thanks GTO

Ok if it is not password protected so that can be solved ?

 

[ayazgreat]

And GTO where do you stand about mentioned below coeds where is an error message after running ?

Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
  
'Function to unlock VBA Project if password is known
  
Dim VBP As VBProject, oWin As VBIDE.Window
  
Dim wbActive As Workbook
  
Dim i As Integer
  
Set VBP = WB.VBProject
  
Set wbActive = ActiveWorkbook
  
If VBP.Protection <> vbext_pp_locked Then Exit Sub
  
Application.ScreenUpdating = True
  
'Close any code windows to ensure we hit the right project
  
For Each oWin In VBP.VBE.Windows
  
If InStr(oWin.Caption, "(") > 0 Then oWin.Close
  
Next oWin
  
WB.Activate
  
' Now use lovely SendKeys to unprotect
  
Application.OnKey "%{F11}"
  
SendKeys "%{F11}%TE" & Password & "~~%{F11}", True
  
'Check to see if project unlocked
  
If VBP.Protection = vbext_pp_locked Then
  
' if failed - maybe wrong password
  
SendKeys "%{F11}%TE", True
  
End If
  
' Leave no evidence of the password
  
Password = ""
  
' Go back to the previously active workbook
  
wbActive.Activate
  
End Sub
  
Sub UnprotectProjectandImportVBAmodule()
  
'Use function to unprotect VBA Project
  
UnprotectVBProject Workbooks("Target.xls"), "password"
  
'Import required VBA Module to target workbook
  
Workbooks("Target.xls").VBProject.VBComponents.Import file
  
'Filename:="X:\Development\STP.bas"
  
'Activate target workbook
  
Workbooks("Target.xls").Activate
  
'Run macro residing in target workbook
  
'Run "Target.xls!testone"
  
End Sub
  
'CODE IN Target.xls
  
Sub testone()
  
'MsgBox ("You have unlocked the project, imported the module called
'STP.bas, and run the intended macro")
  
End Sub

 

[ayazgreat]

Could anyone does not suppose any solution for it?

 

[ayazgreat]

Could anyone does not suppose any solution for it?
<!-- / message --><!-- sig -->