Shake píše:
Chcel by som sa opytat akym sposobom je mozne zadat do VBA cyklus, ktory by kontroloval ci objekt typu ComboBox zo skupiny AktiveX je prazdny alebo nie.
Mam vytvorenu formu pomocou prvkou AktiveX, konkretne len Combo boxy aby uzivatel mohol zadat len navolene nazvy. Tato forma sa potom dalej spracovava a vytvara sa z nej tabulka na import do systemu. Obsahuje cez 50 riadkov, kazdy zacina Combo boxom a chcem proceduru spracovania zadat inteligentne do cyklusu For. Avsak vzdy mi to zlyha nakolko neviem ci je mozne oznacit dany Combo Boc indexom cyklusu????
V Exceli sú "by default" dva typy ComboBoxov - Form a Control (ActiveX),
s každým treba manipulovať inak,
viď príklad:
http://pc-prog.eu/phpBB3/images/xMyKB/ComboBox_test.xls
ActiveX Combo: -
Set ObjCombo = ActiveSheet.OLEObjects
Kód:
Public Sub CheckActiveXCombos()
Dim xValue
Dim ObjCombo, oCmb
On Error GoTo errHndl
Set ObjCombo = ActiveSheet.OLEObjects
For Each oCmb In ObjCombo
If TypeOf oCmb.Object Is MSForms.ComboBox Then
xValue = oCmb.Object.Text
If xValue <> "" Then
x_MsgTxt = x_MsgTxt & oCmb.Name & ":" & xValue & vbCrLf
End If
End If
Next oCmb
If x_MsgTxt <> "" Then
MsgBox x_MsgTxt, vbOKOnly, "NEPRAZDNE"
End If
errRes:
Set oCmb = Nothing
Set ObjCombo = Nothing
Exit Sub
errHndl:
MsgBox Err.Description
GoTo errRes
End Sub
Form COMBO: -
Set x_Drps = ActiveWorkbook.Worksheets(ActiveSheet.Name).DropDowns Kód:
Sub CheckFormCombos()
Dim x_Drps, x_drp, c, i, xDrpIdx, xDrpValue
Dim x_MsgTxt As String, x_ShNm, x_DrpNm
On Error GoTo xErr
Set x_Drps = ActiveWorkbook.Worksheets(ActiveSheet.Name).DropDowns
For Each x_drp In x_Drps
xDrpIdx = x_drp.Value
xDrpValue = x_drp.List(xDrpIdx)
If xDrpValue <> "" Then
x_MsgTxt = x_MsgTxt & x_drp.Name & ":" & xDrpValue & vbCrLf
End If
Next x_drp
If x_MsgTxt <> "" Then
MsgBox x_MsgTxt, vbOKOnly, "NEPRAZDNE"
End If
xRes:
Set x_Drps = Nothing
Exit Sub
xErr:
MsgBox Err.Description
GoTo xRes
End Sub
