Excel Query

  gazmania 22:12 05 Mar 04
Locked

I'm hot on Excel functionality but I'm a novice on VB. What I want to do is lockout a cell, but only after allowing input to it once. Eg. Original value of cell is null. Allows input of data once. Cell becomes locked and suppresses any further input to that cell. I appreciate that a user could circumvent the lockout simply by closing the workbook, then reopening it again. However, any ideas would be appreciated.

  VoG II 22:29 05 Mar 04

Is this just one cell?

He said, stalling for time. This is not an easy one!

  AubreyS 22:33 05 Mar 04

VoG

Tell him to look in Google!! :-)

He's mate of mine

  daba 22:36 05 Mar 04

surely it should be straightforward.

on cell value change, set the cell property to 'locked' and the sheet protection on ?

as i havn't actually tried it yet i may underestimated the problem.

I thought i'd leave it to you in the first place as i knew i couldn't get the answer in b4 u.


gazmania, if after the initial entry, subsequent entries would prompt for an 'are you sure' type dialog, would that not be preferable to a complete lockout, which as you rightly say can be overcome.

  gazmania 22:40 05 Mar 04

Ah, actually, there may be up to 10 seoarate cells that would need the same properties. I will put a formula in an adjacent cell (in fact, the formula can go anywhere as long as it refers to the cell in question) which simply checks the value, eg. =if(upper(D5)="CAT","Correct, Well Done!","Wrong. Try Again")
The idea is cell D5 will have the lockout function and the user will only have one chance to input a value into that cell.
Arghhhhh

  gazmania 22:48 05 Mar 04

Thanks daba. I think your idea could work unless some smarty pants simply explores the menu bar and discovers how to turn off workbook protection.
I think it may be possible to use daba's idea with the addition of switching off the menu item for cell protection. Some clever VB would do it. Beyond me however.

  daba 22:52 05 Mar 04

Try something along these lines....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ActiveCell.Locked = False Then

Sheet1.Unprotect

With ActiveCell

.Locked = True

End With

End If

Sheet1.Protect

End Sub

As the macro automatically protects the sheet, you'll have to manually turn it off to clear the data from, and to unlock, the cells that have had entries made.

  daba 22:56 05 Mar 04

As for the menu item to 'unprotect' the sheet, this is circumnavigated by the VBA code locking the sheet with a password (be wary when playing password games in vba, sometimes it can go horribly chest skywards, especially in Excel '97)

  VoG II 23:11 05 Mar 04

Right click sheet tab, View Code. DElete whatewver is there and paste in:

Dim used, oldvalue

Private Sub worksheet_change(ByVal Target As Excel.Range)

If Target.Address <> "$A$1" Then Exit Sub

If used = 0 Then

used = used + 1

oldvalue = Target.Value

Exit Sub

End If

Target.Value = oldvalue

Exit Sub

End Sub

  daba 23:21 05 Mar 04

Seems to me your solution requires repetitive code, and a 'used' counter (could be a boolean flag) for all cells on the sheet.

I still think my idea is the right line to take, as it just flips the 'locked' status of any cell that has an entry made.

  VoG II 23:25 05 Mar 04

True - it's been a long day that did not start in this country. Your's is definitely the better solution where multiple cells are involved.

Best regards.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…