Mixing
Call specific Userform based on Cell Value
I am creating a system for students to choose their classes at University. I have set up a Login Form that reads the students' UserID and Password from sheet "StudentInformation". There are 200 students belonging to 3 different classes so I have set up a userform for each programme (AMForm, FMForm and HRMForm).
In Column A is their UserID and Column B is their Password. In Column F is where I have the terms, "AM" or "FM" or "HRM" for each programme. When logging in, I want it to open their specific Userform based on the cell value in column F e.g if Cell F2 contains "AM" then AMForm is launched, if F3 contains "FM" then FMForm is launched and if cell F4 contains "HRM" then HRMForm is launched.
I hope this makes sense and any help would be greatly appreciated! I have entered my current login code below.
Private Sub btnLogin_Click()
Dim RowNo As Long
Dim ID As String, PW As String
Dim WS As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
'Ensure User ID and password fields are filled
If Len(Trim(txtUser)) = 0 Then
txtUser.SetFocus
MsgBox "Error. UserID cannot be empty."
Exit Sub
End If
If Len(Trim(txtPass)) = 0 Then
txtPass.SetFocus
MsgBox "Error. Password cannot be empty."
Exit Sub
End If
'Set Range Location
Application.ScreenUpdating = False
Set WS = Worksheets("StudentInformation")
ID = LCase(Me.txtUser)
Set aCell = WS.Columns(1).Find(What:=ID, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPass = aCell.Offset(, 1) Then
MsgBox "Login Successful."
AMForm.Show
Unload Me
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
'If not found
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
CleanExit:
Set WS = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
add a comment |
I am creating a system for students to choose their classes at University. I have set up a Login Form that reads the students' UserID and Password from sheet "StudentInformation". There are 200 students belonging to 3 different classes so I have set up a userform for each programme (AMForm, FMForm and HRMForm).
In Column A is their UserID and Column B is their Password. In Column F is where I have the terms, "AM" or "FM" or "HRM" for each programme. When logging in, I want it to open their specific Userform based on the cell value in column F e.g if Cell F2 contains "AM" then AMForm is launched, if F3 contains "FM" then FMForm is launched and if cell F4 contains "HRM" then HRMForm is launched.
I hope this makes sense and any help would be greatly appreciated! I have entered my current login code below.
Private Sub btnLogin_Click()
Dim RowNo As Long
Dim ID As String, PW As String
Dim WS As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
'Ensure User ID and password fields are filled
If Len(Trim(txtUser)) = 0 Then
txtUser.SetFocus
MsgBox "Error. UserID cannot be empty."
Exit Sub
End If
If Len(Trim(txtPass)) = 0 Then
txtPass.SetFocus
MsgBox "Error. Password cannot be empty."
Exit Sub
End If
'Set Range Location
Application.ScreenUpdating = False
Set WS = Worksheets("StudentInformation")
ID = LCase(Me.txtUser)
Set aCell = WS.Columns(1).Find(What:=ID, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPass = aCell.Offset(, 1) Then
MsgBox "Login Successful."
AMForm.Show
Unload Me
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
'If not found
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
CleanExit:
Set WS = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
1
IfaCell.Offset(, 5) = "AM" Then AMForm.Showetc?
– GSerg
Jan 1 at 13:01
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28
add a comment |
I am creating a system for students to choose their classes at University. I have set up a Login Form that reads the students' UserID and Password from sheet "StudentInformation". There are 200 students belonging to 3 different classes so I have set up a userform for each programme (AMForm, FMForm and HRMForm).
In Column A is their UserID and Column B is their Password. In Column F is where I have the terms, "AM" or "FM" or "HRM" for each programme. When logging in, I want it to open their specific Userform based on the cell value in column F e.g if Cell F2 contains "AM" then AMForm is launched, if F3 contains "FM" then FMForm is launched and if cell F4 contains "HRM" then HRMForm is launched.
I hope this makes sense and any help would be greatly appreciated! I have entered my current login code below.
Private Sub btnLogin_Click()
Dim RowNo As Long
Dim ID As String, PW As String
Dim WS As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
'Ensure User ID and password fields are filled
If Len(Trim(txtUser)) = 0 Then
txtUser.SetFocus
MsgBox "Error. UserID cannot be empty."
Exit Sub
End If
If Len(Trim(txtPass)) = 0 Then
txtPass.SetFocus
MsgBox "Error. Password cannot be empty."
Exit Sub
End If
'Set Range Location
Application.ScreenUpdating = False
Set WS = Worksheets("StudentInformation")
ID = LCase(Me.txtUser)
Set aCell = WS.Columns(1).Find(What:=ID, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPass = aCell.Offset(, 1) Then
MsgBox "Login Successful."
AMForm.Show
Unload Me
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
'If not found
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
CleanExit:
Set WS = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
I am creating a system for students to choose their classes at University. I have set up a Login Form that reads the students' UserID and Password from sheet "StudentInformation". There are 200 students belonging to 3 different classes so I have set up a userform for each programme (AMForm, FMForm and HRMForm).
In Column A is their UserID and Column B is their Password. In Column F is where I have the terms, "AM" or "FM" or "HRM" for each programme. When logging in, I want it to open their specific Userform based on the cell value in column F e.g if Cell F2 contains "AM" then AMForm is launched, if F3 contains "FM" then FMForm is launched and if cell F4 contains "HRM" then HRMForm is launched.
I hope this makes sense and any help would be greatly appreciated! I have entered my current login code below.
Private Sub btnLogin_Click()
Dim RowNo As Long
Dim ID As String, PW As String
Dim WS As Worksheet
Dim aCell As Range
On Error GoTo ErrorHandler
'Ensure User ID and password fields are filled
If Len(Trim(txtUser)) = 0 Then
txtUser.SetFocus
MsgBox "Error. UserID cannot be empty."
Exit Sub
End If
If Len(Trim(txtPass)) = 0 Then
txtPass.SetFocus
MsgBox "Error. Password cannot be empty."
Exit Sub
End If
'Set Range Location
Application.ScreenUpdating = False
Set WS = Worksheets("StudentInformation")
ID = LCase(Me.txtUser)
Set aCell = WS.Columns(1).Find(What:=ID, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
If Me.txtPass = aCell.Offset(, 1) Then
MsgBox "Login Successful."
AMForm.Show
Unload Me
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
'If not found
Else
MsgBox "Incorrect UserID or Password. Please try again.", vbOKOnly
End If
CleanExit:
Set WS = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub
excel vba excel-vba
excel vba excel-vba
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
edited Jan 7 at 8:53
Pᴇʜ
23.7k62952
23.7k62952
asked Jan 1 at 12:02
MJ98MJ98
526
asked Jan 1 at 12:02
MJ98MJ98
526
asked Jan 1 at 12:02
MJ98MJ98
526
526
1
IfaCell.Offset(, 5) = "AM" Then AMForm.Showetc?
– GSerg
Jan 1 at 13:01
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28
add a comment |
1
IfaCell.Offset(, 5) = "AM" Then AMForm.Showetc?
– GSerg
Jan 1 at 13:01
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28
1
1
If
aCell.Offset(, 5) = "AM" Then AMForm.Show etc?– GSerg
Jan 1 at 13:01
If
aCell.Offset(, 5) = "AM" Then AMForm.Show etc?– GSerg
Jan 1 at 13:01
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995263%2fcall-specific-userform-based-on-cell-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995263%2fcall-specific-userform-based-on-cell-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If
aCell.Offset(, 5) = "AM" Then AMForm.Showetc?– GSerg
Jan 1 at 13:01
Thank you, GSerg! :)
– MJ98
Jan 2 at 14:28