Using VB code to change form values.

[ginger_magician]

Im doing a spreadsheet for my A Level ICT coursework and have multiple sheets that are hidden from view unless a password is entered (all other routes to unhiding sheets are locked out). At the moment the password can only be changed if a user changes it in the Visual BASIC editor.

Private Sub ok_Click()
  If password = chngpass.key Then
      Unload Me
      Correct.Show
      ActiveWorkbook.Unprotect
      Sheets("PaySlips").Visible = True
      Sheets("Shifts").Visible = True
      Sheets("Shifts").Select
      Application.DisplayFormulaBar = True
      Application.ActiveWindow.DisplayWorkbookTabs = True
  Else
      Unload Me
      wrong.Show
  End If
End Sub

As you can see, the password is stored on a different form (chngpass) I wish to use this form to enable the "admin" to change the password without the hassle of the VB editor. I have used "Nested If" statements (if thats what they are called in VB) to validate the original password and the new/repeated passwords before allowing any change, the only problem that I am unable to work around is permenantly changing the value of the text box that the password is stored in, as it just resets when it closes. Can someone help me by sending a line of code that will work around this problem if it exists?
Thanks in advance,

Craig.

[bigu2fan00]

I'm not sure what you mean when you say it just resets.....do you mean the new password isn't being saved??

If so, then you might try this:
Activeworkbook.save

(Just guessing)

Also, I got a question for you? How do you lock out a hidden sheet? The user can't go to Format/Sheet/Unhide to view the hidden sheet? Please let me know how because I could use that trick.

Thanks.

[ginger_magician]

I put this code into a user form that runs when the workbook loads up, it simply disables the "Format" menu.

Application.CommandBars("Format").Enabled = False

The "Format" can be changed to match any of the menu buttons except for "Insert" and I'm unsure if it can be locked out at all.

I also put this code in to stop users gaining access to the Visual BASIC Editor by right clicking on the sheet tabs and selecting "View Code".

Application.ActiveWindow.DisplayWorkbookTabs = False

[AlienVoyager]

Set newSheet = Worksheets.Add
newSheet.Visible = xlVeryHidden

will hide a sheet from the format view, I have used it to hide a user sheet from all users except admins.

I hope this helps

"There are 10 kinds of people in the world, those that understand binary and those that don't"