Pb with excel 98 - userForm Control


R

ralf

How can I join userForm control with a cell ?
(there is no controlSource property...)


thanks
 
Ad

Advertisements

J

JE McGimpsey

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top