Help with Code

[Guest]

FIRST.... Let me say thanks for all the help I have received. I am new to
this position and new to VBA and nobody at the company knows it so You all
have been a life saver for me!!!

Here is the question:

Can I write code so than in the worksheet If cell D1 has the value of "Y",
and the user tries to enter data in E1, the cell (E1) would be disabled from
any entries going in?

Thanks again!!!

TimN

 

[Guest]

In a protected sheet you can only change the unlocked cells.
So try to protect this sheet first and then lock or unlock the cell "E1"
depends on the value in cell "D1".

Code would be

if range("D1")="Y" then
range("E1").locked=true
else
range("E1").locked=false
endif

 

[Guest]

Great idea.

Where do I put this code? Is it in the This Workbook module?
All the rest of my code I have driving message boxes, user forms ets. I'm
not sure how to work with this situation where the code just effects a cell
in the workbook.

 

[Guest]

My only other thought is that since the default setting on cells is "locked",
you might have to unlock all of the cells and then re-lock E1 if you want to
be able to change other cells on the spreadsheet.

Dan

 

[Guest]

If it is specific to one worksheet, you can put the code in that sheet module
in the editor. You probably want to put it under a worksheet_change event

Private Sub Worksheet_Change(ByVal Target As Range)

Cells.Locked = False
If Range("D1").Value = "Y" Then
Range("E1").Locked = True
End If
End Sub

 

[Otto Moehrbach]

A thought comes to mind. You say you want entry into that cell disabled.
Do you want to just disable it and not tell the user anything about why he
can't enter anything into that cell? Or do you want him to not be able to
enter anything into that cell AND tell him so and why when he tries to do
it? HTH Otto