ralf said:
How can I join userForm control with a cell ?
(there is no controlSource property...)
You just have to use control/worksheet events to explicitly update the
controlsource/control.
For instance, assume I have a listbox lbMyBox on userform MyForm, loaded
from the range "myList" that I want to control using a cell you've named
"mySource". In my initialization code, after I load the listbox, I set
the index using something like this in the userform's code module:
Option Explicit
Private Sub UserForm_Initialize()
Dim nIndex As Long
Dim vList As Variant
nIndex = Range("mySource").Value
vList = Range("myList").Value
lbMyBox.List = vList
With lbMyBox
If nIndex > -1 And nIndex < .ListCount Then _
.ListIndex = nIndex
End With
End Sub
Private Sub lbMyBox_Change()
Application.EnableEvents = False
Range("mySource").Value = lbMyBox.ListIndex
Application.EnableEvents = True
End Sub
If mySource will only be updated manually or by the listbox, that's all
you need. If mySource may be updated via code while the userform is
open, you'll need to add this worksheet event code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If MyForm.Visible Then
If Not Intersect(Range("mySource"), Target) Is Nothing Then _
MyForm.lbMyBox.ListIndex = Range("mySource").Value
End If
End Sub
I could put error handling in the Worksheet_Change event to make sure
that the values are within range -1 to lbMyBox.ListCount, but I
generally prefer to handle that in the code that changes the value in
the first place.
