PDA

View Full Version : Solved: Different Passwords for all Users to view W/sheets 1 master password



jbri
05-25-2009, 08:23 AM
Summary of what I am wanting to achieve:
Workbook opens on a Sign-In worksheet - user enters their unique password and gets taken to their worksheet - if they navigate away from their worksheet they have to go back to the Sign-In worksheet - when they close the workbook it re-sets to outcome one (Workbook opens on a Sign-In worksheet) - and finally a super password that can enter every worksheet
The practical use: I am trying to set a regular fortnightly test for my team (15 people) that is simple to use and writes itself. My team members supply one question each and the answer to that question making up a 15 question test.

On the 15 worksheets (one for each team member) is 15 rows with 3 columns - the rows have the names of each team member in column 1.

In column 2 they can write their question and in column 3 write the answer to that question.

They can see the questions already set by the team members who have had earlier shifts than them - but obviously not the answers. I have done this by using (for example) =John!D3 to import the question from John's worksheet to the other team members worksheets.
I want access to all their worksheets (the second password of each worksheet) so that I can make sure the questions have relevance to our work, the answers they have given are correct and I can, when everybody has set their questions and answered the questions of everybody else, go through and mark them.

I'll have a results worksheet that shows each team member whether he got the other questions right or wrong and that shows all the questions again with their answers so that, if anybody disagrees with the answer, thinks their answer was better etc., it can become a discussion point to the good of us all (the whole object of this testing system) - our work can be very subjective - hence the possible disagreement about the answers.
What code I have found: I have spent weeks trolling the net - few codes addressed my need that nobody can even view the worksheets without the correct password and reset the system on closing the workbook.... but, here is one that did (however it doesn't close (re-hide) the worksheet if you navigate away from the worksheet with the workbook still open):

Option Explicit
Option Compare Text
'Password to unhide sheets
Const pWord = "help"

Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("John").Visible = xlSheetVeryHidden
End Sub

Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")

Case Is = pWord
With Worksheets("Jon")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"
End Select
End Sub

Option Explicit

Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

'Hide confidential sheet at startup
Call HideSheets

'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Sign-In Page")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub







I even found one code that had the option of 2 different passwords To view it: (But it was for only one worksheet - not the 15 I need)

Dim LastActiveSheet As Worksheet


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Not Sh Is Sheets("Sheet4") Then
Application.ScreenUpdating = False
Set LastActiveSheet = Sh
End If
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh Is Sheets("Sheet4") Then
Sh.Visible = False
With Application
.EnableEvents = False
LastActiveSheet.Activate
.EnableEvents = True
End With
PromptForPassword
End If
End Sub


Sub PromptForPassword()
Dim UserInput As Variant
Const PWord1 As String = "Enter Password1 Here"
Const PWord2 As String = "Enter Password2 Here"
Const Msg1 As String = "Sheet Locked For Viewing !" & vbNewLine _
& vbNewLine & "Enter Password To Unlock."
Const Msg2 As String = "Wrong Password !"

With Application
Do
UserInput = .InputBox(Msg1)
Select Case UserInput
Case Is = False ' if user cancells don't activate sheet
Exit Do
Case Is = PWord1, PWord2 '**if password correct activate sheet4
Set LastActiveSheet = Sheets("Sheet4")
Exit Do
Case Else 'if wrong password give user another try
UserDecision = MsgBox(Msg2, vbRetryCancel): Beep
End Select
Loop Until UserDecision = vbCancel

Sheets("Sheet4").Visible = True
.EnableEvents = False
LastActiveSheet.Activate
.EnableEvents = True
End With
End Sub



CAN YOU HELP?

So, I've found some of the elements of what I want to do - I just don't know how to combine them and what I need to add to achieve my final goal - which (to re-iterate) is:
1. The workbook opens on a sign-in worksheet.
2. From a menu of names on that sign-in worksheet a unique password takes them To their worksheet.
3. If they move To another worksheet they have To go back To the sign-in menu To get back To their worksheet.
4. Each worksheet has 2 password options (theirs And mine which I use To access every worksheet) - alternatively, it might be easier To have a 'super' password icon for me on the sign-in worksheet that turns off all other passwords while I'm working the workbook
5. When the workbook Is closed it re-sets To 1.



Thanks for reading this and hopefully see an easy way to cobble the code I've already found to a bit more for me.


Regards,
Jon.

mdmackillop
05-25-2009, 08:29 AM
Will you know each of the users passwords?

jbri
05-25-2009, 05:46 PM
Yes... if for no other reason than I will have to go through the code to put each persons password in.

The pass wording need is so that everybody can contribute their 1 question - with its answer - to these tests without allowing them to see the answers left by the other team members (or other team members seeing the answer to their question) until all have completed the test and the test with answers gets published by me.

I'm sure others will be able to adapt the code to do what I want for other useful purposes - e.g. A employee diary that has public (for all to read) items and private (only they can read pages) that the boss (me) can leave messages or instructions that are not for 'everybody's eyes'.

On re-reading my request I see that the 'super password icon' on the log-in worksheet (that I hoped for) that turns off the passwords so I can jump between worksheets (checking questions, marking answers etc) would have to be pass-worded to stop my staff from turning the passwords off (and thereby allowing them to cheat in the test).

To stop cheating the code would need to be hidden as some of my people are familiar enough with excel to find and view unprotected code.

jbri
05-25-2009, 05:49 PM
[Only saw I could edit my posts after I had posted this (now erased) post... sorry]

Aussiebear
05-25-2009, 06:37 PM
You can password protect the project to stop other from "accidentally" editing the workbook code. Just one other question whilst MD is gaining some quality sleep time, is can an employee change or edit a password or do they have to request a new one from you?

jbri
05-26-2009, 12:38 AM
Aussiebear (great to see another Banana Bender on this site),

Yes, giving the option to change their passwords would be great - but, I worry that my 'wish list' is already far too long.

While I've been exploring this site (which I have discovered has little elements of most of the things I want) I have seen lots of 'extras' that would be nice - one example is a splash display when Users first open the workbook - but, most are things I'd only do to 'pimp' up the workbook (as opposed to adding real value) if I only knew how to properly install them.

I tried following the explanation of what goes where (this workbook, sheets, this project etc.) but a lot of what I see must assume the reader has a better understanding of VBA than I do (either that or I'm particularly thick).

A few codes I've used in the past, I've been told, require them to be nested by keying alt this or that (or is it ctrl this or that) with no explanation as to why you just can't copy and paste them.

Anyway, thanks for your interest... hopefully MD is not too busy to write me something that meets my basic requirements (and it doesn't take too much out of his, no doubt, busy schedule)

Krishna Kumar
05-26-2009, 04:49 AM
Hi,

You could download a sample workbook from here (http://www.ozgrid.com/forum/showthread.php?t=73749)

jbri
05-26-2009, 07:09 AM
Hi Krishna... I downloaded the xls and [as good as it is] it doesn't have the fundamental features I need - password protection for the viewing of each sheet with a different password for all sheets and a master password I can access them all. Thanks anyway for the thought.

jbri
05-26-2009, 07:22 AM
If it helps MD (or anyone else looking to help me) I just found this code that might by quicker to add to, to give me the other features on my wish list:

Sub ProtectAllSheetsSamePW()
Dim wSheet As Worksheet

For Each wSheet In Worksheets
wSheet.Protect Password:="secret", UserInterfaceOnly:=True
Next wSheet

End Sub

Sub ProtectAllSheetsNOTSamePW()
Dim wSheet As Worksheet, strPassword As String

For Each wSheet In Worksheets
Select Case UCase(wSheet.CodeName)
Case "SHEET1": strPassword = "carrot"
Case "SHEET2": strPassword = "secret"
'Follow pattern
Case Else: strPassword = "generic"
End Select
wSheet.Protect Password:=strPassword, UserInterfaceOnly:=True
Next wSheet

End Sub

I just loaded a workbook with this and it only passwords the sheets - it doesn't stop them being viewed

jbri
05-28-2009, 09:48 PM
Hi All - I guess everyone's given up? Nevermind, this VBA isn't exactly what I want but, I can use it to get my staff tests underway while I play with it to give me everything I was hoping for [CREDIT FOR THIS VBA GOES TO A VBA REGULAR CALLED Mikerickson WHO ANSWERED (on 1 Mar 09) A PLEA FOR HELP ON 28 Feb BY jiddings ENTITLED 'protecting multi shts with separate pwrds']

Private Sub Workbook_Open()
Dim uiPassword As String
Dim oneSheet As Worksheet
Dim SheetsToSee As String

With ThisWorkbook
.Sheets("EveryBodyCanSeeMe").Visible = xlSheetVisible
For Each oneSheet In .Sheets
If oneSheet.Name <> "EveryBodyCanSeeMe" Then
oneSheet.Visible = xlSheetVeryHidden
End If
Next oneSheet
End With

uiPassword = Application.InputBox("Enter your password", Type:=2)

Select Case LCase(uiPassword)
Case Is = "False"
Exit Sub: Rem cancel pressed
Case Is = "password1"
SheetsToSee = "Sheet1 Sheet2"
Case Is = "password2"
SheetsToSee = "Sheet1 Sheet3"
Case Is = "password3"
SheetsToSee = "Sheet2"
Case Else
MsgBox "bad password"
SheetsToSee = vbNullString
End Select

For Each oneSheet In ThisWorkbook.Sheets
If 0 < InStr(SheetsToSee, oneSheet.Name) Then
oneSheet.Visible = xlSheetVisible
End If
Next oneSheet

Rem optional hides EveryBodyCanSeeMe If password Is good
ThisWorkbook.Sheets("EveryBodyCanSeeMe").Visible = IIf(SheetsToSee = vbNullString, xlSheetVisible, xlSheetHidden)

End Sub


Oh - and I'll protect the passwords from being seen if the code is viewed by those who know how to view it by: To protect your VB code from being seen by others, all you need to do is go to the project explorer, point at your project and right click on it. Select VBA project properties, click on the protection tab and check the Lock project for viewing and key your password. That's it. [Thanks to 'Julian's Excel Macro (VBA) Tips to Beginners' - found on Gooogle - for this one]

Gee - seems I'm learning useful stuff everyday

I am still keen for some kind soul to show me how to add to the above code to do the other things I wanted.

Aussiebear
05-29-2009, 12:55 AM
Hi All - I guess everyone's given up? Nevermind, this VBA isn't exactly what I want but, I can use it to get my staff tests underway while I play with it to give me everything

Hmmm.... given up???? That doesn't sound like a Queenslander attitude. jbri, whilst this project is highly important to you, it may not carry the same level with others who frequent this forum. I'm sure you've read posts in forums and passed over them for one reason or another. If you can persevere a little bit, someone will be along to assist you.

Why not throw up a workbook with what you have so far, and we'll have a quick look?

Aussiebear
05-29-2009, 01:32 AM
Have a look at this for a concept;

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/969f4aa363bac935/c13bc6ed71a24179?lnk=st&q=#c13bc6ed71a24179

Aussiebear
05-29-2009, 02:16 AM
Ooops.. my system is acting strangely.

jbri
05-29-2009, 05:07 AM
Aussiebear (or can I call you Koala?) - No I hadn't given up, I just thought everybody else might have) - in fact, that I'm still copying bits of code and finding ways of mating them shows I'm 1. not just sitting back waiting for it to 'drop into my lap' and 2. actually trying to improve my knowledge so I can help others with similar needs.

Tonight I'm re-writing that last (working) code I found with one that gives multiple password tries before it closes down (which the one I found does).

I'm anticipating I'll have it cracked tonight or tomorrow and it was then my intention of posting the working version here so others can make use of it.

Actually I don't think I've ever passed by a post I could be assistance with (but, you'll have to take my word on that), however, with my knowledge of VBA there arn't that many I can help with (heh heh).

I don't have a workbook worth uploading - the best I've had I had to erase today because the code I loaded into it wouldn't accept the password and kept closing. Having spent quite a bit of time pimping up the one I had to erase, I've learned my lesson so, what I'm doing now is opening new workbooks, trying to get them to work and when they do (like this last one posted does) I'll pimp up that workbook and post it here.

Not that I wouldn't like a bit of help (with cleaner, smarter ways of doing it), by not getting immediate help it has forced me to self-teach, experiment and get a lot 'VBA smarter' than I would have if my first post had received a complete working model to the specks requested - gee, I might even become an 'expert' on here helping others (heh heh).

For your (apparent) interest here is how I've modified the above code (which works in the test I did this afternoon at work):

Private Sub Workbook_Open()
Dim uiPassword As String
Dim oneSheet As Worksheet
Dim SheetsToSee As String

With ThisWorkbook
.Sheets("Sheet4").Visible = xlSheetVisible
For Each oneSheet In .Sheets
If oneSheet.Name <> "Sheet4" Then
oneSheet.Visible = xlSheetVeryHidden
End If
Next oneSheet
End With

uiPassword = Application.InputBox("Enter your password to view your worksheet - Once there enter your question for the test, enter your answer to that question, answer any of the other available questions and, finally, do not forget to save before exiting", Type:=2)

Select Case LCase(uiPassword)
Case Is = "False"
Exit Sub: Rem cancel pressed
Case Is = "a"
SheetsToSee = "Sheet2"
Case Is = "b"
SheetsToSee = "Sheet3"
Case Is = "c"
SheetsToSee = "Sheet5"
Case Else
MsgBox "bad password"
SheetsToSee = vbNullString
End Select

For Each oneSheet In ThisWorkbook.Sheets
If 0 < InStr(SheetsToSee, oneSheet.Name) Then
oneSheet.Visible = xlSheetVisible
End If
Next oneSheet

Rem optional hides EveryBodyCanSeeMe If password Is good
ThisWorkbook.Sheets("Sheet4").Visible = IIf(SheetsToSee = vbNullString, xlSheetVisible, xlSheetHidden)


End Sub



Tonight I'm going to try and integrate this following code to give the above code 3 password attempts before closing:


Dim sLast As Object

Private Sub Workbook_Open()

'Ensure Sheet1 is not the active sheet upon opening.

If Sheet1.Name = ActiveSheet.Name Then Sheet2.Select

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim strPass As String

Dim lCount As Long

If Sh.CodeName <> "Sheet1" Then

'Set sLast variable to the last active sheet _

'This is then used to return the user to the _

'last sheet they were on if password is not known _

'or they Cancel.

Set sLast = Sh

Else

'Hide Columns

Sheet1.Columns.Hidden = True

'Allow 3 attempts at password

For lCount = 1 To 3

strPass = InputBox(Prompt:="Password Please", Title:="PASSWORD REQUIRED")

If strPass = vbNullString Then 'Cancelled

sLast.Select

Exit Sub

ElseIf strPass <> "Secret" Then 'InCorrect password

MsgBox "Password incorrect", vbCritical, "Ozgrid.com"

Else 'Correct Password

Exit For

End If

Next lCount


If lCount = 4 Then 'They use up their 3 attempts

sLast.Select

Exit Sub

Else 'Allow viewing

Sheet1.Columns.Hidden = False

End If

End If

End Sub

I hope this display of perseverance on my part lives up to your expectations of what spirit a Queenslander should exhibit (I'm coming back to Brisbane on the 11th of June for annual leave if you'd like to share a beer to have a laugh about our exchanges on this site).

mdmackillop
05-29-2009, 12:00 PM
If you put together what you have in a sample workbook I'll have a look.

Aussiebear
05-29-2009, 02:19 PM
I note that the second section of code came from OZGRID website. Did you follow the link that I provided to see if that would be of any use to you?

Aussiebear
05-29-2009, 09:52 PM
What about this from the KB?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=33

Aussiebear
05-29-2009, 10:09 PM
The attached workbook has the following functions;

1. User & password protection for 15 users,
2. Log on msgbox on opening file,
3. Each User has access to their own sheet + Main Sheet,
4. On closing their worksheet, the workbook will close down.

Credit for the code concept goes to Allen Wyatt, all I've done is amend it slightly to your requirements. Others may wish to amend further, and post back to the forum.

mdmackillop
05-31-2009, 11:06 AM
Hi Ted,
Here is a modified version. For my code I've copied the Names/Passwords to sheet Main, and also show the Questions/Answers. These can be hidden and made visible by entering the Master password.

iMAN2
06-01-2009, 12:06 AM
Hmm,
with this however, will users be able to modify the others cells in there worksheet, or just the one's for their question and answer?

im saying this because by changing the question column from b to c, it will show the answer.

mdmackillop
06-01-2009, 12:09 AM
I would anticipate that Admin would remove all formulae prior to use.

iMAN2
06-01-2009, 12:24 AM
mmm fair enuf

jbri
06-01-2009, 08:58 AM
Hi Gentlemen All (Md, A/Bear & iM2),

It is interesting how many glitches a seemingly simple set of features can create.

I have one version ready to go that doesn't have all the features I wanted but, I needed to get this testing started before I go on Annual Leave next week and it works good enough as an interim measure.

I'll look at MD's modified version of A/Bears especially with respect to iM2's comment (MD - don't I need the 'get cell' data formula there to import the questions from the other users to each users worksheet?).

Meanwhile, thanks to you all for your interest to date. I'm sure many managers will find the final version a very efficient way to regularly test their staff's knowledge of their jobs (as a way of deciding what future training is needed) or (with simple modification - label changes mainly) a way of confidentially communicating with (shift) workers who they might not get to see as often as they would like.

Regards,
Jon.

mdmackillop
06-01-2009, 09:08 AM
Hi Jon,
I would definitely consider removing all passwords from the code and storing them in one location. It will make maintenance much simpler. You should be aware that all Excel security is very poor. If you are only dealing with 15 Q & A, get them emailed to you and copy them into a Users workbook. It would be very simple to extract the answers to a Master book if required.

jbri
06-01-2009, 10:08 AM
Hi MD, I was going to attach one of the versions I'm trying to get working.

However, although it says I can post attachments it doesn't appear to give me that option on this window's toolbar.

Jon.

mdmackillop
06-01-2009, 10:46 AM
Hi Jon,
Use Manage Attachments in the Go Advanced reply section.

jbri
06-01-2009, 11:37 AM
MD.... here is one version I am trying to get working. If you do find the bug in this can you save it in Excel 2003 (I have a new computer in my apartment with the latest Excel (2007?) and my office only has 2003 - 2003 to 2007 is OK but not vice versa even, sometimes, if I save it from the 2007 program to a 2003 compatible file).

I'm trying to change all the sheet names to actual names and keep getting bugs - I go to VBE - select F7 then as an example do this:
Case "User1"
sSName = "Sheet1"
If txtPass <> "User1Password" Then bError = True
Case "User2"
sSName = "Jon2"
If txtPass <> "123" Then bError = True
Case "User3"
sSName = "Sheet3"
If txtPass <> "User3Password" Then bError = True

Then I go to:

Case "Sheet1"
w.Protect ("User1Password")
w.Visible = False
bSaveIt = True
Case "Sheet2"
w.Protect ("123")
w.Visible = False
bSaveIt = True

Save it and then it doesn't work... which is a shame as it has a real good feature in that I can open all pages to copy formats etc.

While I'm at it... on another version that I can't open all the sheets, I'm trying to copy the formats page to page but can't as I have to keep closing the workbook to go to the next sheet and when I come to paste into the next sheet I get two options nether of which paste the format into the sheet I want to copy it into... if I try to copy sheet to sheet, of course I can't because the other sheets are hidden to the sheet I am copying (aaaarrrrggghhhhhhhhh).

Regards,
Jon.

Aussiebear
06-02-2009, 02:28 AM
The purpose of the admin privilege of overseeing all sheets was to enable someone like yourself to edit/change add/delete etc. If you enter the workbook as one of the single sheet users then of course you will be unable to see all the sheets.

As to the issue of copying formats from one sheet to another, set up your workbook as you want to be then apply the code. You need to be logical in your approach to this issue.

jbri
06-02-2009, 03:50 PM
Aussiebear,

To your first comment, yes I know (that's why I like MD's modified version of KB's code) - I may have lead you into believing that I'd missed the value/purpose of the Admin user by using my name as user two in the last bits of code I posted.

As to your second comment (ouch!), again I know that but, I rarely set something up and leave it be. I usually go back several times to either improve the aesthetics or functionality, in which case being able to copy sheet to sheet is important (as an aside I wonder why you can paste special column widths but not row heights?).

Regards,
Jon.

jbri
06-03-2009, 01:17 AM
MDM,
I played with your model all day - I'd get one part working but, then find I stopped something else working. Because of the 'all sheets open' option (once its all works) I can quickly format it later - and modify the format at will at later times after that... its just getting it working.

I've attached my last attempt and I'm sure you'll quickly see where I've gone wrong.

The table on the main page I've really messed up. It had script in the cells but formulas behind then... having wrecked it I could not get it back to where you had it (without starting it again and losing all the user names and passwords I'd already put in - given I'd save at regular times so couldn't just quit without saving.

Regards,
Jon.

mdmackillop
06-03-2009, 01:19 PM
My feeling is it was all getting overcomplicated. I've deleted some sheets for which the passwords were "lost", and added a button on Main for creation of new. Data rows on Main are hidden by the code and made visible when the Admin password is entered.

jbri
06-05-2009, 11:59 PM
MD
Thank you for your latest effort. Using a table on the 'Main' w/sheet makes it heaps easier for novices like me to administer new users or changes of passwords without the need to go into the code to do it (like it originally was) and thereby running the risk of inadvertently changing the code that caused a bug ... it also made it easier for me to see how your code did what it does. I tried jumping in and changing everything I needed to in one fell swoop but, it kept creating bugs... it seems it is better to make one change, see if it works, save it if it does and then attack the next change.

[There are 2 w/sheets - KC & Joey - that don't appear when using the admin Master so, at the moment, I'm looking at how to have them reveal in the w/book so that I can delete them and create them again as official users using your 'Add sheet...' code]

The biggest problem (for me) is to track one change through the entire VBA to make sure everything it is interconnected to is appropriately changed to avoid creating a bug.

I'm about to go on 3 weeks leave and I will try, during this time, to get it fitted out with all my users and the w/sheets formatted the way I would like them to appear. Then I'll post the finished w/book here for you to look at and hopefully declare the original post as solved.

Regards,
Jon.

mdmackillop
06-06-2009, 03:09 AM
I've improved the AddSheet code and added a Delete option. Redundant sheets have been deleted.

mdmackillop
06-06-2009, 04:46 AM
Hi Jon,
Some general suggestions.
1. Before you make any changes. Make a copy of the workbook.
2. Before changing code; Make a copy of the Sub/Function. Rename it so as not to cause a conflict.
3. It is much easier to reinstate than undo changes.
4. Save frequently. Code changes can cause crashes/freezing til you get it working.
5. Can you code be run from more than one position? Be wary of using ActiveSheet/ActiveCell and unqualified range references.
6. Try to keep code in short discreet sections. Call other subs to achieve this. You'll find it easier to follow/debug.
Good luck.

jbri
06-10-2009, 11:22 PM
Hi MD,
Great advice! (Alas, too late this time :banghead: ). As I've found to my utter fraustration that sometimes just the simple act of moving buttons around, deleting/adding unneccessary rows in worksheets and the like can cause it go off the rails (and into 'bug' territory).

For example, I was just adding a bit of 'bling' to the latest model of this project - simple stuff, like creating new worksheets for staff not already in the system, making all the worksheets look the same, protecting cells on worksheets to stop staff writing into cells they shouldn't, etc... I saved regularly and kept re-running it to see if it was ok and it worked great on my office computer... I took it back to my apartment to finish it off and it wouldn't run on my home computer - I'm now in Australia and it won't run on this computer either.

But, I don't think it is different versions of excel effecting it (I have 2007 at home and 2003 at work - I got around this in my apartment by re-commissioning an old laptop I had at that uses 2003). I think its your 'be wary of references' warning - the last thing before going home, I deleted 3 or 4 rows at the tops of the worksheets - each worksheet is referenced to the summary you put on the 'main' worksheet (herein may lay the trouble).

I've uploaded what I did so that you can see how I messed up and also what it will eventually look like (I'm sure you can get to the 'main' w/sheet without knowing this but, I did change the user name to 'admin' and the pw to '1')

Regards,
Jon

mdmackillop
06-11-2009, 12:26 AM
Rescued.

jbri
06-30-2009, 11:42 PM
Hello MD,

Thank you for rescueing the file I had started to enhance. I have been away for 3 weeks and today downloaded that file into my excel (2003) programme to finish off the enhancing work.

However, it opens with the admin page open - to continue you have to close the login box, but doing that closes the add user and delete user boxes - opening the login box (and then entering user and user password) closes the admin page and from that point on it can not be opened again.

Meanwhile the add user, delete user and recall login boxes get pushed below row 18 (as 1 to 18 are hidden in this mode). Would you please have a look at this problem.

Regards,
Jon.

Aussiebear
07-01-2009, 12:36 AM
Go to one of the earlier posts which should contain a working copy. Then make a copy of the working copy and only play around with one of them. As you add additional functionallity to your design, save a copy..... and only screw around with one of them.

jbri
07-01-2009, 05:08 PM
Ordinarily good advice Aussiebear (which I had considered - its not that I'm entirely stupid, its just that I'm not familiar with VBA to the degree that I need to be to solve this problem, and which is why I've come to this site to get the 'expert' advice offered) however, the rescued file not only had additional functionality that I had done, but other improvements that mdmackillop had so kindly added - all of which, of course, earlier posts do not contain. :)

mdmackillop
07-04-2009, 03:22 PM
Finally got back to this. Give this version a test

jbri
07-05-2009, 11:52 PM
Hi md,

I have tested this latest version of the project and found that it does everything that I originally asked for :bow: and has not thrown up any unexpected results :clap:.

The only major thing I did to this file is to delete column I on the admin page - I did so with a bit of trepidation as I thought this might effect references in your VBA, but it still works so I guess adding and deleting columns and rows is allowable.

I assume just changing the VBA where it says hide to row 19 to, say, hide to row 25 will allow more rows should the number of Users go past row 19?

Today I re-wrote a few of the admin buttons and made pretty much all the other changes I intended making before pressing it into service. For example the User worksheets have the cells that will be written into formatted with the row option 'autofit' and 'wrap' so that the Users will not need to know how to make the cells bigger if their question or answer is bigger than the space provided.

I tried to protect all the cells on each user worksheet against being written into expect for their Question cell and all the Answer cells however, this conflicted with your VBA. A fix for this would be nice, but if you want to make the version I am attaching to this post the end of this thread I will understand... in this event, I will take this opportunity to thank you sincerely for your perseverance and willingness to share your obvious expertise in this regard.

The only remaining thing I need to do (before using this file) is to password protect access to the VBA editor.

To anyone reading this, the file that mdmackillop has produced should be easily adaptable to your own needs and particularly useful as a training tool (the use I will be putting to) or as a confidential diary to keep in touch with staff that you don't get to see face to face that often.

Again, thank you very much mdmackillop - I think you have come up with a very neat file that I expect you will add to your Kb entries.

(I have just replaced the file I posted yesterday with an updated one - JBRI)