Excel Validation.Add not working from MS Word VBA

Question

I have a MS Word document that generates an Excel populated with data from a table in the Word document. The Excel version of the Word table is intended to be a checklist. As such, I'm trying to add features to the generated excel to make it more useful. I tried to add conditional formatting (to make a row green if the row is marked complete) and Validation (so a Completed column has a dropdown with Yes or No). I can make both work in Excel but can not get VBA from Word to add the functionality to the generated Excel.

In both cases the .Add method seems to be causing the problem. I have tried 2 syntaxes to do this:

With objNewExcel.Sheets(1).Range("e5").Validation 
   .Add Type:=xlValidateWholeNumber, _
        AlertStyle:= xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:="5", _
        Formula2:="10" 
   .InputTitle = "Integers" 
   .ErrorTitle = "Integers" 
   .InputMessage = "Enter an integer from five to ten" 
   .ErrorMessage = "You must enter a number from five to ten" 
End With

Returns Error "excel runtime error '1004' application-defined or object-defined error" which is not useful

and

objNewExcel.Sheets(1).Range("e5").Validation.Add(xlValidateWholeNumber, xlValidAlertStop, xlBetween, "5", "10")

Returning Error "Expected: =". When I set the Validation to a var it gives me a type miss match error. I DIMed the var as an object.

I can't get either to work from MS Word VBA. Does anyone know how to reference the Excel .Add method from Word VBA?

Ben Ben · Accepted Answer · 2017-09-22T15:05:12

With help from a friend I was able to modify the second syntax to work.

Using:

objNewExcel.Sheets(1).Range("e5").Validation.Add (xlValidateWholeNumber, xlValidAlertStop, xlBetween, "5", "10")

Word VBA expects a returned value. If you add "Call" to the beginning of the line or remove "(" and ")" from the .Add method, VBA knows that there is not return and it works.

Working Syntax's:

objNewExcel.Sheets(1).Range("e5").Validation.Add xlValidateWholeNumber, xlValidAlertStop, xlBetween, "5", "10"

and

Call objNewExcel.Sheets(1).Range("e5").Validation.Add (xlValidateWholeNumber, xlValidAlertStop, xlBetween, "5", "10")

Excel Validation.Add not working from MS Word VBA

1 Answers