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!
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
"Ciara" wrote:
> 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!
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?
"Neil" wrote:
> 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
>
> "Ciara" wrote:
>
> > 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!
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
"Ciara" wrote:
> 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?
>
>
> "Neil" wrote:
>
> > 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
> >
> > "Ciara" wrote:
> >
> > > 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!
Ciara,
Just a had another thought on your multiple cell range problem, try using
the following line
If Target.Address = Range("C5:D9,G9:H16,B14:D18") 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)
"Ciara" wrote:
> 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?
>
>
> "Neil" wrote:
>
> > 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
> >
> > "Ciara" wrote:
> >
> > > 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!
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.
Ciara wrote:
>
> 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?
>
> "Neil" wrote:
>
> > 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
> >
> > "Ciara" wrote:
> >
> > > 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!
--
Dave Peterson
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" wrote:
> 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.
>
>
>
> Ciara wrote:
> >
> > 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?
> >
> > "Neil" wrote:
> >
> > > 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
> > >
> > > "Ciara" wrote:
> > >
> > > > 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!
>
> --
>
> 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.
Ciara wrote:
>
> 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" wrote:
>
> > 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.
> >
> >
> >
> > Ciara wrote:
> > >
> > > 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?
> > >
> > > "Neil" wrote:
> > >
> > > > 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
> > > >
> > > > "Ciara" wrote:
> > > >
> > > > > 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!
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks