Ms Excel-I need code to run after a cell has lost focus

[malibu65k]

I have a bid of code that works the way I want it to. But I can't get it to run WHEN I want it too. Currently I have to click off the cell then click back on it then the code runs. I want to run the bit of code when I hit the enter button or after I click off the cell.

I found this code on the web. There are several versions of it. But they all run the same way. I want to modify it to run after the active cell has lost the focus.

Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
Application.ScreenUpdating = True

Any Ideas??

 

[cLFlaVA]

I would dim a variable outside your functions that will hold the last selected cell.

Here's a barebones example:

Dim lastCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox lastCell
    lastCell = Target.Address
End Sub

Now, you can test the value in last cell, and if it matches the cell you want, you call your function.

Hope this helps.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[malibu65k]

Sounded good. I tried it, but it's not exactly what I was looking for. I don't need to test values, just need to recognize when the focus has left a cell. When the cell(any active cell) is no longer active.

To me, I know VBA for Access, this is a piece of cake but as for Excel, I am at a loss.

Any other suggestions??

 

[cLFlaVA]

I'm not sure I follow...

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[TonyJollans]

Hi malibu65k,

You must put your code in the Worksheet_SelectionChange event as shown in cLFlaVA's example. It will then run every time the selection is changed. This function is passed the selection you are moving TO and you must code yourself to remember it so that next time you have the cell you are moving FROM. If need be you can also add some code that runs at workbook open to populate your 'previous selection' variable.

In your worksheet's code module, select "Worksheet" from the dropdown at the top left of the code window and it will default to "SelectionChange" in the right hand dropdown. This will give you the start and end of the function and you add your code between. The extra code you need is

(a) a global variable outside the procedure at the top of the module - in this case it only needs to be a flag, and

(b) code in the module to (i) check the global variable and (ii) reset the global variable for next time

For example, if B1 is the cell you are interested in ..

[blue]Public bMyCell As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If bMyCell Then
    MsgBox "Just left my cell" [green]' Your code instead of this[/green]
End If

bMyCell = Not Intersect(Target, Range("B1")) Is Nothing

End Sub[/blue]

You then need, in the ThisWorkbook code module, code to opopulate your flag, something like ..

[blue]Private Sub Workbook_Open()

Sheet1.bMyCell = Not Intersect(ActiveCell, Range("B1")) Is Nothing

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[malibu65k]

Hi,TonyJollans

Thanks for your response.

I have my code in the Worksheet_SelectionChange. It works good except I have to click back on the cell to activate the code. I tried your suggestion, although it will trigger the messagebox, when I replace this area with my code, it does not work. And I can still click back on the cell I just left, as always the code will be triggered.
I can't figure out why i have to reselect the cell I just entered data to activate the changes.

Actually, no changes are being made, just adding data to the empty cell.

I have set it to be merged and wrap text. I just want the row height to expand after I leave the cell to accommodate the entire text.

Any other suggestions?

Thanks for all your help.

 

[TonyJollans]

Hi malibu65k,

You shouldn't have to reselect the cell and I suspect, perhaps, that merged cells may be causing your problem.

Can you post back with details of pertinent merged cells and the precise sequence of steps (including which cells you click in and where the cursor goes following pressing Enter) and when the code (appears to) run and/or not run.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[malibu65k]

Hi ToniJollans,

I can't figure out why I am having to re-select the cell again.

Anyway, I have merged cells B11. Also I have set these cells to Word Wrap. The Word Wrap functionality doesn't work so well with the merged cells, so I found this code on the internet but it does not work the way I had expected. In these merged cells I would type a long string of text. After entering the text, I press enter and the row does not expand. It wraps within the cell but the row does not expand until after I reselect the merged cell.

Maybe you could copy and paste the code into excel and get a visual. I may not be explaining so well.

Thanks for your help!

 

[TonyJollans]

Hi malibu65k,

Right! I hadn't really looked at your code before. Your problem is caused by the references to [purple]ActiveCell[/purple].

When it runs the ActiveCell is not B1. The process triggers when you leave B1 and the activecell is somewhere else. To make it run, change the two references to activecell to range("b1"). Change from ..

:
:
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
:
:

.. to ..

:
:
If Range("B1").MergeCells Then
With Range("B1").MergeArea
:
:

This is a quick fix. Let me know if it works. Meanwhile I will look at it a bit closer to try and come up with something better.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[TonyJollans]

Looking a bit more closely at the code, a few more changes will be needed.

Merged cells appear to have been forged in Mordor and are best left well alone. Could you not change your format to use "Center Across Selection" instead of merging the cells. You would achieve almost the effect I think you desire without needing any code at all.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[malibu65k]

I have thought about changing the format. It is either that or deal with clicking back on the cell to expand the row. It's still better than manually widening the row.

Thanks for all you help!