PDA

View Full Version : Solved: Excel VBA - Save As with Password



Andybuck86
08-23-2010, 04:13 AM
Hi,

I'm new to VBA and was hoping for a little help from you!

I have a spreadsheet that will be distributed to several members of staff. Each of those staff members will need to complete the sheet, save the spreadsheet with the right file name, save it in the right location AND password protect the document.

Therefore to make things easier for them I am hoping to create a VBA.

My aim is this:

- Create a button
- When button pressed by user: Save As - filename (based on two cells in the document, Employee Name and Payroll Month)
- Save it in the correct location on a network drive
- Give the user an option to create a password to protect the document
- Create an administrator password so that office staff can access without knowing what password the user created.

Is any of this even possible? And if so pretty please help me! :banghead:

GTO
08-23-2010, 04:30 AM
Greetings Andy,

Welcome to vbaexpress! There are a lot of good folks here, you'll be able to learn a lot:yes

As to your questions:

I have a spreadsheet that will be distributed to several members of staff. Each of those staff members will need to complete the sheet, save the spreadsheet with the right file name, save it in the right location AND password protect the document.

Therefore to make things easier for them I am hoping to create a VBA.

My aim is this:

- Create a button


Not a problem...


- When button pressed by user: Save As - filename (based on two cells in the document, Employee Name and Payroll Month)
This is certainly doable; you will want to include code to check and make sure the user has not typed in illegal characters.


- Save it in the correct location on a network drive

Presuming this will be a constant, no problem as well.

- Give the user an option to create a password to protect the document
- Create an administrator password so that office staff can access without knowing what password the user created.

Er... As far as I know, a wb can only have one password to open, so I think you'll have to pick.

Hope that helps,

Mark

Andybuck86
08-23-2010, 04:43 AM
Hi Mark, :hi:

Thanks very much for your response! I have a few VBA projects in mind so I'm sure you guys will come in very handy!

Ok so it can be done which is great. Next question is how!

I can create a button and understand how to link code to the button.

Creating the code is my problem.

I have had a change of plan which will hopefully make this easier. I will create two cells, one for the file name and the other for the folder location it needs to be saved to.

Therefore the VBA needs to look at these two cells for the file name and for the location it needs to be saved to.

As for the password, am I able to create a master password for read only, and have the user create a password for edit?

I noticed that these two options are available when saving the document manually.

Thanks again for you help Mark


Andy

lynnnow
08-23-2010, 05:51 AM
Andy,

The passwords that will be created will have to be known by you and the end user. For a read-only file, you can set a master password. Also, for the end user to insert a password, he'll have to tell you the password so that you can open the file, if that is the intention. If you do not have the password to open the file, then you will not be able to collate the data available in that worksheet.

For the file name and location, you can set these two values in a separate sheet of the workbook and make the code refer these two cells to put the created file on the network drive.

We would be able to help you much more if you were able to post an example of your ideas.

Best regards,

Andybuck86
08-23-2010, 06:17 AM
Andy,

The passwords that will be created will have to be known by you and the end user. For a read-only file, you can set a master password. Also, for the end user to insert a password, he'll have to tell you the password so that you can open the file, if that is the intention. If you do not have the password to open the file, then you will not be able to collate the data available in that worksheet.

For the file name and location, you can set these two values in a separate sheet of the workbook and make the code refer these two cells to put the created file on the network drive.

We would be able to help you much more if you were able to post an example of your ideas.

Best regards,

Hello

Thanks for another response.

Sorry I'm new to this and not sure what you mean by posting an example.

I think for what we need a password set by the end user and then a master password that will allow me read only access would be great.

Here's an example of what I need:

A button that when pressed saves the document with a name (taken from cell G1) and saves it into a folder (folder location taken from cell G2).

Then a message should appear saying something along the lines of 'please create a password' with the option for them to create a password.

The macro should then also create a master password for read only access.

Thanks very much for any help - it is really REALLY appreciated!

Andy

lynnnow
08-23-2010, 06:59 AM
I'm not sure if this is what you are looking for but here it is. This is just for illustrative purposes. You might have to explain a little bit more after this...

Andybuck86
08-23-2010, 09:04 AM
I'm not sure if this is what you are looking for but here it is. This is just for illustrative purposes. You might have to explain a little bit more after this...

WOW - Thanks sooo much. This is almost completely what I wanted!

The only thing different is that I wanted the passwords to be the other way around.

I.e. When user is prompted for a password they should be prompted for the write/edit password

The master password should just be read only.

Only the user should be able to edit the sheet.

Again, your help is very much appreciated

lynnnow
08-23-2010, 09:17 AM
Then you just need to interchange the variables for the passwords and you are done. All the best.

Andybuck86
08-23-2010, 09:35 AM
Then you just need to interchange the variables for the passwords and you are done. All the best.

Right I have done that - thanks again.

All seems to be working the way I want it to now, although now I have another problem.

After saving the document and going back into it, it asks me for the master password, and then for the read/write password.

To explain myself better, we have designed an electronic timesheet which will completed by over 100 members of staff, all at different locations. All locations have a pc which has access to a shared network drive. Some of the staff members move around the locations. Therefore all timesheets need to be saved in one folder and accessed by all locations.

The problem with this is that all users will be able to see eachothers timesheets, which obviously can't happen.

Therefore we wanted to password protect each file with a password chosen by the end user.

If every user must enter the master password before they enter their own password they will be able to go into any file as read only, therefore they will be able to peak at everyone else's timesheets.

Is there any way around this?

Thanks again


Andy

lynnnow
08-23-2010, 09:56 AM
I'm not sure if the order of the passwords can be changed, but in effect, from my experience so far, you need to input the file open password first and only then will the read/write password can be inserted.

Andybuck86
08-23-2010, 10:00 AM
I'm not sure if the order of the passwords can be changed, but in effect, from my experience so far, you need to input the file open password first and only then will the read/write password can be inserted.

Ok I will have to re-think how I can do this then. At least I have something to work with thanks to you! :bow:

Just to confirm, there is no way to have 2 seperate passwords that both have write access? One set by the VBA and the other set by the end user?

Thanks again

grichey
08-24-2010, 10:40 AM
What about locking all the cells from being written and having one password to protect/unprotect the sheet and having a prompt for a pw when you open the doc and have 2 different options that toggle the password?

Said another way, the pw that locks or unlocks the cells would only be used in the code. When prompted if the user enters the master pw OR the end user pw, the vba unlocks the sheet.

Andybuck86
08-24-2010, 11:02 AM
What about locking all the cells from being written and having one password to protect/unprotect the sheet and having a prompt for a pw when you open the doc and have 2 different options that toggle the password?

Said another way, the pw that locks or unlocks the cells would only be used in the code. When prompted if the user enters the master pw OR the end user pw, the vba unlocks the sheet.

Hi Grichey,

Thanks for the idea.

I can see where you're coming from but the idea is so that nobody can physically see the data in the spreadsheet (payroll information). The way you have suggested would still allow people read only access right?

Do you think there is a solution to this?

If so please could you provide a working example if possible - that was a pretty please! :bow:

Thanks Grichey

Andy

grichey
08-24-2010, 11:05 AM
Look at Simon's solution using the xlveryhidden sheet functionality to hide everything but a welcome sheet until a pw is entered on this thread:

http://www.vbaexpress.com/forum/showthread.php?t=33742