Excel Query

  gazmania 22:12 05 Mar 2004

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 2004

Is this just one cell?

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

  AubreyS 22:33 05 Mar 2004


Tell him to look in Google!! :-)

He's mate of mine

  daba 22:36 05 Mar 2004

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 2004

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.

  gazmania 22:48 05 Mar 2004

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 2004

Try something along these lines....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ActiveCell.Locked = False Then


With ActiveCell

.Locked = True

End With

End If


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 2004

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 2004

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 2004

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 2004

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.

Elsewhere on IDG sites

iMac Pro review

Why this awful City of Los Angeles job ad for a graphic designer is actually brilliant

iMac Pro review

Les meilleures prises CPL (2018)