Baffled

  • Hello,


    I am slowly teaching myself how to program in MS Excel 2002. Perhaps the project I chose is fairly advanced for my present programming knowledge, but what the heck... I enjoy a good challenge.


    Anyways, here is my code so far, with an explanation of what I am wanting to accomplish with this project to follow.


    Private Sub Workbook_Open()
    Dim Username As String, Password As String

    ' Hide all worksheets in the active workbook
    SheetCount = ActiveWorkbook.Sheets.Count
    For H = 1 To SheetCount + 1
    If ActiveWorkbook.Sheets(H).Visible = True Then
    ActiveWorkbook.Sheets(H).Visible = False
    End If
    Next H

    ' Requests username and password
    Do Until Username = "Master"
    Username = InputBox("Please enter your username:", "Username")
    If Username <> "Master" Then
    Msg = "That username does not exist in our database"
    MsgBox Msg, vbCritical
    Else
    Do Until Password = "master"
    Password = InputBox("Please enter your password:", "Password")
    If Password <> "master" Then
    Msg = "That password does not match the specified username"
    MsgBox Msg, vbCritical
    Else
    Msg = "Welcome Raymond"
    MsgBox Msg, vbInformation
    End If
    Loop
    End If
    Loop
    End Sub



    As you can see, this is a procedure that executes when the workbook is opened. The first part is supposed to hide all the worksheets in the workbook. Unfortunately, I must have this section coded incorrectly as it keeps returning an error message. What I am wanting to do with this section is have it hide all the worksheets and then, upon successful entry of the username and password, will unhide those worksheets that I want that particular user to have access to. Of course, I would want it set that my login would make all the worksheets visible.


    The next section work perfectly with the following exception... unless you enter the correct username/password combo, you are stuck in an endless loop. I haven't been able to figure out how to have it so that if after the third unsuccessful attempt, it locks out that username for 24 hours and shuts down Excel. Also, I would prefer to have the username/password in a userform rather than using the inputboxes.


    That is all for now. There is much more that I want to do with this, but I want to try and figure out as much on my own as I can.


    Thanks for any help you can offer.

  • I think you might be getting the error message because you cannot hide all the sheets in Excel, one must remain visible at all times.
    Have alook at Dave's PasswordBook.zip here
    http://www.ozgrid.com/download/default.htm

  • Hi,


    Create a dummy-sheet and hide it and show the real sheets when correct password is entered.


    For exit the loop You may consider to use a counter to determine how many times a user can try to enter the correct password.


    You may also consider to add an Msgbox with yes/no-buttons to continue to try.


    Mail back if furhter help is wanted.

  • Hi XL-Dennis,


    Thanks for the quick reply. I never even thought to put in a counter variable. I also like your idea for having a userform asking the user if they wish to continue.


    I know that there is a way to e-mail from Excel, so, could the userform be embellished to allow for a user to e-mail the workbook administrator in the event that they have forgotten their password? Of course, this would mean that there would be multiple users that have access to the same workbook and thereby, there would need to be a database file that contains the usernames and passwords and for excel to be able to query that file for vaildation of such. Also, there would need to be set in place a way for users to be able to change their own passwords and actually, perhaps this could be something that would be mandatory every 30 days. Just some more ideas I am playing with, so any coding suggestions or help would be appreciated.

  • Hi DrGuru,


    Please search the archieve for e-mailing from XL.
    You find all relevant threads in the Excel / Email-forum.


    If all users are connected to one common server You may consider to set up a small XL-file on the server that only deals with passwords and that only the admi have full access to.


    Then the desktop-application can query at login, i e check the password, and let the users update their passwords. You can also create a check-routine that force / remind the users to change their passwords every 30 days.


    One approach is to use ADO and SQL for it.


    Since the security in XL is very bad You may also consider to build a small app in VB that serves only the purpose to checkthe login and open the XL-file.

  • Quote

    Originally posted by DrGuru
    Is there a way, however, to trick Excel into thinking that there is at least one sheet visible?


    DrGuru,


    Yes, but it is not to trick Excel it is to use what Excel already allows. As you may be aware the normal extension for an Excel file is *.xls which is the standard. You can however also have a number of others but the type we need to aquaint you with is *.xla which is an addin file.


    An addin is essentially an *.xls workbook that has been created to carry out a specific function or functions, and is loaded into excel but is not visible, it normally is saved as with the *.xla extension but there is a little trick that allows you to have an *.xls addin.


    What we want is an *.xls file that has it's properties set to be an Addin rather than a woorbook, this way when it opens it is "invisible" until such time as the properties are changed. This achieves what you need, the next step is how to tell it to change the properties through the VBE (Visual Basic Editor), try playing with the following line of code and see if it fits with what you need to achieve.


    Application.ActiveWorkbook.IsAddin = True


    You can also set it to False


    Hope this helps.


    AJW


    C:\temp\Addin Property.jpg

  • Hi AJW,


    That is an interesting solution and I have been playing with it a bit to see if it will suit my needs. The reason I need to have the actual sheets hidden is that each user of this workbook will have a set access level, thereby only being granted access to very specific worksheets. Thereby, the remaining sheets will remain hidden to them. There will also be several work categories, so once a user has successfully logged in, they will be presented with a userform-based menu so they choose which module to work in. Then, only those worksheets that pertain to that work module will be made visible. I might even want to have it so that all the worksheets remain hidden and all the entry is done via userforms.


    I am trying to make this as user-friendly as possible as the group of people that I work with are not the most computer literate, so I am trying to make it easy for them to use. Any suggestions?

  • Ok, this has really got me stymied. I have tried everything I can think of, and still can't get this next stage of my appy to work.


    The problem I am having is with regard to users logging on. I started with Dave Hawley's PasswordBook file as I wanted to use a userform for user logging on instead of a series of InputBoxes.


    As you can well imagine, I have done some modifications to customize the log-in pertaining to my needs. I added two fields that regular users won't see... their full name and user level. I have these fields set up as textboxes on the userform. What I need is to have their full name and user level displayed in their respective textboxes once the user switches the focus to the password textbox.


    This appy creates a series of reports for my department, and I want them to be created with the user's fullname instead of their login ID. Also, the user level is necessary to determine the level of access granted to each user.


    Any help you can offer will be greatly appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!