Thread
:
modify a macro with a macro
View Single Post
#
3
Posted to microsoft.public.excel.programming
davegb
external usenet poster
Posts: 573
modify a macro with a macro
wrote:
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 wrote:
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.
Reply With Quote
davegb
View Public Profile
Find all posts by davegb