Surface Pro 5 News - release date, UK price, features, specs
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.
Tell him to look in Google!! :-)
He's mate of mine
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.
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.
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.
Try something along these lines....
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell.Locked = False Then
.Locked = True
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.
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)
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
Target.Value = oldvalue
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.
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.
This thread is now locked and can not be replied to.