Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

vba code to change a password in a macro

Posted on 2013-02-01
12
Medium Priority
?
420 Views
Last Modified: 2013-02-18
I am using the below code which contains a password. Is there any code that would prompt a password change every 30 days?

Sub Approval()
    resp = InputBox("Enter Password")
    Select Case resp
    Case "silver1":
   
    Insert
   
    'Case "password2": Sheet2.Range("$A$2") = "JR"
         'etc
    Case Else: MsgBox "Invalid Password"
    End Select
   
End Sub
0
Comment
Question by:Jagwarman
  • 7
  • 5
12 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38843538
Hi, John.

You currently have the passwords hard-coded into the macro. This is doable, but it would be a lot easier if they could instead be stored in a Very Hidden sheet (i.e. on that can only be unhidden via macro). Is that acceptable? If not,
 - What's the name of the module?
 - Please export the current module, redact the existing passwords and post it here. (If code is going to read the code then we need the exact code.)

How many passwords are there?

Do they all work on the same 30 day cycle or do they each have their own?

Thanks,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38843560
Hi Brian,

This is a different project I am working on to the one we are working on.

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38843685
John,

Yes, I was aware of that! I'm currently actively involved in 59 open questions and monitoring others.

Regards,
Brian.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 38843708
OMG that's why you are top of the list with 43,800 points

That really is Multi tasking
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38843759
John,

Not as big as it sounds, for example the oldest item on my list was opened on the 1st of November 2011. It's most recent activity was a year after that. I don't find it very demanding!

Now back to the current question - any answers?

Thanks,
Brian.
0
 

Author Comment

by:Jagwarman
ID: 38843931
Hi Brian

You currently have the passwords hard-coded into the macro. This is doable, but it would be a lot easier if they could instead be stored in a Very Hidden sheet (i.e. on that can only be unhidden via macro). Is that acceptable? If not,

I am not sure I understand what you are proposing. After 30 days the user would need to input a new password from a prompt. Reading your note above it sounds like you are proposing the password would be preset on the hidden sheet. Have I misunderstood?

This is the Module/Macro

Sub Approval()
    resp = InputBox("Enter Password")
    Select Case resp
    Case "xxxxxxxx":
   
    Insert
       
    Case Else: MsgBox "Invalid Password"
    End Select
   
End Sub

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38843974
John,

(1) it sounds like you are proposing the password would be preset on the hidden sheet
No - rather that the passwords would be stored on the Hidden Sheet. (They are currently stored in your macro.)
Is this acceptable?

(2) Your latest code is smaller than your original sample! Do you have multiple passwords or not?

Thanks,
Brian
0
 

Author Comment

by:Jagwarman
ID: 38843998
Hi Brian,

(2) Your latest code is smaller than your original sample! Do you have multiple passwords or not?

I removed a line of code that is not used.

No Just the one password.

Regards
John
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38844180
John,

And question (1)?

Brian.
0
 

Author Comment

by:Jagwarman
ID: 38844223
Brian

1) Yes I would say it is acceptable

Regards
John
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38844733
John.,

Please see attached. The password is "A Password" (case-sensitive).

The code is...
Option Explicit

Sub Approval()
Dim xPassword As String
Dim xDate     As Date
Dim xExpired  As Boolean
Dim resp      As String
Dim resp2     As String


xPassword = Sheets("Password").Range("A1")
xDate = Sheets("Password").Range("B1")

If xDate + 30 < Now() Then xExpired = True

If xExpired Then MsgBox ("Your password has expired. You will be required to enter the old one and then enter a new one.")

resp = InputBox("Enter Password")

If resp <> xPassword Then
    MsgBox ("Invalid Password")
    Exit Sub
End If

If xExpired Then
    resp = ""
    Do Until resp <> ""
        resp = InputBox("Please enter new Password")
        If resp = "" Or resp = xPassword Then
            MsgBox ("Your new password must be non-blank and different from your last one.")
            resp = ""
        End If
        If resp <> "" Then
            resp2 = InputBox("Please confirm your new Password")
            If resp2 <> resp Then
                MsgBox ("Confirmation failed - you will be promptedf again for a new password.")
                resp = ""
            End If
        End If
    Loop
            
    Sheets("Password").Range("A1") = resp
    Sheets("Password").Range("B1") = Now()
    
    MsgBox ("New password accepted" & IIf(Mid(resp, 1, 1) = "=", ", smart alec.", ".") & " If you don't save the file then the old password will continue to be in effect.")

End If
    
MsgBox ("Insert")
    
End Sub

Open in new window

Brian.Ageing-Password.xls
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38902700
Thanks, John.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question