Password protecting a MACRO which then copies/ pastes data from another workbook

[MatthewOliver]

Morning (or afternoon or evening),

If someone could help this one it would really help me out.

I have a Workbook which has a button 'Update' on a worksheet called "Front". Ideally when the 'Update' button is pressed I would like the user to be requested to input a password. If not correct they could still view the workbook but not update it.

If the password is correct I would ideally like the Macro to open up two other workbooks called "Book 1" and "Book 2" both located in the folder C:\Mydocuments\Database\Data" and copy data from worksheets contained within.

For "Book 1" the worksheet is "Sheet 1". The Range is A2:M200 (but if you can copy the whole worksheet this would work)
For "Book 2" the worksheet is "Data". The range is the same as above and ok again if it's the whole worksheet.

If possible I would like the macro to shut down "Book 1" and "Book 2" and paste the data into the active workbook (named: Database). The pasting location in the active workbook is "Sheet 9" & "Sheet 10" (doesn't matter which one contains which).

Fingers crossed this makes sense and thank you to anyone who can help me out!

Matthew

 

[dmt32]

Hi,
Welcome to forum
If users are opening your workbook over a corporate network then I would suggest that you avoid passwords & just use network usernames to make your copy button visible to valid users only.

To Achive this you would:

• add a sheet & name it Users
• In column A enter all valid network usernames

Hide the sheet when done

Add following codes

In ThisWorkBook code page

 Private Sub Workbook_Open()    
    Worksheets("Front").CommandButton1.Visible = IsValidUser
End Sub

In STANDARD module

 Function IsValidUser() As Boolean    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Users")
    IsValidUser = Not IsError(Application.Match(Environ("Username"), ws.Columns(1), False))
End Function

When your workbook opens your CommandButton on Sheet Front should only be visible to those users on your list.

Change commandbutton name shown in RED if required.


Hope Helpful

Dave

 

[MatthewOliver]

Cheers for the reply Dave!

Nope unfortunately the names of the people who will be accessing will not be fully known. It'll pretty much be open to anyone who has access to the system but can only be Updated by a few............. if that makes sense??

 

[dmt32]

Cheers for the reply Dave!

Nope unfortunately the names of the people who will be accessing will not be fully known. It'll pretty much be open to anyone who has access to the system but can only be Updated by a few............. if that makes sense??

I take it that the few that can update the data are known? then if so, suggestion should work - you need only to enter those that update the data network username in to the list & your copy button will only be visible for them. All other users will not see it.

Dave

 

[MatthewOliver]

Oh I get the logic now!

Will try when I get back to my laptop.

Cheers Dave ?