modify a macro with a macro

[davegb]

I'm working on a program to change the passwords in some of the
worksheets in the workbook. I've got it, with some help, to change the
worksheet passwords. Now I need it to go into 2 existing macros that
extract data from the password protected worksheets and change the
passwords there. This code is in a userform that is called for the user
to input the 2 existing passwords and the 2 new ones, which are entered
twice each for verification.
I've never tried to edit a macro from a macro. Here is the code so far:

Private Sub cbOK_Click()
Dim sOldMnthlyShtPWORD As String
Dim sOldTtlShtPWORD As String
Dim sNewTtlShtPWORD1 As String
Dim sNewTtlShtPWORD2 As String
Dim sNewMnthlyShtPWORD1 As String
Dim sNewMnthlyShtPWORD2 As String
Dim ws As Worksheet

sOldMnthlyShtPWORD = ufPswrdChange.tboxOldMnthlyShtPword.Value
sOldTtlShtPWORD = ufPswrdChange.tboxOldTtlShtPword.Value
sNewTtlShtPWORD1 = ufPswrdChange.tboxNewTtlShtPword1.Value
sNewTtlShtPWORD2 = ufPswrdChange.tboxNewTtlShtPword2.Value
sNewMnthlyShtPWORD1 = ufPswrdChange.tboxNewMnthlyShtPword1.Value
sNewMnthlyShtPWORD2 = ufPswrdChange.tboxNewMnthlyShtPword2.Value

ufPswrdChange.Hide


If Len(sOldMnthlyShtPWORD) = 0 Then
MsgBox "The old Totals sheet password is blank. Please enter the
password.", vbOKOnly
Call PasswordChange
End If

If Len(sOldTtlShtPWORD) = 0 Then
MsgBox "The old Monthly sheet password is blank. Please enter the
password.", vbOKOnly
Call PasswordChange
End If

If Len(sNewTtlShtPWORD1) = 0 Then
MsgBox "The first new Title sheet password is blank. Please enter
the password.", vbOKOnly
Call PasswordChange
End If

If Len(sNewTtlShtPWORD2) = 0 Then
MsgBox "The second new Title sheet password is blank. Please enter
the password.", vbOKOnly
Call PasswordChange
End If

If Len(sNewMnthlyShtPWORD1) = 0 Then
MsgBox "The first new Monthly sheet password is blank. Please enter
the password.", vbOKOnly
Call PasswordChange
End If

If Len(sNewMnthlyShtPWORD2) = 0 Then
MsgBox "The second new Monthly sheet password is blank. Please enter
the password.", vbOKOnly
Call PasswordChange
End If


'If sNewTtlShtPword1 <> sNewTtlShtPword2 Then
' MsgBox "The new Title sheet passwords don't match. Please re-enter
them", vbOKOnly
' Call PasswordChange
'
'End If
If sNewMnthlyShtPWORD1 <> sNewMnthlyShtPWORD2 Then
MsgBox "The new Monthly sheet passwords don't match. Please re-enter
them", vbOKOnly
Call PasswordChange
End If

For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "*Monthly" Then
ws.Unprotect Password:=sOldMnthlyShtPWORD
ws.Protect Password:=sNewMnthlyShtPWORD1
End If
If ws.Name = "TOTALS" Then
ws.Unprotect Password:=sOldTtlShtPWORD
ws.Protect Password:=sNewTtlShtPWORD1
End If
Next

End Sub

If I want this code to also modify a module in the same spreadsheet
named "ModCount" and change the password there. If someone can get me
pointed in the right direction, I'd appreciate it.

 

[dolivastro]

I may not completely understand what you are doing here, but let me
take a stab for what it is worth:

It sounds like you have the password hard-coded into your macros. If
you are okay with that, then why don't you just store them in a
separate file. Then the macros will read this file, and use the
password it finds there. You can even use a simple encryption, like
shifting everyting three letters, so DOM becomes GRP.

Dom

 

[davegb]

I may not completely understand what you are doing here, but let me
take a stab for what it is worth:

It sounds like you have the password hard-coded into your macros. If
you are okay with that, then why don't you just store them in a
separate file. Then the macros will read this file, and use the
password it finds there. You can even use a simple encryption, like
shifting everyting three letters, so DOM becomes GRP.

Dom

The passwords are hard coded, as Constants in the declarations. I
thought it would be relatively easy to find them and change them with a
macro. Maybe not?
"It sounds like you have the password hard-coded into your macros. If
you are okay with that, then why don't you just store them in a
separate file."
But then the "separate file" has to be placed where the macro or
reference, depending on how I do it, can always find it. Might just as
well keep the passwords in a hidden sheet if I decide to go that way,
don't you think?

 

[davegb]

Check out this link from Chip's site...

http://www.cpearson.com/excel/vbe.htm

Thanks, Jim

 

[davegb]

Check out this link from Chip's site...

http://www.cpearson.com/excel/vbe.htm

I reviewed that page, and what I'm gathering is that you can't modify a
macro with a macro, only create and remove modules from macros. Is this
correct?