VBA: how to... cell properties has to change based on a condition
 

Hi Everyone,

i'm new to this forum and i have a question for you reguardin
excel VBA. I want to declare in a module a function or sub that wil
change the CELL properties where the function has been called from i
another cell contains certain information...example:

A B C D
1 100 5.5 =ONOFFCell(A1)
2 0.5 2 =ONOFFCEll(A2)
3 .
4 .

the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so tha
people cannot enter anything in C1 cell IF the cell A1 value let say i
bigger than "0". Then on each line i will have to repeat the sam
function etc. etc.

How can i do that? Can someone help me ?

Thank you!

William

--
will9
-----------------------------------------------------------------------
will99's Profile: http://www.excelforum.com/member.php...fo&userid=2679
View this thread: http://www.excelforum.com/showthread.php?threadid=40041

how to... cell properties has to change based on a condition
 

Unlock the cells you need to edit usign FormatCellsProtectionuncheck
Locked. Protect the worksheet and use this routine. Change the range for r
to suit your application. If the value in column 1 (A) is 0 the
corresponding cell in column 3 (C) is locked.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets(1).Unprotect
For r = 1 To 10
If Cells(r, 1).Value 0 Then
Cells(r, 3).Locked = True
Else
Cells(r, 3).Locked = False
End If
Next
Worksheets(1).Protect
End Sub

--
Ian
--
"will99" wrote in
message ...

Hi Everyone,

i'm new to this forum and i have a question for you reguarding
excel VBA. I want to declare in a module a function or sub that will
change the CELL properties where the function has been called from if
another cell contains certain information...example:

A B C D
1 100 5.5 =ONOFFCell(A1)
2 0.5 2 =ONOFFCEll(A2)
3 .
4 .

the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that
people cannot enter anything in C1 cell IF the cell A1 value let say is
bigger than "0". Then on each line i will have to repeat the same
function etc. etc.

How can i do that? Can someone help me ?

Thank you!

William!


--
will99
------------------------------------------------------------------------
will99's Profile:
http://www.excelforum.com/member.php...o&userid=26790
View this thread: http://www.excelforum.com/showthread...hreadid=400416

VBA: how to... cell properties has to change based on a condition
 

Generally a function returns a value and a sub does work (change formatting)

You could possibly use a function to call a sub but i think it would need to
know both the cell "A1" address and the address where the function is.

My preference for this type of thing would be to use the worksheet change
event
when Target is in Column A
then if target 0 then
reset properties for target.offset(0,2)
reset could lock cell and change color of background to emphisize the lock.

"will99" wrote:


Hi Everyone,

i'm new to this forum and i have a question for you reguarding
excel VBA. I want to declare in a module a function or sub that will
change the CELL properties where the function has been called from if
another cell contains certain information...example:

A B C D
1 100 5.5 =ONOFFCell(A1)
2 0.5 2 =ONOFFCEll(A2)
3 .
4 .

the ONOFFCELL (sub or function) must DISABLE ITSELF (CELL C1) so that
people cannot enter anything in C1 cell IF the cell A1 value let say is
bigger than "0". Then on each line i will have to repeat the same
function etc. etc.

How can i do that? Can someone help me ?

Thank you!

William!


--
will99
------------------------------------------------------------------------
will99's Profile: http://www.excelforum.com/member.php...o&userid=26790
View this thread: http://www.excelforum.com/showthread...hreadid=400416

VBA: how to... cell properties has to change based on a condition
 

Generally a function returns a value and a sub does work (chang
formatting)

You could possibly use a function to call a sub but i think it woul
need to
know both the cell "A1" address and the address where the function is.

My preference for this type of thing would be to use the workshee
change
event
when Target is in Column A
then if target 0 then
reset properties for target.offset(0,2)
reset could lock cell and change color of background to emphisize th
lock.



Can you please show it to me with an example in real code?

Thank you!

William

--
will9
-----------------------------------------------------------------------
will99's Profile: http://www.excelforum.com/member.php...fo&userid=2679
View this thread: http://www.excelforum.com/showthread.php?threadid=40041