changes and passwords

[Guest]

hello.

I am working on a quote tool in excel. people go into the spread sheet -
make their selections and it will come up with a final cost.

at this stage my boss has to validate the quote. I need there to be some
part of the sheet that only he can access and maybe tick a box to say he has
validated the quote.

once he has done this if anybody changes anything in the spreadsheet it
should loose his validation.

any help is appreciated!

 

[Guest]

I think you need to think about how strong you want to make the protected
part of the sheet.

One solution would be to have an area of your sheet set aside for a short
acceptance statement by your boss, but is by default left blank.

Then have a non-printed command button button on the sheet that when clicked
ran some VBA which asked you for a password, and then assuming the password
was correct automatically entered the acceptance statement on the sheet.

You would probably need to add some VBA code into the Change Worksheet event
that checked for changes to your key cells, and removed the acceptance
statement.

On a legal(ish) note, you might also want o add something to your sheet tat
say something about the acceptance being electronically generated, and a
written signature not being necessary. I wrote an application for a client
recently doing something similar, and it was acceptabvle as an ISO 9000
approved system.

If you need more detailed help, then please re-post, but hopefully this
will be enough to get you started.

Neil
www.nwarwick.co.uk

 

[Guest]

ok - I've got a command button that when pressed will ask for a password and
on getting the correct password enters a validation statement into a cell.

I also have some code so that if a certain cell is changed the statement is
removed - is it easy enough to have this happen when various cells are
changed?

only problem is any user can go into the validation part and just type the
statement in. so I would need to lock the cell - open it to put the
statement in - and then lock it again?

Private Sub CommandButton1_Click()
Dim strAnswer As String, MyPassword As String
MyPassword = "Test"
strAnswer = InputBox("Enter Password: ", _
"Password Protected Worksheet...")
If UCase(strAnswer) <> UCase(MyPassword) Then
MsgBox ("Incorrect Password")
Else
Range("E4") = "Validated by.."
' Application.Sheets("Sheet1").Activate
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$10" Then

' this need to be more than one cell - can i put multiple cells here?

Worksheets("Sheet3").Range("E4").Clear
End If
End Sub

is this the best way to do this - or am i making it more complicated?

 

[Guest]

I'll answer your last question first - No you're not, You're doing a good job
as far as I can see.

You can perform your test for changed cells in three ways,
1. As soon as anything in the sheet is changed you remove the validation
statement.
2. You could if there aren't too many cell that could be changed, test each
one with an If statement, problem here is that you will duplicate the code
many times.
3. Set up a range to contain the cells you are interested in and then just
test for a change anywhere in the range.

To add the protection to the staement I would suggest that you remove
locking from all the parts of the sheet the user can change, and then lock
the sheet with a password. (Make sure the cell with the staement is locked)
All you need to do then is to unlock the cell in your code, insert the
statement, and then re-lock the cell.
To Unlock the sheet use the following code.
ActiveSheet.Unprotect Password:="MyPassword"

Then add the statement

Then use the same line as above but change the Unprotect to Protect to
re-protect the sheet.

Hope this helps, post again if you have more questions.

Neil
www.nwarwick.co.uk

 

[Guest]

Ciara,
Just a had another thought on your multiple cell range problem, try using
the following line

If Target.Address = Range("C59,G9:H16,B1418") Then

I haven't tried this so I'm not sure if it will work or not, but it's
probably worth a go (Obviously you will need to change the cell references to
those in your sheet)

 

[Dave Peterson]

You can do something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
dim myRngToCheck as range
set myRngToCheck = me.range("a1,b9,c12,d33,f18,g:g")

if intersect(target,myrngtocheck) is nothing then
'outside that range
'do nothing special
else
application.enableevents = false
worksheets("sheet3").range("e4").clearcontents
'or
'me.range("e4").clearcontents
application.enableevents = true
end if

end sub

If the changes the user makes and the validation are on the same sheet (sheet3),
then I'd use the me.range("e4").clearcontents version. (Me refers to the thing
that owns the code--in this case the worksheet itself.)

The .enableevents stuff stops the change the code makes from triggering the
worksheet_change event.

And I changed .clear to .clearcontents--it just erases the value in the cell.

 

[Guest]

thanks for your ideas guys - the only one I can get to work is by repeating
the if statement for every cell that could change as neil suggested - it's a
lot of repetative code but at least it works!

Thanks for all you help guys - you are stars!

 

[Dave Peterson]

I don't understand why these two statements didn't do what you wanted:

Set myRngToCheck = Me.Range("a1,b9,c12,d33,f18,g:g")

If Intersect(Target, myRngToCheck) Is Nothing Then

The first specifies the cells you want to check. The second checks to see if
the cell(s) you changed included one of those cells.