Error in Validation.Add - Application define or Object defined error" in Excel 2013 only

rsinha

New Member
Joined
Jun 11, 2015
Messages
1
Hi,
I have following code in Excel macro that puts validation rule on a range of cells -

Code:
'// Open the File that was selected
    If Right(sInFile, 3) <> "csv" Then
      '//Array of all 256 colums
      For i = 0 To 255
        vFieldInfo(i) = Array(i + 1, 2)
      Next i
      Workbooks.OpenText Filename:=sInFile, _
                         Origin:=xlWindows, _
                         StartRow:=1, _
                         DataType:=xlDelimited, _
                         TextQualifier:=xlDoubleQuote, _
                         ConsecutiveDelimiter:=False, _
                         Tab:=True, _
                         Semicolon:=False, _
                         Comma:=False, _
                         Space:=False, _
                         Other:=False, _
                         FieldInfo:=vFieldInfo
     Else
      Workbooks.Open Filename:=sInFile
     End If
     
     Sheets(1).Select
     Sheets(1).Name = sInSheet
     Sheets(1).Unprotect
     OpenFile = True

'// Specify range that must have validation rule
  Dim iLastCol As Integer
    Dim rRange As Range

    iLastCol = Range("I6").Value
    Set rRange = Range(Cells(iRow, 10).Address + ":" + _
                      Cells(iRow + 2, iLastCol).Address + "," + _
                     Cells(iRow + 4, 10).Address + ":" + _
                     Cells(iRow + 5, iLastCol).Address)
    
'// Validation
        For Each area In rRange.Areas
            count = area.count()
            area.Activate
            area.Locked = False
            area.Value = 0
           With area.Validation
           .Delete
          .Add Type:=xlValidateWholeNumber, _
             AlertStyle:=xlValidAlertStop, _
             Operator:=xlGreaterEqual, _
             Formula1:="0"
          .ErrorTitle = "Integers"
          .ErrorMessage = "Please enter a positive integer value"
       End With

When I run this program in debug mode, i.e. by pressing F8 line by line, the validation rule is
correctly placed in the output file on the above range. But when I run it by clicking on a button
I get following error on ".Add Type" in area.Validation-

"Application define or Object defined error"

I checked the value of rRange both in debug mode and clicking on the button and the
range values are the same in both. Also this error occurs only with Excel 2013. On the
previous versions it works fine.

Does any one know why it doesn't work in Excel 2013, but works when we run it in debug?

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,207,173
Messages
6,076,923
Members
446,242
Latest member
JECYN

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
Back
Top