Forum Home
Press F1
Thread ID: 68201	2006-04-21 01:02:00	VBA in Excel	pico (4752)	Press F1

Post ID	Timestamp	Content	User
448141	2006-04-22 02:34:00	Hi Pico Good stuff on what you've done so far. As Parry says, DialogSheets are pretty old; if you're using Excel 97 or higher I'd really look at creating a UserForm as suggested. Anyway, the reason you're now getting an error in the vlookup is because the username variable is an object rather than string. Who knows why the debugger expects objects?? Try: Set ActualPassword = Application.WorksheetFunction.VLookup(cStr(Usernam e), LoginTable, 2, False) to see if it will fix it (without the weird spacing caused by the forum).	Antmannz (6583)
448142	2006-04-23 00:54:00	Anyway, the reason you're now getting an error in the vlookup is because the username variable is an object rather than string. Who knows why the debugger expects objects?? Hi Antmannz, Excel expects an object because the SET keyword is only used for setting objects. For other variables you just go variablename = whatever. Pico, I dont know why your persisting with DialogSheets unless you actually do have a very old version of Excel. DialogSheets arent even in the VBE Help in my version (2003) so if you have any problems with them your going to find it difficult to get help. I suggest using a userform (which have superceded dialoghsheets) but up to you. I suspect DialogSheets are an object as are ranges but a lookup should be a variant I believe as it may return an error, a double or string data type. So lose the SET keyword for that line -undefined variables are automatically variants so you dont need to specifically declare if you dont want to. hth	Parry (5696)
448143	2006-04-24 00:37:00	Hi all, Thanks for the advice everyone . I figured out that when you use "set" it applies to objects and when the debugger was running it was expecting an object after the set command, so I removed it . Thanks for putting me onto using userforms in VB rather than using dialogsheets . They seem much easier to work with . Option Explicit Option Compare Text Sub LoginButton_Click() Dim Username As String Dim TryPassword As String Dim ActualPassword As String Dim LoginTable As Range Dim NumTries As Integer TryPassword = LoginForm . PasswordBox . Value Username = LoginForm . UsernameBox . Value LoginTable = ThisWorkbook . Sheets("DataSheet") . Range("LoginTable") . Address(external:=True) ActualPassword = Application . WorksheetFunction . VLookup(Username, LoginTable, 2) If TryPassword = ActualPassword Then NumTries = 0 If Username = "Operations" Then 'enter sheets as sheet . show MsgBox "Operations logged in" ElseIf Username = "Sales Division" Then 'enter sheets as sheet . show MsgBox "Sales Division logged in" ElseIf Username = "Technical" Then 'enter sheets as sheet . show MsgBox "Technical logged in" End If Else 'wrong pwd . First check how many wong password attempts If NumTries = 3 Then MsgBox "Sorry you have exceeded the # password tries", vbCritical, "Too Many Password Attempts" Application . ThisWorkbook . Close Else MsgBox "Sorry thats the incorrect password . ", vbCritical, "Incorrect Password" NumTries = NumTries + 1 'increment number of tries End If End If End Sub Private Sub CancelButton_Click() 'Application . ThisWorkbook . Close End Sub Private Sub UserForm_Activate() 'This sets the login box to the correct source . LoginForm . UsernameBox . Value = "" LoginForm . PasswordBox . Value = "" LoginForm . Controls("UsernameBox") . RowSource = Range("Users") . Address(external:=True) End Sub 'Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'If CloseMode = vbFormControlMenu Then 'Cancel = True 'MsgBox "Please press the cancel button . " 'End If 'End Sub I get the error: Object variable or with block variable not set when I try I click the LoginButton . The debugger stops at the line thats in bold . I am trying to assign a range to the variable LoginTable (dim as range) . It refers to a range named in Excel on the "Datasheet" worksheet with the same name, "LoginTable" (would the use of the name twice cause this error?) . I'm not sure of the use of " . Address(external:=True)" at the emd of that statement either, just trying to apply the code that you supplied Parry . All this code is in the code section for a userform named LoginForm . Thanks for all your help everyone .	pico (4752)
448144	2006-04-24 02:07:00	Hi, a few things to fix. 1) The NumTries variable needs to be a public variable outside the procedure. You use the keyword Public instead of Dim. The reason why is that the value of variables within a procedure only contain their values while the procedure is running so NumTries is always 1. Public variables on the other hand stay in memory until the workbook is closed. 2) You Dim LoginTable as a range (which is good) but are trying to apply an address (which is a string) into that variable. Also, a range is a type of object (so is a sheet, workbook, drawing, picture & controls) so they need to have the keyword SET to apply the object to the variable. 3) In this case the VLOOKUP will fail if the username isnt in your list. A formula error will cause a run time error 1004 so you can use OERN to get the value then see if an error occurred and act accordingly. hth Public NumTries As Integer Sub LoginButton_Click() Dim Username As String Dim TryPassword As String Dim ActualPassword As String Dim LoginTable As Range 'TryPassword = LoginForm.PasswordBox.Value TryPassword = LoginForm.PasswordBox.Value Username = LoginForm.UsernameBox.Value Set LoginTable = ThisWorkbook.Sheets("DataSheet").Range("LoginTable") On Error Resume Next 'if username cannot be found the next line will fail ActualPassword = Application.WorksheetFunction.VLookup(Username, LoginTable, 2) If Err.Number <> 0 Then Err.Clear 'clear error On Error GoTo 0 'turn off OERN GoTo FailedPassword 'go to heading FailedPassword Else On Error GoTo 0 'turn off OERN End If If TryPassword = ActualPassword Then NumTries = 0 If Username = "Operations" Then 'enter sheets as sheet.show MsgBox "Operations logged in" ElseIf Username = "Sales Division" Then 'enter sheets as sheet.show MsgBox "Sales Division logged in" ElseIf Username = "Technical" Then 'enter sheets as sheet.show MsgBox "Technical logged in" End If Else FailedPassword: 'heading 'wrong pwd. First check how many wong password attempts If NumTries = 3 Then MsgBox "Sorry you have exceeded the # password tries", vbCritical, "Too Many Password Attempts" Application.ThisWorkbook.Close Else MsgBox "Sorry thats the incorrect password.", vbCritical, "Incorrect Password" NumTries = NumTries + 1 'increment number of tries End If End If End Sub	Parry (5696)
448145	2006-04-24 04:45:00	Parry, I think you should change your custom title from "Excel Specialist" to "Excel Legend." :p Anyway, I did notice Pico that you're using: ' If Username = "something" Then ' a lot. You could use the Select Case statement in this area. The Select Case statement evaluates the variable only once, vs. the 3 times that are happening at the moment - which allows for greater application speed. Admittedly, you will probably not notice the difference in this case, but it is a good habit to get into. NumTries = 0 Select Case Username Case "Operations" 'enter sheets as sheet.show MsgBox "Operations logged in" Case "Sales Division" 'enter sheets as sheet.show MsgBox "Sales Division logged in" Case "Technical" 'enter sheets as sheet.show MsgBox "Technical logged in" Case Else ' allows for any other value ' insert code here End Select	Antmannz (6583)
448146	2006-04-24 22:17:00	Thanks Parry and Antmannz I got the code to work, using both of your suggestions. BTW what is it that you both do, obviously you must have done a lot of work using Excel and VBA?	pico (4752)
448147	2006-04-24 22:38:00	Im a BA consultant. I like Excel and learned VBA by reading John Walkenbach's Excel VBA Power Programming book and helping others on Mr Excel (www.mrexcel.com) where Im an MVP.	Parry (5696)
448148	2006-04-27 08:36:00	Good to hear you got it all working. I'm a freelance IT consultant, now mainly handling web design and Server 2003 maintenance. I cut my teeth on Excel way back about 1994 (version 4, I think); then started on VBA in Excel about 3 years after that. I haven't really used Excel for the last 3 to 4 years, so I'm a little rusty as I said in a previous post.	Antmannz (6583)
448149	2006-04-28 09:03:00	Cool thanks guys. All this makes me wish I had studied IT aswell as/instead of chemistry. I'm interested in working more on this type of thing. I have used Excel for a long time and have most of the standard stuff under my belt, but have only starting doing coding lately. I might hear from you guys again when next I encounter a problem.	pico (4752)
1 2