neelyjer
asked on
Excel VBA IF function question.
Hello Experts,
My question is this:
I need to know the correct function for matching multiple criteria before an event takes place. I have the following code:
' ========================== ========== ========== ========== =====
' If the User name and Password are a manager's Login Then
' Open the Options Label AND include the Add/Remove User Option
' ========================== ========== ========== ========== =====
If txtLoginID.Value = "Manager" Then
If txtLoginPW.Value = "Password" Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else If
.....
Obviously the line -- If txtLoginID.Value = "Manager" Then -- will cause an error with the line that follows. How do I get those two criteria to be considered before the visible options below it are allowed??
My question is this:
I need to know the correct function for matching multiple criteria before an event takes place. I have the following code:
' ==========================
' If the User name and Password are a manager's Login Then
' Open the Options Label AND include the Add/Remove User Option
' ==========================
If txtLoginID.Value = "Manager" Then
If txtLoginPW.Value = "Password" Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else If
.....
Obviously the line -- If txtLoginID.Value = "Manager" Then -- will cause an error with the line that follows. How do I get those two criteria to be considered before the visible options below it are allowed??
ASKER
I will increase the points to 500 if someone can tell me how to get this code to look at a worksheet for individual user names instead of the hardcoded values given above.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Harish has done the right IF statement join for u
or u could just take your existing code and add the END IF in the right place
If txtLoginID.Value = "Manager" Then
If txtLoginPW.Value = "Password" Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
End If 'end here
Else If 'this else if loginid not = Manager
or u could just take your existing code and add the END IF in the right place
If txtLoginID.Value = "Manager" Then
If txtLoginPW.Value = "Password" Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
End If 'end here
Else If 'this else if loginid not = Manager
To look at sheet values
I suggest u place them in variables
Dim sUserID as String
dim sPswd as String
sUserID = Sheets("nameofsheet").Rang e("A1")
'or
sUserID = Sheets("nameofsheet").Cell s(1,1)
same for password
sPassword = Sheets("nameofsheet").Rang e("A2")
'or
sPassword = Sheets("nameofsheet").Cell s(1,2)
Now use sUserID and sPassword in your code checks
I suggest u place them in variables
Dim sUserID as String
dim sPswd as String
sUserID = Sheets("nameofsheet").Rang
'or
sUserID = Sheets("nameofsheet").Cell
same for password
sPassword = Sheets("nameofsheet").Rang
'or
sPassword = Sheets("nameofsheet").Cell
Now use sUserID and sPassword in your code checks
ASKER
mgh_mgharish ,
> If txtLoginID.Value = "Manager" And txtLoginPW.Value = "Password" Then
> frmDataOptions.Visible = True
> ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
> Else If
The Else If portion of the code is returning the error -
Compile Error:
Expected: expression
When the code i ran I receive a syntax error.
> If txtLoginID.Value = "Manager" And txtLoginPW.Value = "Password" Then
> frmDataOptions.Visible = True
> ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
> Else If
The Else If portion of the code is returning the error -
Compile Error:
Expected: expression
When the code i ran I receive a syntax error.
Remove "If"
If txtLoginID.Value = Sheets("SheetName").[A1] And txtLoginPW.Value = Sheets("SheetName").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
' Do whatever you want
End If
If txtLoginID.Value = Sheets("SheetName").[A1] And txtLoginPW.Value = Sheets("SheetName").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
' Do whatever you want
End If
ASKER
Starting to feel like a dumb a$$ here....
Used the following --
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
If txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
Got an error stating Block If without End If. So I added another End If.
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
If txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
End If
Then got error stating --
Runtime error 424 -- Object Required and
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
Is highlighted.
Any Ideas?
Used the following --
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
If txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
Got an error stating Block If without End If. So I added another End If.
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else
If txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
End If
Then got error stating --
Runtime error 424 -- Object Required and
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
Is highlighted.
Any Ideas?
Do you have a sheet named Manager and Users ?
If yes, this should work:
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
ElseIf txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
If yes, this should work:
If txtLoginID.Value = Sheets("Manager").[A1] And txtLoginPW.Value = Sheets("Manager").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
ElseIf txtLoginID.Value = Sheets("Users").[A1] And txtLoginPW.Value = Sheets("Users").[B1] Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
ASKER
yes i do
Can you send the workbook ? (Click my name, you'll get my email id)
Or put it in http://www11.rapidupload.com/ and post the link here
Or put it in http://www11.rapidupload.com/ and post the link here
ASKER
if I remove the And txtLogin..... the error does not occur
txtLoginID or txtLoginPW ?
Try putting brackets:
If((txtLoginID.Value = Sheets("Manager").[A1]) And (txtLoginPW.Value = Sheets("Manager").[B1])) Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
ElseIf((txtLoginID.Value = Sheets("Users").[A1]) And (txtLoginPW.Value = Sheets("Users").[B1])) Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
Try putting brackets:
If((txtLoginID.Value = Sheets("Manager").[A1]) And (txtLoginPW.Value = Sheets("Manager").[B1])) Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
ElseIf((txtLoginID.Value = Sheets("Users").[A1]) And (txtLoginPW.Value = Sheets("Users").[B1])) Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = False
frmSubmit.Visible = True
End If
Harish, EE (unsupported) now have this
http://www.ee-stuff.com
I suggest u use that for requests. Its quite handy!
As debugging, can I suggest u put in variables like I said
then do a debug.print or msgbox so u can confirm what is there
msgbox "UserID in sheet is >" & sUseriD & "<" & vbcrlf & "UserID in txtbox is >" & txtLoginID.Value & "<"
msgbox "Pswd in sheet is ">" & sPassword & "<" & vbcrlf & "Pswd in txtbox is >" & txtLoginPW.Value & "<"
http://www.ee-stuff.com
I suggest u use that for requests. Its quite handy!
As debugging, can I suggest u put in variables like I said
then do a debug.print or msgbox so u can confirm what is there
msgbox "UserID in sheet is >" & sUseriD & "<" & vbcrlf & "UserID in txtbox is >" & txtLoginID.Value & "<"
msgbox "Pswd in sheet is ">" & sPassword & "<" & vbcrlf & "Pswd in txtbox is >" & txtLoginPW.Value & "<"
rockiroads, thanks for the interest, but I disagree with "Its quite handy!"
No probs Harish. The MS Access lot find it useful. Even the page editor has suggested using that. For small minor things that help EE, its useful I think.
Each to their own I guess
Each to their own I guess
ASKER
mgh_mgharish
I EMAILED THE FILE TO YOUR GMAIL ACCOUNT. I tried the link you provided but the site is having issues. Won;t accept my upload.
I EMAILED THE FILE TO YOUR GMAIL ACCOUNT. I tried the link you provided but the site is having issues. Won;t accept my upload.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try using .Text instead of .Value ...
i.e. txtLoginID.Text and txtPassword.Text
i.e. txtLoginID.Text and txtPassword.Text
ASKER
rockiroads,
Although all of my correspondents have been directed towards Harish, I amlook at the other responses as well. I will try the site you recommended.
Thanks
Although all of my correspondents have been directed towards Harish, I amlook at the other responses as well. I will try the site you recommended.
Thanks
ASKER
cj_1969,
Attempted your suggestion and got the same error message as above.
Attempted your suggestion and got the same error message as above.
ASKER
ALL EXPERTS,
Here is the link to file I have uploaded <a href="https://filedb.experts-exchange.com/incoming/ee-stuff/282-Book1--version-1-.zip">!!!</a>
Here is the link to file I have uploaded <a href="https://filedb.experts-exchange.com/incoming/ee-stuff/282-Book1--version-1-.zip">!!!</a>
ASKER
guess I didn't need to code the html huh???
ASKER
ROCKIROADS,
I haven't tried your suggestion of adding variables because I am very new to VBA. I have a good general understanding of the structure and format of a few VBA items, but when it comes to adding variables, I haven't got a clue. If you'd be willing to explain more on how to use the variables, I will gladly give them a try. Please keep in mind, I am trying to learn VBA, although your direct coding would be appreciated I would rather code myself. So if you can provide a sample code then a breakdown of what the code does and what the code is suggesting, I think that would be more advantageous to me.
I haven't tried your suggestion of adding variables because I am very new to VBA. I have a good general understanding of the structure and format of a few VBA items, but when it comes to adding variables, I haven't got a clue. If you'd be willing to explain more on how to use the variables, I will gladly give them a try. Please keep in mind, I am trying to learn VBA, although your direct coding would be appreciated I would rather code myself. So if you can provide a sample code then a breakdown of what the code does and what the code is suggesting, I think that would be more advantageous to me.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry about the comment above ... in reading over it, I don't mean to be or sound demeaning ... its a very common mistake that all of us make ... I alway need a second pair of eyes when coding to find little things like that :)
The names of your textboxes are txtUserID and txtUserPW
But you have coded them as txtLoginID and txtLoginPW
Change one of them, and it'll be okay
But you have coded them as txtLoginID and txtLoginPW
Change one of them, and it'll be okay
ASKER
OOPS & Excellent!!! One last thing and we'll call this question complete. How do I get the password to not be displayed in the text box? I don't think I'd like to have someone looking over my shoulder when I login and see my PW in plain site.
Select your "txtUserPW" on the form and set it "PasswordChar" property to "*"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys so much.
rockiroads - 50 for suggesting upload spot for my file
cj_1969 - 50 for catching the error
mgh_mgharish - 400 for fixing initial code and supply how to display password as '*'
Thanks again for all your help!!!
NEXT QUESTION
https://www.experts-exchange.com/questions/21901132/What-is-the-Not-Equal-sign-for-VBA.html
rockiroads - 50 for suggesting upload spot for my file
cj_1969 - 50 for catching the error
mgh_mgharish - 400 for fixing initial code and supply how to display password as '*'
Thanks again for all your help!!!
NEXT QUESTION
https://www.experts-exchange.com/questions/21901132/What-is-the-Not-Equal-sign-for-VBA.html
Hi neelyjer, glad its sorted. Im just looking at my EE Notifications and saw this
Already sorted.
Already sorted.
If txtLoginID.Value = "Manager" And txtLoginPW.Value = "Password" Then
frmDataOptions.Visible = True
ckbUserAddRemove.Visible = True
frmSubmit.Visible = True
Else If
---
Harish