automatically run a macro based on a cell value

[carmenlisa]

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 indentifying 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.

 

[kjv1611]

Have you looked into using something like the Worksheet On Change event?

Here's a reference rather than my trying to retype or explain it all:

http://www.ozgrid.com/VBA/run-macros-change.htm

They do have a big advertisement at the top of their page about hiring out their services, but the information is below the advertisement. Just wanted to point that out - I have no affiliation with that site, other than finding the results via Google. I do look at that site sometimes, amongst many others.

Here's another reference from Chip Pearson:

http://www.cpearson.com/excel/Events.aspx

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

hi,

Use the Worksheet_SelectionChange event.

There is no event upon leaving a cell. But you could, by storing the cell address and previous address, you could figure out if you were leaving the cell in question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

This is how I have previously set up trapping the range that was left in excel:

Public rng_Last As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If rng_Last Is Nothing Then

    MsgBox "This is the 1st selected cell"

Else

    MsgBox "cell that was left is: " & rng_Last.Address

End If

Set rng_Last = Target

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[carmenlisa]

I see the Event and should be able to make that work. I still have the send part of the problem to deal with. How to refer to the check box controls. In Access, each one would have a name and I could just change the property of the check box to make it be checked or not checked. I do not see where these controls have any sort of name in Excel. Surly there is a way to do this?

As I kept poking around, I right clicked on the box thinking that somehow it would show be a place to see/edit properties. Eventually, in the format control option, I found a tab that says web and indicates the two sample check boxes I inserted controls are named Check Box 1 and Check Box 2. That is, these names are said to be the alternative text for the controls if used on the web. Will VBA also see this as the name of the various controls?

 

[xlbo]

is there a checkbox next to each cell that can be left?

In general the checkboxes would work in exactly the same way although you have the complication of tying the checkbox to the cell that has been left

This is probably best accomplished using the linkedcell property of the checkbox (there are 2 different types - control and forms - both have a linkedcell property)

Lsets assume the cells that can be left are all in column A and your checkboxes are in column B

Starting from row 2 and going down to row 10

Set up each checkbox to have it's linked cell as the cell it sits over so B2:B10 in line with the cells that need to be checked - this should then be the basis of what you need:

Public rng_Last As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim iSect As Range

On Error Resume Next

Set iSect = Intersect(rng_Last, Range("A1:A10"))

On Error GoTo 0

If Not iSect Is Nothing Then

If rng_Last Is Nothing Then

MsgBox "This is the 1st selected cell"

Else

If rng_Last.Value >= 1 Then

rng_Last.Offset(0, 1).Value = 1

Else

rng_Last.Offset(0, 1).Value = 0

End If

End If

Set rng_Last = Target

Else

Set rng_Last = Target

End If

End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question