find/replace macro code using another macro 1

[wildek]

Hi,

I'm trying to get a macro to open another macro in a different worksheet (the worksheet will already be opened) and then prompt the user to enter the text to find, and the text to replace within the macro (not on the workesheet). Basically, I'm tryin got find the code to open and fill in the Find/Replace dialog box in a macro.

I know how to have a macro find/replace text in a worksheet, but can't get it to find/replace text in a macro. Is there a way?

Thanks!

 

[SkipVought]

Hi,

Are you changing CODE or are you changing DATA VALUES assigned to variables?

The former, I would caution against.

The latter, I would suggest placing data values in a SHEET and changing them there. Furthermore, the cell locations could be given Range Names.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[wildek]

I am trying to change part of the code. Here is the macro code I am opening and want to change:

Sub lock2_502()

Workbooks.Open ("F:\daysheets\502.xls"), writerespassword:="password"
Application.Goto Reference:="pp22A"
ActiveSheet.Unprotect password:="password"
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.Goto Reference:="pp22B"
ActiveSheet.Unprotect password:="password"
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
ActiveWindow.Close

End Sub


In this case I would like to open this macro and find pp22 and replace it with say pp23.

 

[SkipVought]

That is not CODE -- its DATA!

For instance, add a new sheet.

Select A1 - in the Name Box Type GoToRef and hit [Enter]

In A1 enter pp22B

change this value on the sheet any time.

Sub lock2_502()

    Workbooks.Open ("F:\daysheets\502.xls"), writerespassword:="password"
    Application.Goto Reference:=[b][GoToRef][/b]

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[PHV]

Another way is to add a parameter in your macro:
Sub lock2_502(myRef As String)
Workbooks.Open "F:\daysheets\502.xls", writerespassword:="password"
Application.Goto Reference:=myRef & "A"
...
Application.Goto Reference:=myRef & "B"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wildek]

I probably could just put the PP22, pP23, etc.. in the sheet, and have the macro point to that particular cell. I think that my work, rather than editing the actual macro code.

 

[wildek]

Thanks so much. I think I have it now!