Making a macro run when leaving a cell

rmccafferty · June 11th, 2012, 11:32 PM

I am spoiled by some of the functionality in Access when I use Excel, and am also amazed at what people have figured out how to do in Excel.

What I would like to to in Excel:
When I leave the current cell,
1. evaluate the contents of the cell I am leaving (essentially determining if it is empty or not
2. If it is empty, do nothing
If it is not empty (or maybe if it has a number value in it)
3. Then uncheck a box (a check box form control)

In Access, I would enter code in on Leave property
It would essentially change the value in the text box that would be named or have some other unique method of identifying it.

In Excel, I do not know to make a macro fire upon leaving a cell
And I do not know how to refer to the properties of a specific check box control. that is, there will be many of them in the spreadsheet and I do not see how to name them. Clicking of properties for the check box gave no hint of it.

Apostolos55 · July 11th, 2012, 11:54 AM

create a check box, say Check box 1

use

Code:

for each a in activesheet.shapes:? a.name:next

in immediate to see the name
to my knowledge checkbox must be linked to a cell. So write click on it and link it to a cell, say cell(1,1) (or "A1")
paste the following code in the Worksheet and experiment with the "valid" areas that initially are set to A1:D3. Changes occur for values bigger or smaller than 10...later you can adjust this at will.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim UPRw As Long, DWNRw As Long, LftClmn As Long, RgtClmn As Long, CurRw As Long, CurClmn As Long
Dim dum1

CurRw = Target.Row: CurClmn = Target.Column
UPRw = 1: DWNRw = 3       'Valid Rows
LftClmn = 1: RgtClmn = 4    'Valid Columns

If (CurRw >= UPRw And CurRw <= DWNRw) And (CurClmn <= RgtClmn And CurClmn >= LftClmn) Then
    'we are within valid range
    If Target.Value > 10 Then Cells(1,1) = False Else Cells(1,1) = True

Set dum1 = Selection
ActiveSheet.Shapes("Check Box 1").Select
Selection.Characters.Text = "Check Box " + Format(Target.Value, "00")
dum1.Select

End If

End Sub

Valid Area can be omitted but it is useful to restrict running the code in areas of your interest. Fit to your needs, use ranges and

Code:

set newRange=application.intersect(range1,range2...)
if not newrange is nothing then
...
end if

if needed