hey
I'm name is Collymore and I'm not very good with excel and vba and
oh yes first time asking question on mrexcel.com but I visit this website everyday to learn
But i need to grant particular user from excel vba (login form) to access its own user database(ms access)
Ability
The user must have ability to delete and append data into access database
and view entire database on worksheet
I have the login form as follows
Credentials:
are saved in the excel worksheet (username and password)
Multiform code:
Option Explicit
Public attempt As Integer
Private Sub cancelBtn_Click()
username_input.Text = ""
password_input.Text = ""
End Sub
Private Sub exitBtn_Click()
Dim iExit As VbMsgBoxResult
iExit = MsgBox("Confirm if you want to Exit!!!", vbQuestion + vbYesNo, "Registration System")
If iExit = vbYes Then
MsgBox "Enjoy your day!!!", vbOKOnly + vbCritical
Unload Me
End If
End Sub
Private Sub loginBtn_Click()
Dim username As String
Dim password As String
Dim username_1, password_1 As String
'Store Range(A2,F2,C2) cells '
username = Sheets("Sheet1").Range("A2").Value
username_1 = Sheets("Sheet1").Range("A3").Value
password = Sheets("Sheet1").Range("F2").Value
password_1 = Sheets("Sheet1").Range("F3").Value
If (username_input = username Or username_input = username_1 And password_input = password Or password_input = password_1) Then
MsgBox "Login Success!!", vbOKOnly + vbQuestion
Unload Me
Application.Visible = True
Else
If (username_input <> username Or username_input <> username_1) Then
MsgBox "You have entered an invalid username or email.", vbOKOnly + vbExclamation
ElseIf (password_input <> password) Then
MsgBox "You have entered incorrect password.", vbOKOnly + vbExclamation
'Locks after 3 tries'
Else
If attempt < 3 Then
MsgBox "Invaild login credentials. Please try again.", vbOKOnly + vbCritical, "Invaild login Details"
attempt = attempt + 1
Else
MsgBox "You have exceeded the maximum numer of login attempts.", vbOKOnly + vbCritical, "Invalid Credentials"
Unload Me
ThisWorkbook.Close saveChanges:=False
Application.Visible = True
attempt = 0
End If
End If
End If
End Sub
Private Sub UserForm_Initialize()
Me.username_input.Value = ""
Me.password_input.Value = ""
Me.username_input.SetFocus
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub
from login:
if username and password is true then
open userform2 for that particular logged in user
userform2(delete/append/import) database
does the following:
1. update(Append) particular column and row data entires from excel to access database table of the particular user(from loginform)
2. delete particular column and row entries from excel to access database table
3. Import/ view entire columns and rows of the database in the listbox
the problem is i dont have any idea to plan userform2
please help!!
I'm name is Collymore and I'm not very good with excel and vba and
oh yes first time asking question on mrexcel.com but I visit this website everyday to learn
But i need to grant particular user from excel vba (login form) to access its own user database(ms access)
Ability
The user must have ability to delete and append data into access database
and view entire database on worksheet
I have the login form as follows
Credentials:
are saved in the excel worksheet (username and password)
Multiform code:
Option Explicit
Public attempt As Integer
Private Sub cancelBtn_Click()
username_input.Text = ""
password_input.Text = ""
End Sub
Private Sub exitBtn_Click()
Dim iExit As VbMsgBoxResult
iExit = MsgBox("Confirm if you want to Exit!!!", vbQuestion + vbYesNo, "Registration System")
If iExit = vbYes Then
MsgBox "Enjoy your day!!!", vbOKOnly + vbCritical
Unload Me
End If
End Sub
Private Sub loginBtn_Click()
Dim username As String
Dim password As String
Dim username_1, password_1 As String
'Store Range(A2,F2,C2) cells '
username = Sheets("Sheet1").Range("A2").Value
username_1 = Sheets("Sheet1").Range("A3").Value
password = Sheets("Sheet1").Range("F2").Value
password_1 = Sheets("Sheet1").Range("F3").Value
If (username_input = username Or username_input = username_1 And password_input = password Or password_input = password_1) Then
MsgBox "Login Success!!", vbOKOnly + vbQuestion
Unload Me
Application.Visible = True
Else
If (username_input <> username Or username_input <> username_1) Then
MsgBox "You have entered an invalid username or email.", vbOKOnly + vbExclamation
ElseIf (password_input <> password) Then
MsgBox "You have entered incorrect password.", vbOKOnly + vbExclamation
'Locks after 3 tries'
Else
If attempt < 3 Then
MsgBox "Invaild login credentials. Please try again.", vbOKOnly + vbCritical, "Invaild login Details"
attempt = attempt + 1
Else
MsgBox "You have exceeded the maximum numer of login attempts.", vbOKOnly + vbCritical, "Invalid Credentials"
Unload Me
ThisWorkbook.Close saveChanges:=False
Application.Visible = True
attempt = 0
End If
End If
End If
End Sub
Private Sub UserForm_Initialize()
Me.username_input.Value = ""
Me.password_input.Value = ""
Me.username_input.SetFocus
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub
from login:
if username and password is true then
open userform2 for that particular logged in user
userform2(delete/append/import) database
does the following:
1. update(Append) particular column and row data entires from excel to access database table of the particular user(from loginform)
2. delete particular column and row entries from excel to access database table
3. Import/ view entire columns and rows of the database in the listbox
the problem is i dont have any idea to plan userform2
please help!!