Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VBA IF function question.

Posted on 2006-06-26
32
Medium Priority
?
629 Views
Last Modified: 2006-11-18
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??
0
Comment
Question by:neelyjer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 6
  • +1
32 Comments
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16986954
Hi,

    If txtLoginID.Value = "Manager" And txtLoginPW.Value = "Password" Then
        frmDataOptions.Visible = True
        ckbUserAddRemove.Visible = True
        frmSubmit.Visible = True
    Else If
---
Harish
0
 

Author Comment

by:neelyjer
ID: 16986958
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.
0
 
LVL 37

Assisted Solution

by:Harisha M G
Harisha M G earned 1600 total points
ID: 16986978
neelyjer, assuming you have username in A1 and password in B1..

    If txtLoginID.Value = Sheets("SheetName").[A1] And txtLoginPW.Value = Sheets("SheetName").[B1] Then
        frmDataOptions.Visible = True
        ckbUserAddRemove.Visible = True
        frmSubmit.Visible = True
    Else If
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 65

Expert Comment

by:rockiroads
ID: 16986995
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16987011
To look at sheet values

I suggest u place them in variables

Dim sUserID as String
dim sPswd as String

sUserID = Sheets("nameofsheet").Range("A1")
'or
sUserID = Sheets("nameofsheet").Cells(1,1)

same for password

sPassword = Sheets("nameofsheet").Range("A2")
'or
sPassword = Sheets("nameofsheet").Cells(1,2)

Now use sUserID and sPassword in your code checks

0
 

Author Comment

by:neelyjer
ID: 16987023
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.
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16987043
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
0
 

Author Comment

by:neelyjer
ID: 16987261
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?
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16987323
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
0
 

Author Comment

by:neelyjer
ID: 16987403
yes i do
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16987427
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
0
 

Author Comment

by:neelyjer
ID: 16987611
if I remove the And txtLogin.....  the error does not occur
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16987636
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16987920
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 & "<"



0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16987997
rockiroads, thanks for the interest, but I disagree with "Its quite handy!"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16988099
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
0
 

Author Comment

by:neelyjer
ID: 16989833
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.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 16990193
neelyjer
have u tried uploading via http://ee-stuff.com
I know you are working with Harish but for other experts who want to help or for other people who want to read, its only fair that you place in a common area.
0
 
LVL 22

Expert Comment

by:cj_1969
ID: 16992526
Try using .Text instead of .Value ...
i.e. txtLoginID.Text and txtPassword.Text
0
 

Author Comment

by:neelyjer
ID: 16992725
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
0
 

Author Comment

by:neelyjer
ID: 16993058
cj_1969,

Attempted your suggestion and got the same error message as above.
0
 

Author Comment

by:neelyjer
ID: 16993130
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>
0
 

Author Comment

by:neelyjer
ID: 16993135
guess I didn't need to code the html huh???
0
 

Author Comment

by:neelyjer
ID: 16993191
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.

0
 
LVL 22

Assisted Solution

by:cj_1969
cj_1969 earned 200 total points
ID: 16993624
Ok, I hope you don't feel too silly about this ...
I looked at your document and got it working ...
the .Value paramter does work ...
you need to reference the password field with the correct name ... you are calling txtLoginPW but the field is named txtPW
0
 
LVL 22

Expert Comment

by:cj_1969
ID: 16993791
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  :)
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16994857
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
0
 

Author Comment

by:neelyjer
ID: 16995502
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.
0
 
LVL 37

Expert Comment

by:Harisha M G
ID: 16995520
Select your "txtUserPW" on the form and set it "PasswordChar" property to "*"
0
 
LVL 37

Accepted Solution

by:
Harisha M G earned 1600 total points
ID: 16995602
Since your txtUserPW is hidden behind the frame, you may follow this procedure:

In the Properties window, select txtUserPW in the combobox. Then set the PasswordChar property to *
0
 

Author Comment

by:neelyjer
ID: 16995712
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

http://www.experts-exchange.com/Applications/Q_21901132.html
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16995981
Hi neelyjer, glad its sorted. Im just looking at my EE Notifications and saw this
Already sorted.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange’s free Course of the Month is focused on CompTIA IT Fundamentals.
If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloud’s Office 365 is relatively simple…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question