Change variable in one macro with another macro in vba

Question

Second post here. All I want to do is change the password to protect and unprotect my workbook as defined in my code here...

Dim myPassword As String
myPassword = "yogurt"                     'defines the password

For Each sh In ActiveWorkbook.Worksheets  'unprotects the sheet for editing
    sh.Unprotect Password:=myPassword
Next sh

...by using another macro called something like "Change password," wherein the user would enter the current password and then be able to enter the new password.

I only want the "change password" macro to work if the user types the new password twice to ensure accuracy.

any quick suggestions?

Many thanks.

Sub change_password() 
Dim OldPassword, MyPassword, NewPassword As String 
Dim pass1, pass2 
MyPassword = monkey 
OldPassword = InputBox("Please enter the old password.") 
    If OldPassword = MyPassword Then 
        pass1 = InputBox("Enter the new password.") 
        pass2 = InputBox("Enter the new password again to ensure accuracy.") 
    If pass1 = pass2 Then 
        MyPassword = pass1 
    Else 
        MsgBox "The new password you entered was not entered correctly both times." 
    End If 
End If 
MsgBox ("Your new password is" & MyPassword) 
End Sub

I don't think I was clear in my question then. I would like the password to change in the first sub by typing into an inputbox. Here, I'll write up a little bit of code to help clarify. — FinanceMajor
– FinanceMajor, Commented Jun 7, 2013 at 2:25
Sub change_password() Dim OldPassword, MyPassword, NewPassword As String Dim pass1, pass2 MyPassword = monkey OldPassword = InputBox("Please enter the old password.") If OldPassword = MyPassword Then pass1 = InputBox("Enter the new password.") pass2 = InputBox("Enter the new password again to ensure accuracy.") If pass1 = pass2 Then MyPassword = pass1 Else MsgBox "The new password you entered was not entered correctly both times." End If End If MsgBox ("Your new password is" & MyPassword) End Sub — FinanceMajor
– FinanceMajor, Commented Jun 7, 2013 at 2:41
sorry. i'm trying to figure out how to post correctly. when i post the code from my macro, it turns out like that. how can i put spaces between lines on this site? — FinanceMajor
– FinanceMajor, Commented Jun 7, 2013 at 2:59
you can't put spaces between the lines in a comment, you need to edit the body of your post. — David Zemens
– David Zemens, Commented Jun 7, 2013 at 3:07

Santosh · Accepted Answer · 2013-06-07 03:29:47Z

1

When its password it has to be stored somewhere. I have used in below code a range and named it password Range("password").

Dim OldPassword As String
Dim NewPassword As String

Sub change_password(ByRef blnIsChanged)

    Dim pass1 As String, pass2 As String, myPassword As String

    myPassword = Range("password")
    OldPassword = InputBox("Please enter the old password.")

    If OldPassword = myPassword Then
        pass1 = InputBox("Enter the new password.")
    Else
        MsgBox "Old password not matching", vbInformation
        Exit Sub
    End If

    pass2 = InputBox("Enter the new password again to ensure accuracy.")
    If pass1 = pass2 Then
        Range("password") = pass1
        NewPassword = pass1
        blnIsChanged = True
        MsgBox ("Your new password is " & myPassword)
    Else
        MsgBox "The new password you entered was not entered correctly both times."
    End If


End Sub


Sub btnGO()

    Dim blnPassword As Boolean
    change_password blnPassword

    If blnPassword Then
        For Each sh In ActiveWorkbook.Worksheets
            sh.Unprotect Password:=OldPassword ' Unprotect with old password
            'your cod here
            sh.Protect Password:=NewPassword
        Next sh
    End If
End Sub

answered Jun 7, 2013 at 3:29

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

FinanceMajor Over a year ago

This looks great! I think I can follow this. I'll try to work it all into my workbook sometime this weekend! For now I'll label it solved.

David Zemens Over a year ago

If you're storing the password in a Named Range, it is potentially visible to anyone, which kind of defeats the purpose of protecting the sheets. Or am I missing something?

FinanceMajor Over a year ago

They only need to be protected from unintended edits that would mess the sheet up. I'm programming this for a chain of ice cream shop managers' use. They have the passwords anyway.

FinanceMajor Over a year ago

So I think I almost understand this code and how to use it almost. Two things, 1. What does the line "change_password blnPassword" do? and 2. Which code should I put in the "your cod here?" Thanks.

FinanceMajor Over a year ago

One more question... What does the "(ByRef blnIsChanged)" mean in the first line of your code? I feel like it's important.

|

David Zemens · Accepted Answer · 2013-06-07 03:13:56Z

0

Although it is probably easier to simply call the dialog to set the workbook protection (i.e., this method will have errors if different sheets require different passwords, I've attempted to trap that sort of error) and use the built-in dialogs, this will do pretty much what you're asking for.

As always, remember your passwords. I provide no means of retrieving a lost password.

Option Explicit
Public badpassword As Boolean

Sub changepassword()
    Dim sh As Worksheet
    Dim pw1 As String

    Dim newpw As String
    Dim newpw2 As String
    badpassword = True
    'enter the current password, twice
    pw1 = enterpassword("Please enter the password to UNPROTECT the sheets")

    'prompt for a new password
    newpw = enterpassword("Please enter the new password")
    newpw2 = enterpassword("Please re-enter the new password")
    If newpw <> newpw2 Then
        '## inform the user that the passwords don't match
        MsgBox "The passwords are not the same", vbCritical
    Else:
        '## Attempt to change the password on each sheet
        For Each sh In ActiveWorkbook.Worksheets
            On Error GoTo badpassword '## provide a means of escaping error if password is incorrect
            protectsheet sh, pw1, newpw
            On Error GoTo 0
            If badpassword Then
                MsgBox "The password you entered is incorrect for sheet:" & sh.Name _
                    , vbCritical
                '## allow the macro to continue on other worksheets:
                badpassword = False
            End If
        Next
    End If

    Exit Sub
badpassword:
    '## Trap errors if the supplied password is invalid
    badpassword = True
    Resume Next
End Sub

Function enterpassword(Optional msg As String = "Please enter the password")
    Dim pw$
    pw = InputBox(msg, "Password?")
    enterpassword = pw
End Function

Sub protectsheet(sh As Worksheet, pw As String, newpw As String)
    sh.Unprotect pw
    sh.protect newpw
    badpassword = False 'indicates a success
End Sub

answered Jun 7, 2013 at 3:13

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

3 Comments

FinanceMajor Over a year ago

thank you! I am a beginner so much of the code doesn't make any sense to me. I don't doubt it works, but I will have an easier time working with the code that Santosh has provided. I would vote up but I don't have enough reputation...

David Zemens Over a year ago

It doesn't have to make sense to you, it just has to work :) I believe there is a security concern with Santosh's approach, that the password will potentially be visible to anyone.

Santosh Over a year ago

@DavidZemens Ofcousrse there are ways to hide a cell which has a password and you are better aware of that.

Collectives™ on Stack Overflow

Change variable in one macro with another macro in vba

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related