Accessing a Password Protected Worksheet with VBA

[JustATheory]

Greetings,

Here is my situation, I have a worksheet that I need to maintain the data in, but need someone else running a macro to access data in the worksheet. Their function is merely to query or vlookup the data and place it in their worksheet, I need to be the one updating it exclusively. Is it possible to have the macro they're running enter the password under a mask like (*****) and grab the data, place in their spreadsheet and close the source data worksheet. I haven't been able find this.

As always any help is greatly appreciated.
Thanks,
Andy

 

[SkipVought]

Hi,

Have you tried macro recording what you want to do?

Then, that code can be modified as required.

Post back with your code if you have a question.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[JustATheory]

Hi Skip,

It gives me the Password Message box when recording and when running it. I've tried this with both Password to Open and Password to Change data.

As always your input is greatly appreciated,
Andy

Workbooks.Open Filename:="C:\TestDir\MacroTest\DestinationData.xls"

Workbooks.Open Filename:="C:\TestDir\MacroTest\SourceData.xls" 'Password Protected File

Windows("DestinationData.xls").Activate
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[SourceData.xls]Sheet1!C1:C2,2,0)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A8"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A8").Select
Range("A1").Select

 

[combo]

You need Workbook.Open with additional 'Password' parameter. The password string can be either in the code or you can ask user for it (for instance custom userform with textbox, that can have PasswordChar mask set).

combo