Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-03-2017, 10:47 AM
hockeyman9474 hockeyman9474 is offline Run Macro on Cell Lost Focus Windows 7 64bit Run Macro on Cell Lost Focus Office 2010 32bit
Novice
Run Macro on Cell Lost Focus
 
Join Date: Apr 2017
Posts: 1
hockeyman9474 is on a distinguished road
Default Run Macro on Cell Lost Focus

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

Last edited by macropod; 04-03-2017 at 04:16 PM. Reason: Added code tags
Reply With Quote
  #2  
Old 04-03-2017, 04:31 PM
macropod's Avatar
macropod macropod is offline Run Macro on Cell Lost Focus Windows 7 64bit Run Macro on Cell Lost Focus Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Macro on Cell Lost Focus Start macro when placeholder gets focus PellePetimeter Word VBA 5 03-16-2017 04:26 AM
Run Macro on Cell Lost Focus Macro to move focus after entry in a cell Phil H Excel Programming 3 06-18-2015 01:20 PM
Run Macro on Cell Lost Focus 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft