Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Hi all,
I have a datasheet that Ive written some code to find the first empty row. I will then enter data across that row to Column M. I will enter some data in Column K that I want to check by running some IF statements when I leave the current cell in Column K. What I dont know how to do is how to call that routine when I leave the current cell in Column K. Is it possible? Any help or suggestions are always appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Hi Jeff,
Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Count 1 Then Exit Sub Set rng = Me.Columns("K") If Not Intersect(Target, rng) Is Nothing Then 'Your processing code End If End Sub '<<============= --- Regards, Norman "jeffP" wrote in message ... Hi all, I have a datasheet that I've written some code to find the first empty row. I will then enter data across that row to Column M. I will enter some data in Column K that I want to check by running some IF statements when I leave the current cell in Column K. What I don't know how to do is how to call that routine when I leave the current cell in Column K. Is it possible? Any help or suggestions are always appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Hi Jeff,
The suggested code represents an event procedure and should be pasted into the worksheet code module - not a standard module. If you want to run an existing procedure when a column K cell is changed, add a line like: Call MyMacro as your processing code. --- Regards, Norman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Norman,
this works terrific although I truly don't understsand the "If not is nothing " statement. I'll keep trying to understand so someday...I can write them myself. In the meantime, I still need help. When this runs I call some nested IF's to check and proceed on different answers. My problem is the the Work_Sheet change is just that: if the cell is left empty (which I don't want to allow) no change happens to call the trailing code. Can I somehow check for a blank cell, with this or some other way. As always any help and education is greatly appreciated "Norman Jones" wrote: Hi Jeff, The suggested code represents an event procedure and should be pasted into the worksheet code module - not a standard module. If you want to run an existing procedure when a column K cell is changed, add a line like: Call MyMacro as your processing code. --- Regards, Norman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Hi Jeff,
this works terrific although I truly don't understsand the "If not is nothing " statement. The following lines are intended to limit macro action to changes in a column K cell: Set rng = Me.Columns("K") If Not Intersect(Target, rng) Is Nothing Then I would have liked to say: If Intersect(Target, rng) Is Something Then but the keyword something does not exist. Since, however, the keyword Nothing does exist, I use this with a double negative to obtain a valid equivalent expression. My problem is the the Work_Sheet change is just that: if the cell is left empty (which I don't want to allow) no change happens to call the trailing code. Can I somehow check for a blank cell, with this or some other way. As written, the code is invoked in response to a change in a column K cell: if an entry is deleted (so that the cell becomes blank), the code will run. Would your purpose be satisfied if code were to check the column K cell when an entry is made in the corresponding column L:M cells? --- Regards, Norman "jeffP" wrote in message ... Norman, this works terrific although I truly don't understsand the "If not is nothing " statement. I'll keep trying to understand so someday...I can write them myself. In the meantime, I still need help. When this runs I call some nested IF's to check and proceed on different answers. My problem is the the Work_Sheet change is just that: if the cell is left empty (which I don't want to allow) no change happens to call the trailing code. Can I somehow check for a blank cell, with this or some other way. As always any help and education is greatly appreciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calling procedure when leaving cell
Norman,
Thanks for the explaination/education. At least on the right track. I was trying to "simplify" the code by eliminating the double negatives so MY logic : If Intersect(target,rng) then but alas.......... :) not allowing blank cell : cells L:M are possible empty but these would be allowed. I do have a column (B) where data would be mandatory for the row/record to exist so MY logic is somewhere a test like If B5 "" Then but I don't know if this is a proper test or where to put it. As you know MY logic and VBA logic is not necessarily compatible :) thanks again jeffp "Norman Jones" wrote: Hi Jeff, this works terrific although I truly don't understsand the "If not is nothing " statement. The following lines are intended to limit macro action to changes in a column K cell: Set rng = Me.Columns("K") If Not Intersect(Target, rng) Is Nothing Then I would have liked to say: If Intersect(Target, rng) Is Something Then but the keyword something does not exist. Since, however, the keyword Nothing does exist, I use this with a double negative to obtain a valid equivalent expression. My problem is the the Work_Sheet change is just that: if the cell is left empty (which I don't want to allow) no change happens to call the trailing code. Can I somehow check for a blank cell, with this or some other way. As written, the code is invoked in response to a change in a column K cell: if an entry is deleted (so that the cell becomes blank), the code will run. Would your purpose be satisfied if code were to check the column K cell when an entry is made in the corresponding column L:M cells? --- Regards, Norman "jeffP" wrote in message ... Norman, this works terrific although I truly don't understsand the "If not is nothing " statement. I'll keep trying to understand so someday...I can write them myself. In the meantime, I still need help. When this runs I call some nested IF's to check and proceed on different answers. My problem is the the Work_Sheet change is just that: if the cell is left empty (which I don't want to allow) no change happens to call the trailing code. Can I somehow check for a blank cell, with this or some other way. As always any help and education is greatly appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
copying cell names | Excel Discussion (Misc queries) | |||
When calling a different file, can I make the tab equal to a cell? | Excel Discussion (Misc queries) | |||
prevent a user leaving a blank cell in excel2003 | Excel Discussion (Misc queries) | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |