VBA Question

[dwilliamson1024]

First of all, thanks to everyone who has helped me on my current project.

I currently have a code to prompt for password before saving a metrics file, which only a handful of people know the password (and not too many are bright enough here to look in VBA to find the password )

I was asked to keep track of the users who go into the file. I can do this by one of two ways:

1. get a time and user stamp on an external file and save it
2. get a time and user stamp on a very hidden sheet and save it within the file itself (preferred)

I greatly prefer option 2 (as I don't want the user to be able to see another file open and put their name down and save, or I will get 500980 phone calls asking what it's doing .

Is there a way to use VBA to (when file first opens):

• unhide the very hidden worksheet
• put the user name and current time into the next available open row
• very hide the worksheet again
• save the file (but enter the password right away and click ok quick enough for the user to not notice it, or notice what the password is when it is typed into the prompt box for saving password.

Any ideas out there?

Thanks!

 

[AlphaFrog]

Yes; This probably could be done. Can you show your code that prompts for password and saves?

VBA can write to a hidden worksheet without making it visible.

 

[dwilliamson1024]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ans As String

ans = InputBox("This is a protected file. Please enter a password to allow saving", "Password Required", "******")

If ans <> "MY.PASSWORD.WE.ARE.USING.GOES.HERE" Then
MsgBox "Password incorrect. Sorry, only administrators have permission to save this file"
Cancel = True
End If

End Sub

 

[AlphaFrog]

Change the name of the Hidden sheet to suit.

[color=darkblue]Dim[/color] ans    [color=darkblue]As[/color] [color=darkblue]String[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeSave([color=darkblue]ByVal[/color] SaveAsUI [color=darkblue]As[/color] [color=darkblue]Boolean[/color], Cancel As [color=darkblue]Boolean[/color])
    
    [color=darkblue]If[/color] ans <> "MY.PASSWORD.WE.ARE.USING.GOES.HERE" [color=darkblue]Then[/color]
        ans = InputBox("This is a protected file. Please enter a password to allow saving", "Password Required", "******")
    
        [color=darkblue]If[/color] ans <> "MY.PASSWORD.WE.ARE.USING.GOES.HERE" [color=darkblue]Then[/color]
            MsgBox "Password incorrect. Sorry, only administrators have permission to save this file"
            Cancel = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    ans = ""
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    ans = "MY.PASSWORD.WE.ARE.USING.GOES.HERE"
    [color=darkblue]With[/color] Sheets([COLOR=#ff0000]"Sheet3"[/COLOR]).Range("A" & Rows.Count).End(xlUp)
        .Offset(1, 0).Value = Application.UserName
        .Offset(1, 1).Value = Now
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    ThisWorkbook.Save
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[dwilliamson1024]

Excellent, thank you so much! Let me put it in and test it and make sure I have everything correct....

 

[dwilliamson1024]

works great, thanks!

 

[dwilliamson1024]

Good morning AlfaFrog.

Is there an easy way to put in an IF statement that would skip putting down my user name? I go in the file 15 times a day, and it will start to become cumbersome if its 800 of me and 50 of other users.

Also out of curiosity, how are you pasting in your code that way into the forum threads? I can't seem to find a post that explains that either. Thanks!

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    
    [color=darkblue]If[/color] Application.UserName <> [COLOR=#ff0000]"dwilliamson1024"[/COLOR] [color=darkblue]Then[/color]
        [color=darkblue]With[/color] Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)
            .Offset(1, 0).Value = Application.UserName
            .Offset(1, 1).Value = Now
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        ans = "MY.PASSWORD.WE.ARE.USING.GOES.HERE"
        ThisWorkbook.Save
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

See my signature block below about the use of CODE tags.

 

[dwilliamson1024]

Perfect, thanks!

I tried it myself, but was getting an error when I opened the file...works good now.