Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

An Excel VBA problem for Kees,

Jun 25, 2005 10:25PM PDT

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

Discussion is locked

- Collapse -
An Excel VBA answer for Mark.
Jun 26, 2005 7:14PM PDT

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

- Collapse -
Great, and thanks
Jun 28, 2005 12:59AM PDT

Your suggestions are great Kees. Many thanks for the work.

I'm trying them both out.

I'm still working out in my head what the second code will do, but I bet the best way is just to insert it and stand back!

Then modify it if need be.

Code 1 looks so simple so I think I will try that first.

I will let you know how I get on.

Thank you again Kees.

Mark

- Collapse -
Progress report
Jun 30, 2005 5:37AM PDT

I haven't finished this yet.

The workbook my colleagues sent me from work was just the monthly collation workbook and they didn't send the other workbooks that are linked to it, so I've been trying these codings out on one of my own.

But I'm not doing that well. The problem is me. I don't know enough about VBA to pick up on any adjustments I need to do, so I'm teaching myself as I go along.

I'll get there.

Mark

- Collapse -
If you can't work it out ...
Jun 30, 2005 5:46AM PDT

drop me a message via the forum mail, and I'll tell you where to send the spreadsheet so I can have a look at it, if necessary.

But I'll shortly leave for a 3 week holiday (European standard, indeed), so you've got ample time to try it yourself. But if you are in despair after that 3 weeks, I'll be glad to help.

Kees

- Collapse -
Wow, 3 weeks???
Jun 30, 2005 5:52AM PDT

Yep that will give me time to work things out.

I will let you know if I need your help when you get back Kees, and thanks again.

Enjoy your holiday.

Mark

- Collapse -
Thanks.
Jun 30, 2005 6:06AM PDT

Mark,

Most people (with a little bit of money, I'm afraid) leave Holland for three weeks or so. France is very popular, but we are heading for Croatia now. Never been there, but about everybody I tell it say they or some family member goes there for 4 years already. Sun and sea, that's a good holiday to me, although I don't object to a nice town or an interesting museum either. But my son does!

Here in Holland it's not unusual to have 25-days of holiday and 10 days of 'ATV' (Work Time Reduction, there must be a better English equivalent, but I don't know the right term). Twenty years ago we all got to work a few hours a week less to help the young find a job also (like they work 35 hours a week in France, not 40).

If parents are separated children may go on holiday with both parents (and the new partners, if present) for two weeks, for example.


But you are 'semi-retired' and can have all the holidays you want, can't you? I have to wait at least 10 years for that (and don't even know if I'll like it).

Regards,

Kees

- Collapse -
Yes I understand
Jun 30, 2005 9:42AM PDT

In the UK many people have 25 days a year, or some of the luckier ones have 30 days.

I'm not sure what ATV would be. I don't think we do have an equivalent, although we have flexible working hours, mostly in office jobs, where as long as we work a prescribed number of hours a month, we can "adjust" our working day to suit our needs. And we have part-time working where perhaps two people will do the same job but on different days. But perhaps neither is similar to ATV.

Most people here work in the range 37- 40 hours a week.

But I'm fully retired now. On holiday "all" the time, Happy

So far I am enjoying myself immensely.

I have heard Croatia is a lovely place. I'm sure you'll have a great time.

Mark

- Collapse -
Mark, did you succeed?
Jul 24, 2005 6:57AM PDT

If not, I might be able to help as I offered before.

Kees

- Collapse -
Hiya Kees
Jul 24, 2005 10:49AM PDT

and welcome back from your holiday. We had 3 days of sun here in the UK and now it's raining. A lot.

I've given up. I wasn't getting the linked spreadsheets I asked for to try out an admin password on the master spreadsheet, and although I could have constructed something it looks like the need for it has passed.

I did have a go with one of my own spreadsheets, but I needed the works ones for the full picture, so I decided to pass.

Many thanks for your offer though. I know where to come in the future, Happy

Mark