Creating UserId and Password.

[Navi]

Hi All,

Need a help in VBA form.

I have a excel with 1000 rows filled with 96 columns. there are different sales manager for whom specific rows are allotted, say for sales manager 1 we have 10 rows, for sales manager 2 we have 25 rows and so on.

we will be sharing the entire excel to all sales manager to update on there respective rows.

currently Have a VBA for providing Username and password as common. just wanted to know if we can restrict the password to be unique, may be salesmanager 1 can enter his Id as user name and password with combination of first 3 letters of his name and date of joining so excel is opened and only the rows allotted to him is visible to him.

kindly let me know if this can be done.

Thanks & Regards,
Naveen

 

[vletm]

we will be sharing the entire excel to all sales manager to update on there respective rows.
How do You share something? Is it shared Excel file?
If managers should update only own part of that file then it would 'easier' to send own that part file to each manager.
And later make one file.

Do You use internal network? ... then everyone has already own ID there.
If no ... then ... Excel has also 'username'.
I won't make any new login procedure to file!
Then they could just login with others ID and password.

>> It's possible, but how useful? if more than one user uses in same time! <<

 

[Deepak]

Something like...

Option Explicit

Public Const wks                As String = "ABCD"
Public Const wksPassword        As String = "123"
Public Const UserRange          As String = "RAM,SHYAM"
Public Const UserRangePassword  As String = "1,2"

Public vRng As Variant, vPass As Variant



Sub LockCellsWithUniquePassword()

vRng = Split(UserRange, ",")
vPass = Split(UserRangePassword, ",")

With Sheets(wks)
    .Unprotect wksPassword
    .Protection.AllowEditRanges.Add Title:=vRng(0), Range:=Rows("2:4"), Password:=vPass(0)
    .Protection.AllowEditRanges.Add Title:=vRng(1), Range:=Rows("5:8"), Password:=vPass(1)
    .Range("2:1000").EntireRow.Hidden = True
    .Protect wksPassword
End With

End Sub


Sub Hide_Unhide(ByVal strUser As String)

vRng = Split(UserRange, ",")

If Not IsError(Application.Match(strUser, vRng, 0)) Then
Else
    MsgBox "Invalid User", vbCritical
    Exit Sub
End If

With Sheets(wks)
    .Unprotect wksPassword
    .Range("2:10000").EntireRow.Hidden = True
    .Range(.Protection.AllowEditRanges.Item(Application.Match(strUser, vRng, 0)).Range.Address).EntireRow.Hidden = False
    .Protect wksPassword
End With

End Sub

Sub test2()
Hide_Unhide "RAM"
End Sub

Nothing is secure for advance user!!

 

[Navi]

we will be sharing the entire excel to all sales manager to update on there respective rows.
How do You share something? Is it shared Excel file?
If managers should update only own part of that file then it would 'easier' to send own that part file to each manager.
And later make one file.

Do You use internal network? ... then everyone has already own ID there.
If no ... then ... Excel has also 'username'.
I won't make any new login procedure to file!
Then they could just login with others ID and password.

>> It's possible, but how useful? if more than one user uses in same time! <<

Will be sharing the file through googledocs.. hence if they can open with network id and password also is fine. but how to connect the network password to get accepted by Excel.

 

[vletm]

Navi
... You skipped few points!
1)
Is that file 'Excel's shared file' like => [Shared]
or not?
If YES then many people could use one file same time.
If NO then ... no!

If YES then 'normal Protecting' not work
( ... or maybe, but then have to pull out other users, not verified),
If NO then 'normal Protecting' works.

2) GoogleDocs ... never tested!
But You could test how would it work with sample file? (not Shared)
It will show 'username' ... or 'username'.
... and if lucky, it will unprotect one of those sheets too.

3) Why need still password?
If user already have to use login procedure!

> Many things are possible, but many things have to know before... <