Najbrž sem se srečal z neprijetno omejitvijo...
Moj problem:
Glede na izbrano vrednost v celici, v drugi celici zvezka s pomočjo VBA kode nastavljam validacijo seznam. Vse lepo špila, potem pa pridem do primera, ko je teh elementov malo več in mi jih potem v spustnem seznamu ne prikaže vseh. V primerih, ko je manj elementov, lepo dela.
Elemente polnim v spremenljivko seznam, tipa String.
Koda: Izberi vse
Sub napolni_CC()
Dim celica As Range
Dim seznam As String
Dim test As Range
seznam = ""
'označim šifrant CC
Application.Goto Reference:="CC"
i = 1
department_oznaka = Worksheets("interface").Range("c2").Value
If department_oznaka <> "" And department_oznaka <> "Salaries" Then
For Each celica In Selection
'napolnim tekstovno polje
If celica.Value = department_oznaka Then
seznam = seznam + celica.Offset(0, 1).Value + ","
i = i + 1
End If
Next
'Worksheets("List1").Range("c3").ClearContents
'Worksheets("List1").Range("c3").Value = seznam
'MsgBox i
'nastavimo validacijo
Worksheets("interface").Range("c3").ClearContents
Worksheets("interface").Range("c3").Validation.Delete
Worksheets("interface").Range("c3").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=seznam 'polnim seznam
Worksheets("interface").Activate
Range("c3").Select
ElseIf department_oznaka = "" Then
Worksheets("interface").Activate
Range("c2").Select
MsgBox "Najprej izberi Department"
Else
'MsgBox "sala"
' Dim novi As String
' Dim celica1 As Range
' j = 0
' 'Application.Worksheets.Add
'Application.Goto Reference:="CC"
' For Each celica1 In Range("cc")
''MsgBox celica.Address
' If celica1.Value = "Salaries" Then
' MsgBox celica1.Row
' Worksheets("podatki").Activate
'Range(Selection, Selection.End(xlDown)).Select
'odmik = Range(Selection, Selection.End(xlDown)).Count
'a = celica1.Row
'j = j + 1
'b = celica1.Row + j - 1
'End If
' ActiveWorkbook.Names.Add Name:="aa", RefersToR1C1:="=podatki!R" & a & "C5:R" & b & "C5"
' Worksheets("interface").Activate
'Next
'nastavimo validacijo
Worksheets("interface").Range("c3").ClearContents
Worksheets("interface").Range("c3").Validation.Delete
Worksheets("interface").Range("c3").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=SAL" 'polnim seznam
Worksheets("interface").Activate
Range("c3").Select
End If
End Sub
LP!