Solved

vba code to change a password in a macro

Posted on 2013-02-01
12
281 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now