?
Solved

vba code to change a password in a macro

Posted on 2013-02-01
12
Medium Priority
?
366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

801 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