Excel macro to call Word macro stopped working

RamaG

New Member
Joined
Jan 19, 2017
Messages
5
I would appreciate any insight as to why the following two previously working (as of December 2016) macros might now give me error #16 (from MS Word). I am using MS Office Plus Professional 2010.

============Excel Macro:===========
Option Explicit
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
'override default behavior only if selection is in one of the
'activation areas as defined by column labels
Dim WordApp As Object
Dim rng As Range
Dim rubricCol As Integer
Dim msg As String
On Error GoTo ErrorHandler
Set WordApp = GetObject(, "Word.Application")

If ActiveSheet.Cells(1, Target.Column) Like "short description" Then 'override double click
'automatically put commentary in the adjacent cell into the active Word document

'Call WordApp.Run("InsertCommentary", ActiveSheet.Cells(Target.Row, Target.Column + 1))
WordApp.Selection.typetext Text:="testing"
Cancel = True
Else
If ActiveSheet.Cells(1, Target.Column) Like "full commentary text" Then 'override double click
'automatic commentary from selected cell into Word document
Call WordApp.Run("InsertCommentary", ActiveSheet.Cells(Target.Row, Target.Column))
Else '--------------------default double click behavior-------------
Cancel = False
End If
End If
ActiveCell.Offset(1, 0).Select
Exit Sub 'only envoke the error handler when an error happens

ErrorHandler: Select Case vbCritical
Case 1004 'generic Excel error - need to add better handling later - now assume Hlookup issue
msg = "generic Excel error"
Case 16
msg = "You cannot insert a comment within another comment. " & _
vbNewLine & "Move your cursor into the body of your Word document."
Case Else
msg = " some other ERROR: "
End Select
MsgBox msg & " Error #" & vbCritical

Exit Sub

End Sub

=======Word Macro===================
Sub InsertCommentary(mystring As String)
'This routine is called from an Excel sheet and pastes
'the contents of the current cell into a comment
On Error GoTo errHandler
Dim msg As String
Selection.Comments.Add Range:=Selection.Range
Selection.TypeText Text:=mystring
ActiveWindow.View.SplitSpecial = wdPaneNone 'close the comments window
Exit Sub ' only envoke error handling if there is an actual error
errHandler:
Select Case vbCritical
Case 1004 'generic Excel error - need to add better handling later - now assume Hlookup issue
msg = "generic Excel error"
Case 16
msg = "You cannot insert a comment within another comment. " & _
vbNewLine & "Move your cursor into the body of your Word document."
Case Else
msg = " some other ERROR: "
End Select
MsgBox msg & " Error #" & vbCritical
Exit Sub
End Sub

I tried commenting out all of the code inside the Word macro that actually does anything and it still spits back error #16.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RamaG

New Member
Joined
Jan 19, 2017
Messages
5
This macro does work, but is not as robust as might be desired. One cause of the above problem is multiple Word documents open at the same time. Essentially the Excel macro does not know which Word document is active and could be going a non-active document (where you could have the cursor inside a comment). Also what can happen is that the macro appears to do nothing, but is actually filling up one of the non-active documents with unwanted comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top