Setting Data Validation causes 1004 error.

[a15457]

Good afternoon all,


I have a macro enabled workbook running in Excel 2016. On one tab I've set up a button that runs a macro that will add a new row to a table and set some values in it.


Here's the code.

Sub ServiceHealth_Tbl()
Dim lo As ListObject
Dim FinalRow As Long
Dim myValue As Variant
Dim calcFormulaValue As String
Dim calcRangeValue As String


FinalRow = 0
Set lo = ActiveSheet.ListObjects("ServiceHealth")
lo.ListRows.Add , 1
myInputValue = InputBox("Enter the name of the Service Health item.", "Service Health item entry.", "ITSM connector for ServiceNow")
FinalRow = Range("B" & Rows.Count).End(xlUp).Row
If FinalRow = 12 Then Cells(FinalRow, "B").Value = 1
' I know this isn't the best way to do this but it works for me jsut now ... feel free to improve it.
If FinalRow > 12 Then calcFormulaValue = "=B" & FinalRow - 1 & "+1"


If FinalRow > 12 Then Cells(FinalRow, "B").Value = calcFormulaValue


Cells(FinalRow, "C").Value = myInputValue


Cells(FinalRow, "F").Value = "='Version Control'!$G$11"
Cells(FinalRow, "G").Value = "='Version Control'!$I$11"
Cells(FinalRow, "H").Value = "='Version Control'!$G$11"
calcRangeValue = "F" & FinalRow
'Cells(FinalRow, "F").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='Version Control'!$G$11:$G$21"
'Range(calcRangeValue).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertInformation, Formula1:="='Version Control'!$G$11:$G$21"
Cells(FinalRow, "G").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='Version Control'!$I$11:$I$16"
'Cells(FinalRow, "H").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='Version Control'!$G$11:$G$21"
End Sub

My problem is this ... if I "empty" out my table and run the macro ... it works perfectly for the first row addition ... but if I run it again then it fails at the data validation line with the annoyingly vague "Run-time error '1004': Application-defined or object-defined error.", and the same if I "end" it and try and run it again on subsequent rows.


Can anyone suggest anything that might help me sort this out? I don't get why it works fine for one row and then not for other rows.

Kind regards,

Dave.

 

[Paul Ked]

Can you post a link to the file (with private data replaced with examples if needed) as I can't replicate the problem. Thanks

 

[a15457]

Hiya Paul,

I've resuscitated my old Dropbox account ...

Here's a link to the shared file... Should be pretty anonymous.

https://www.dropbox.com/sh/9irlvhe4b608zpf/AABasG_TscJbs5OiEpV1RCqAa?dl=0

The macro button is on the "Service Health" Tab.

Cheers,

Dave

 

[a15457]

Hiya Paul ...

Never mind ... One of my colleagues managed to work it out ...

It was failing because the data validation was copying down for subsequent rows ... but there was no data validation when I was doing the "first" row so there was no error.

So all I need to do now is a Cells(reference).Validation.delete in the right spot or a check to see if it's the first row or not and then bobs my godfather.

Thanks for trying to have a look anyway!!!

Kind regards,

Dave.



PS. I've deleted the dropbox file too.

 

[Paul Ked]

Hi Dave, I had just downloaded it and started to look when you replied (it's 05:15 here!).

Thanks for that. It appears that once you've put validation in a cell it is automatically copied down whether in a table or not. New to me too