Run Macro on Cell Lost Focus

hockeyman9474 · #1 04-03-2017, 10:47 AM

I have a word doc im using as form. There are a few cells I am using as currency. I am using the following code (which work) but I can't figure out how to run the macro when loosing focus in a cell on the table in word. Any ideas?

Code:

Sub CurrConvert()
'
' CurrConvert Macro
'
'
 Dim tCell As Word.Cell
    Dim tRange As Range
    If Selection.Type = wdSelectionIP Or Not Selection.Information(wdWithInTable) Then
        MsgBox "Select numerical values in tables cells before running this macro.", , "Error"
        Exit Sub
    End If
    For Each tCell In Selection.Cells
        Set tRange = tCell.Range
        tRange.End = tRange.End - 1
        With tRange
            If IsNumeric(tRange) Then
                .Text = FormatCurrency(Expression:=.Text)
            End If
            On Error GoTo Skip
Skip:
        End With
    Next tCell
End Sub

macropod · #2 04-03-2017, 04:31 PM

Word doesn't have the kind of change events Excel has, so your options are limited. Although Word does have a WindowSelectionChange event, instantiating it requires the creation of an event handler and class module for the code and, even then, would only work in your situation if you were select something in a table after typing whatever you want to the macro to work on. Still, if you're interested in going down that path, see:
http://wordmvp.com/FAQs/MacrosVBA/In...tSavePrint.htm
http://wordmvp.com/FAQs/MacrosVBA/AppClassEvents.htm

The 'event' macro might then be coded along the lines of:

Code:

Private Sub oApp_WindowSelectionChange(ByVal Sel As Selection)
Application.ScreenUpdating = False
If Sel.Information(wdWithInTable) <> True Then Exit Sub
Dim tCell As Word.Cell, tRange As Range
For Each tCell In Selection.Cells
  Set tRange = tCell.Range
  tRange.End = tRange.End - 1
  With tRange
    If IsNumeric(.Text) Then
      .Text = FormatCurrency(Expression:=.Text)
    End If
  End With
Next tCell
Application.ScreenUpdating = True
End Sub

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Start macro when placeholder gets focus	PellePetimeter	Word VBA	5	03-16-2017 04:26 AM
Macro to move focus after entry in a cell	Phil H	Excel Programming	3	06-18-2015 01:20 PM
Macro will not read the value of cell with a formula in it!	grayson1231	Excel Programming	10	03-28-2015 03:47 PM
Macro to change cell references	SaneMan	Excel Programming	6	10-01-2013 02:49 PM
Macro based on cell value	ubns	Excel Programming	1	05-07-2012 04:03 AM