| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify a macro with a macro
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify a macro with a macro
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 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify a macro with a macro
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify a macro with a macro
Jim Thomlinson wrote: Check out this link from Chip's site... http://www.cpearson.com/excel/vbe.htm -- HTH... Jim Thomlinson Thanks, Jim "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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify a macro with a macro
Jim Thomlinson wrote: Check out this link from Chip's site... http://www.cpearson.com/excel/vbe.htm -- HTH... Jim Thomlinson 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? "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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to modify my macro ... | Excel Discussion (Misc queries) | |||
| Modify A Macro | Excel Worksheet Functions | |||
| Modify a Macro | Excel Worksheet Functions | |||
| Modify a macro | Excel Programming | |||
| Help to modify macro please | Excel Programming | |||
Linear Mode
