Mark,
Two different ways you can try.
1. Excel VBA (like all Basic variants) does have an On Error option. The code below compiles without errors.
Sub test()
On Error GoTo x
x: Exit Sub
End Sub
2. You could simply test the result to see if the user entered the correct passwork. Something like this (on error resume next might suppress the error message):
On Error Resume Next
b_stop = False
Sheets("Admin").Visible = True
While Not Sheets("Admin").Visible and Not b_stop
If MsgBox("Wrong password. Retry?", vbYesNo) = vbYes Then
Sheets("Admin").Visible = True
Else
b_stop = True
End If
Wend
Just this weekend I finished my first html-form with Javascript validation included. An programmer experienced in Javascript would do it in his sleep, so to say, but it took me quite a few hours. I kept trying the wrong things, not to speak of the syntax pecularities. So I can imagine how you feel right now.
Hope this helps,
Kees
or anyone else with ''the knowledge''.
I have a spreadsheet for work that displays graphs of; work received each month, work done, work outstanding, and trends over a the past 18 months.
It's relatively complicated, (for me), because it grabs data for 4 different people from other workbooks, (daily records), stored on a shared directory on the main office server.
I'm retired now, but my ex colleagues still wants to use the spreadsheets and they have asked me to maintain it for a while. I will do, but not for long as inevitably my involvement will decrease over time.
So I am preparing an ''Admin'' worksheet on this spreadsheet so that minor changes can be made to keep it uptodate and running smoothly.
The Admin worksheet is no particular problem, but what I want to do is password protect access to it, (so it doesn't display otherwise), through a simple Form button with an assigned macro. I know I could protect the workbook and individual sheets, etc, but I want to make this a ''push button'' affair, to keep it as simple as possible. None of the worksheets have scroll bars, column and row headers, formular bar, worksheet tabs or status bars, and access to each worksheet is through form buttons with simple macros assigned.
So, after all that, here is my problem. From an ''Overview'' page, there will be a form button for ''Admin''. Clicking that will bring up a password request box, and entering the correct password will take the user to the Admin page which, at the same time, will re-select all the tabs, scroll bars, etc.
Those options re-selection I can do, but I can't do the VBA coding for the password request, checking and unhiding. Any ideas?
I can create simple macros for Hiding/Unhiding the sheet, and for Protecting/Unprotecting it, but I can't combine the two. If I protect the whole workbook, then when the Admin sheet is hidden, there is no Unprotect option in the Tools menu for this sheet. With an unprotected workbook, this is what I have so far;
Sub UnhideAdmin()
'
' UnhideAdmin Macro
' Macro recorded 26/06/2005 by Mark Flaxman
'
'
Sheets(''Admin'').Visible = True
Sheets(''Admin'').Select
Application.Run ''Portfolio.xls!Admin''
ActiveSheet.Unprotect
End Sub
But with this, if the wrong password is input, I just get a debug error and the sheet is still displayed. The error is;
Run-time error '1004':
The password you supplied is not correct. Verify that the CAPS LOCK key is off and be sure to use the correct capitalization.
I would prefer an OnError type action that the sheet is not unhidden and the user stays on the sheet where the ''Goto Admin'' form button is.
And the person who says, ''now I'm retired I can research VBA and do it myself'', I will come over and stamp on his big toe!
Mark

Chowhound
Comic Vine
GameFAQs
GameSpot
Giant Bomb
TechRepublic