Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change line chart color based on condition like + and -. | Charts and Charting in Excel | |||
Change row color based on condition of celss | Excel Worksheet Functions | |||
Change cell format based on condition. | Excel Programming | |||
Color Change in chart based of condition | Charts and Charting in Excel | |||
How do i change the format of a cell based on the condition of another cell in same row? | Excel Programming |