Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autocomplete
I In column I, I have a dropdown menu listing either "yes" or "no".
What I would like to do is, when any other cell within that row contains data, for the cell in colum I to change automatically to "no" until user changes it to "yes" So for example, if I enter any data into cells A1 or B1 or C1 etc etc, then cell I1 will automatically fill with the word "no" Any suggestions? I have been told I can do this with a worksheet_change event but ain't got the foggiest. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autocomplete
Right click on the sheet tab and select view code. In the resulting module,
in the upper left dropdown, select Worksheet and in the upper right dropdown select Change (not selectionchange) this will cause the declaration of the Change event of which you speak: Private Sub Worksheet_Change(ByVal Target As Range) End Sub so within this declaration you would put code like this: Private Sub Worksheet_Change(ByVal Target As Range) If target.count 1 then exit sub if target.column =1 and target.column <= 3 then if application.CountA(cells(target.row,1).Resize(1,3) ) 0 then cells(target.row,"I").value = "no" end if end if End Sub -- Regards, Tom Ogilvy "Worzel Gummidge" wrote in message .uk... I In column I, I have a dropdown menu listing either "yes" or "no". What I would like to do is, when any other cell within that row contains data, for the cell in colum I to change automatically to "no" until user changes it to "yes" So for example, if I enter any data into cells A1 or B1 or C1 etc etc, then cell I1 will automatically fill with the word "no" Any suggestions? I have been told I can do this with a worksheet_change event but ain't got the foggiest. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autocomplete
Thanks Tom
"Tom Ogilvy" wrote in message ... Right click on the sheet tab and select view code. In the resulting module, in the upper left dropdown, select Worksheet and in the upper right dropdown select Change (not selectionchange) this will cause the declaration of the Change event of which you speak: Private Sub Worksheet_Change(ByVal Target As Range) End Sub so within this declaration you would put code like this: Private Sub Worksheet_Change(ByVal Target As Range) If target.count 1 then exit sub if target.column =1 and target.column <= 3 then if application.CountA(cells(target.row,1).Resize(1,3) ) 0 then cells(target.row,"I").value = "no" end if end if End Sub -- Regards, Tom Ogilvy "Worzel Gummidge" wrote in message .uk... I In column I, I have a dropdown menu listing either "yes" or "no". What I would like to do is, when any other cell within that row contains data, for the cell in colum I to change automatically to "no" until user changes it to "yes" So for example, if I enter any data into cells A1 or B1 or C1 etc etc, then cell I1 will automatically fill with the word "no" Any suggestions? I have been told I can do this with a worksheet_change event but ain't got the foggiest. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autocomplete | New Users to Excel | |||
AutoComplete | Excel Discussion (Misc queries) | |||
autocomplete | Excel Programming | |||
Using autocomplete | Excel Worksheet Functions | |||
autocomplete? | Excel Discussion (Misc queries) |