SOLVED...

 

i,
I found this code online (Sigma Coding) which would suit to my needs of getting names of Charts and Tables in excel workbook.
Howeever, I have the problem taht i get into validate list only the last chart/table entry in the array and cannot figure out what is wrong. With the debug.print I get the lisitng in the array and it is correct:
Tabela1-Export-ListObject
Grafikon1-Costs-ChartObject
Grafikon2-Costs-ChartObject
Tabela25-Costs-ListObject
graf_Sales-Sales-ChartObject
Tabela3-Sales-ListObject

However in the drow down list I get only the last one (Tabela3-Sales-ListObject). I assume it is the problem within the .Add Type line. Would somebody help me? The code is listed below.

Thank you and BR,
Andrej

------------------------------------------------------------------
Sub UpdateDropdownColumn()

Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim xlTable As ListObject
Dim xlTableColumn As ListColumn
Dim xlChartObject As ChartObject
Dim xlTableObject As ListObject

Dim ObjectArray() As String
Dim ObjectArrayIndex As Integer

'set the book
Set xlBook = ThisWorkbook

'Loop trough each worksheet

For Each xlSheet In xlBook.Worksheets

'if we have Chart
If xlSheet.ChartObjects.Count > 0 Then

'Grap each chart name
For Each xlChartObject In xlSheet.ChartObjects

'Update the count
ObjectArrrayIndex = ObjectArrayIndex + 1
ReDim Preserve ObjectArray(ObjectArrayIndex)

'Add the chart object to the array
ObjectArray(ObjectArrayIndex) = xlChartObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlChartObject)
Debug.Print ObjectArray(ObjectArrayIndex)
Next
End If

'if we have table
If xlSheet.ListObjects.Count > 0 Then

'Grap each chart name
For Each xlTableObject In xlSheet.ListObjects

'Update the count
ObjectArrrayIndex = ObjectArrayIndex + 1
ReDim Preserve ObjectArray(ObjectArrayIndex)

'Add the chart object to the array
ObjectArray(ObjectArrayIndex) = xlTableObject.Name & "-" & xlSheet.Name & "-" & TypeName(xlTableObject)

Next
End If

Debug.Print ObjectArray(ObjectArrayIndex)

Next
'Grab the sheet
Set xlSheet = xlBook.Worksheets("Export")

'Grab the table from the sheet
Set xlTable = xlSheet.ListObjects("Tabela1")

'grab the Object Column from my table
Set xlTableColumn = xlTable.ListColumns("Graphs")

'Set the validation dropdown
With xlTableColumn.DataBodyRange.Validation

'delete the old one
.Delete

'add the new data to the dropdown,
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ObjectArray, ",")

'make sure it is a dwopdown
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True

End With
End Sub

-------------------------------------------------