Creating multiple login usernames/passwords in workbook

[SonicBoomGolf]

I am sure that what I want to do can be done, but I don't have any idea where to start with this. On that note, this post is meant to be a brainstorming session

I have a tab of a workbook devoted to inputing information that is used to develop a profile on a particular person. Upon completing all the fields, the person hits my "Create Profile" button and their information is entered into my "database" (a different tab in the workbook). Pretty straight forward so far.

What I would like to do is have the person also enter a username and password on my profile creation tab so that when they enter the workbook next time they can enter their username and password and they will be directed to their specific tab in the workbook (for arguments sake we will make this their personal "profile" tab)

I guess that what I am looking for is a login prompt upon opening the file that asks for the users name and password. The login would check the "database" tab to see if the login matches the current username/password combinations on file. If the login matches, they are directed to their special tab. If they don't have a username/password then there will also be the option to create a profile.

There would be multiple profiles, each with a different login name/password chosen by the creator. None of them are accessing the file at the same time.

So what do the pros think??

 

[SonicBoomGolf]

Nimrod,

I agree with you 100% about using their computer login ID to identify who they are. Unfortunatly, this will not be sufficient for my purposes. What I am trying to do is replicate in Excel the way that I want my web site built so that I can properly commuicate the process to my developers/programmers. I am about 90% finished right now.

In my file I have created a main profile creation tab in which members will enter all necessary data. When they submit the data it is input into another tab which represents my core customre database. The unique username and password is created by the individual when the profile is created and is also entered into the database.

The reason for the login username/password is because each individual will be entering their own unique username and pasword.

So let's say for arguments sake that I have 3 people in the same household, with one computer, using my "Excel" web site. Each person has a different profile and receives a different service. As a result there are three unique users with different usernames and passwords using the same computer in the house. Computer ID can't work.

What I am trying to do is have it where, upon opening the file, the user is prompted for their unique username and password. If the figures match what is on file then they are directed to their respective tab. If they do not have a profile, then they are given the option of creating one. On one computer I would like to be able to login with one id, logout, login with another id, logout, etc.......

Long story short, I need help creating a login screen that will reference my database for username/password matches. Any ideas? BTW, thanks for the help!!

HalfAce...That link looks promising I'll let you know if it works. Thanks!!

 

[Nimrod]

Option Explicit

'////////////////////////////////////////////////////////////////////
'Password masked inputbox
'Allows you to hide characters entered in a VBA Inputbox.
'
'Code written by Daniel Klann
'March 2003
' LINE : http://www.mrexcel.com/board2/viewtopic.php?t=44926&highlight=password+inputbox
'////////////////////////////////////////////////////////////////////


'API functions to be used
Private Declare Function CallNextHookEx Lib "user32" (ByVal hHook As Long, _
ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long

Private Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long

Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" _
(ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long

Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long

Private Declare Function SendDlgItemMessage Lib "user32" Alias "SendDlgItemMessageA" _
(ByVal hDlg As Long, ByVal nIDDlgItem As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, _
ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long

'Constants to be used in our API functions
Private Const EM_SETPASSWORDCHAR = &HCC
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
Private Const HC_ACTION = 0


Private hHook As Long


Public Function NewProc(ByVal lngCode As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Dim RetVal
Dim strClassName As String, lngBuffer As Long

If lngCode < HC_ACTION Then
NewProc = CallNextHookEx(hHook, lngCode, wParam, lParam)
Exit Function
End If

strClassName = String$(256, " ")
lngBuffer = 255

If lngCode = HCBT_ACTIVATE Then 'A window has been activated

RetVal = GetClassName(wParam, strClassName, lngBuffer)

If Left$(strClassName, RetVal) = "#32770" Then 'Class name of the Inputbox

'This changes the edit control so that it display the password character *.
'You can change the Asc("*") as you please.
SendDlgItemMessage wParam, &H1324, EM_SETPASSWORDCHAR, Asc("*"), &H0
End If

End If

'This line will ensure that any other hooks that may be in place are
'called correctly.
CallNextHookEx hHook, lngCode, wParam, lParam

End Function

Function InputBoxDK(Prompt, Title) As String
Dim lngModHwnd As Long, lngThreadID As Long

lngThreadID = GetCurrentThreadId
lngModHwnd = GetModuleHandle(vbNullString)

hHook = SetWindowsHookEx(WH_CBT, AddressOf NewProc, lngModHwnd, lngThreadID)

InputBoxDK = InputBox(Prompt, Title)
UnhookWindowsHookEx hHook

End Function

--------------------------------------------------------------------------------




To use this code just try something like this:-


code:
--------------------------------------------------------------------------------

Sub Test()
Dim x
x = InputBoxDK("Type your password here.", "Password Required")

If x <> "yourpassword" Then
MsgBox "Youd didn't enter a correct password."
End If

End Sub

 

[SonicBoomGolf]

Wow, that is some serious coding going on there and I understand almost none of it.

My only question is: What part of the code that you sent me do I have to alter to have the values entered in the input box cross referenced with the values in my database?

Thanks a ton for the help. It is greatly appreciated.

 

[Nimrod]

trackman69:

If I was doing this I would have a custom form input box pop up when the user first open the book . Do you know how to go into VBEditor and create a small user form ?? If you not willing to do that I don't want to get into a long explainaton on how to set it up :wink: .

Several other things you will need to do:
1: Set up a Load form call in the On_Open event
2. Have the ON_close event hide all sheets but one telling user they must have macro's enabled
3. Create a custom user form with 2 text boxes and 2 command buttons

 

[SonicBoomGolf]

I know how to do some of what you suggested. For the stuff I don't know....well, I guess that this will be a learning experience.

I was going to use the following link, suggested by HalfAce, as a reference:
http://www.mrexcel.com/board2/viewtopic.php?t=135672&highlight=

Do you think this will be the correct process for what I want to do??

 

[Nimrod]

trackman69:

There's many ways to start , but what I would do is start by making a custom userform.
1. Open VBEdior
2. On tool bar click "Insert" .... "userform"
3. Form the tool box drag 2 text boxes and 2 command buttons onto form.

4. Post back when this is done :wink:

 

[SonicBoomGolf]

Finished the first part....

 

[HalfAce]

Nimrod,
WetWare. I like it!
We don't all log in under the same username, the machine (and the excel app.) never gets shutdown other than to reboot occasionally.

That inputbox password character code is great. I've used that for a while now on some other stuff. I highly recommend it.

In order to avoid confusing the issue here I'll just sort of bow out & sit on the sidelines keeping an eye on it. I don't want to answer any questions unless I can be sure they're on the same track as what you're thinking or if it's some cut & dried "how to" type question.

Dan

 

[SonicBoomGolf]

The following code is working for me in terms of logging in, minus the Sheet part, as I mention below.

Private Sub CommandButton1_Click()
Dim logName As Range
Dim pWord As Range
Dim Sheet As Range

FinalRow = Sheets("Sample Database").Range("A65536").End(xlUp).Row

On Error GoTo errhandle
Set logName = Sheets("Sample Database").Range("A4:A" & FinalRow).Find(TextBox1)
Set pWord = Sheets("Sample Database").Range("B4:B" & FinalRow).Find(TextBox2)
Set Sheet = logName.Offset(0, 28)
If TextBox1 = "" Then
GoTo errhandle
End If
If Not logName Is Nothing Then
If Not pWord Is Nothing Then
If pWord = logName.Offset(0, 1) Then
MsgBox "Welcome - Log in successful"
Sheets("Sheet").Select
Me.Hide
Exit Sub
End If
Else
GoTo errhandle
End If
Else
GoTo errhandle
End If
GoTo errhandle
Exit Sub
errhandle:
MsgBox "Invalid details - please try again"
TextBox1.Text = ""
TextBox2.Text = ""
End Sub

The only addition I want to make is have it where, upon logging in successfully, have the individual directed to their specific tab. My attempt to do this is through the following portions of code:

Dim Sheet As Range and
Sheets("Sheet").Select

The code above works until I add this portion of the code.

Basically, wherever the logName is found in column A, the name of the tab they should be directed to is 28 columns to the right. How can I do this?

 

[Nimrod]

HalfAce ... please don't bow out ... I only want to contribute to the conversation not take it over

Sorry if I talk to much ... it's only because of my passion for excel . ray: