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
448131	2006-04-21 01:02:00	Hi all, I am working on an Excel file. I am trying to write some VB code for a dialog box that users enter a username and password into. Which then will show sheets specific to that user. Here is the code I have so far: Sub Button2_Click() On Error GoTo Button2_Click_Err If Application.WorksheetFunction.VLookup(DialogSheets ("Login").UsernameBox, Worksheets(7).Range(Login), 2, False) = PasswordBox Then MsgBox ("yeah") Else: MsgBox ("nah") End If Button2_Click_Exit: Exit Sub Button2_Click_Err: Error$ Resume Button2_Click_Exit End Sub When I run the dialog box nothing happens when you click Button2 (the OK button). I don't know how to refer to the values in the boxes of the dialog box (UsernameBox and PasswordBox). Login is a named range in Excel, a table containing the login info. I am new to writing code and have had some recent experience using it in Access but it seems that the way VBA interacts with these two programs is quite different. Thanks.	pico (4752)
448132	2006-04-21 01:16:00	If you're creating username/password to show / hide Excel worksheets, it is pretty minimal security. The standard solution is a file server, and NTFS / share permissions, and separate spreadsheets, possibly linked. Anyhow, when you right-click the button, choose "View code". This will put you into the correct event sub. In the left margin, you can click once and set up a breakpoint. A large red dot will appear, and the line will highlight in red. Then you can use various option under the "debug" menu.	kingdragonfly (309)
448133	2006-04-21 03:09:00	I know that thisi s minimal security, I did suggest having multiple files but they didn't want that. If someone wants to they will be able to hack this document this is merely to allow people to populate only the information that they are allocated. I can't set file permissions either because this will be a template file which could be sent anywhere. I'm not sure how to use the breakpoint thing either, but I will give it a go.	pico (4752)
448134	2006-04-21 03:10:00	The error is in your if statement; I think it should be: If Application.WorksheetFunction.VLookup(DialogSheets ("Login").UsernameBox, Worksheets(7).Range(Login), 2, False) = {OK Response} Then MsgBox ("yeah") Else MsgBox ("nah") End If Also, you can retrieve the values from the UsernameBox and PasswordBox with "UsernameBox.Text" and "PasswordBox.Text" I don't know if you know this, but you can use the ***** mask the on the password box by setting the PasswordChar property to "*".	Antmannz (6583)
448135	2006-04-21 03:22:00	I noticed the ":" too. But in this case it serves as a statement seperator. Ugly, but it works.	kingdragonfly (309)
448136	2006-04-21 04:47:00	Ahhh wait. You're using a vlookup to match the username info to the password required. Ignore my previous post, it won't fix your problem. However, I can't tell where you're getting the username info from - have you previously stored it in a variable, or are both textboxes on the same form? Also, when in breakpoint mode, you can use the F8 button to step through the code one line at a time: this will let you see what's happening through each step.	Antmannz (6583)
448137	2006-04-21 05:11:00	Hi Pico, I believe DialogSheets are from pre Excel 97 days. You can either create your own userform your just use Application.InputBox to get info from the user which just has a single textbox and a msg. See my example below. If you want to get fancy as mask what is entered with asteriskes you will need to use API calls which gets a little more complicated but can be done. I suggest you dont use On Error Resume Next unless it is for a specific purpose. Because of this line you cannot see that there may actually be an error with the Application.WorksheetFunction.VLookup... line. OERN has its place like using before renaming a sheet in case the name is invalid but then you should always check for errors with If Err.Number <> 0 Then (0 means no error) and then tell user theres an error or whatever. To turn off OERN use On Error Goto 0. Example Code Public NumTries As Integer Sub Button2_Click() Dim varResult As Variant varResult = Application.InputBox("Hi " & Environ("Username") & ", Please enter your password.") If varResult = False Then 'they pressed cancel MsgBox "You have not entered a password. You may not access this workbook" Else If varResult = WorksheetFunction.VLookup(Environ("Username"), Range("A1:B20"), 2) Then MsgBox "Open Sesame!" 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" Else MsgBox "Sorry thats the incorrect password.", vbCritical, "Incorrect Password" NumTries = NumTries + 1 'increment number of tries End If End If End If 'rest of your code End Sub	Parry (5696)
448138	2006-04-21 05:55:00	Hi everyone. Thanks for your inputs, I have managed to figure out some of the things you have suggested: - *'s in password box check. - : after else (now I understand why its there) check - error handling statement taking control after problem in vlookup function check. Antmannz: Also, you can retrieve the values from the UsernameBox and PasswordBox with "UsernameBox.Text" and "PasswordBox.Text" This was what I was after, how to refer to whats in the boxes (they are both on the same form and haven't been assigned to variables as yet). Parry: I'm not exactly sure what DialogSheets are. I just realised that you can create forms in Excel as an option to insert a new sheet. Am I not using it in context? Or should I be creating a form within the VB code?	pico (4752)
448139	2006-04-21 07:58:00	Ok so here is what I have now. I tried to make it easier to follow. I originally wanted to make the object variables as strings but the debugger expected objects. So now the vlookup function causes an error. Whats wrong? Option Compare Text Sub LoginButton_Click() Dim Username As Object, TryPassword As Object, ActualPassword As Object, LoginTable As Range Set Username = DialogSheets("Login").DropDowns("UsernameBox").Text Set TryPassword = DialogSheets("Login").EditBoxes("PasswordBox").Text Set LoginTable = Range(LoginTable) Set ActualPassword = Application.WorksheetFunction.VLookup(Username, LoginTable, 2, False) If TryPassword = ActualPassword Then MsgBox "this works" Else MsgBox "doesn't work" End If End Sub	pico (4752)
448140	2006-04-21 08:02:00	The are pre-Excel 97 so were last used in Excel 95. Ive never used them myself so cant tell you anything about them. They have been replaced by Userforms - in the VBE select Insert | Userform. You draw your controls on the form then add code to the various controls you added as required. The code for the userform is in the userform itself but you need a procedure outside the userform to start it with Userform1.Show where Userform1 is the name of the form. If its something simple you could just use an InputBox as per my example. Search for Excel Userform in Google and you should find some good tutorials. regards, Graham	Parry (5696)
1 2