I'm having a userform which on initialization should select one of the
values in the combo box control. This selection should be based on the
value of particular cell.
For example, if cell (B11) = 20, the selected value in comboBox upon
initializatino should be 20 which is second entry in the complete list
of (10,20,30,40).
Please suggest solution for this.
Appreciate any help.

thanks,
Anand.

Private Sub UserForm_Initialize()
Dim iCt As Integer

iCt = -1
For Each c In Range(ComboBox1.RowSource)
iCt = iCt + 1
If c = Range("B11") Then
ComboBox1.ListIndex = iCt
Exit Sub
End If
Next c

End Sub

The above assumes the RowSource property has been set and the sheet
with the relevant B11 is the ActiveSheet.

Hth,
Merjet

On May 22, 3:53 pm, merjet wrote:
Private Sub UserForm_Initialize()
Dim iCt As Integer

iCt = -1
For Each c In Range(ComboBox1.RowSource)
iCt = iCt + 1
If c = Range("B11") Then
ComboBox1.ListIndex = iCt
Exit Sub
End If
Next c

End Sub

The above assumes the RowSource property has been set and the sheet
with the relevant B11 is the ActiveSheet.

Hth,
Merjet

I have multiple combo boxes to be filled in with such data. In some
cases, the data comes from a defined RowSource. For some it is
populated through some set of cells. How can I achieve same results
with the comboBox populated using addItem()?

Thanks,
Anand.

Hi Anand,

In the Userform module, try something
like:

'===========
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim Res As Variant
Dim arr As Variant

Set WB = ThisWorkbook '<<===CHANGE
Set SH = WB.Sheets("Sheet1") '<<===CHANGE

With SH
Set Rng = .Range("A1:A10") '<<===CHANGE
Set rCell = .Range("B3") '<<===CHANGE
End With

arr = Rng.Value
Res = Application.Match(rCell.Value, arr, 0)

With Me.ComboBox1
.List = arr
If Not IsError(Res) Then
.ListIndex = Res - 1
End If
End With
End Sub
'<<==========



---
Regards.
Norman


"Anand" wrote in message
...
On May 22, 3:53 pm, merjet wrote:
Private Sub UserForm_Initialize()
Dim iCt As Integer

iCt = -1
For Each c In Range(ComboBox1.RowSource)
iCt = iCt + 1
If c = Range("B11") Then
ComboBox1.ListIndex = iCt
Exit Sub
End If
Next c

End Sub

The above assumes the RowSource property has been set and the sheet
with the relevant B11 is the ActiveSheet.

Hth,
Merjet

I have multiple combo boxes to be filled in with such data. In some
cases, the data comes from a defined RowSource. For some it is
populated through some set of cells. How can I achieve same results
with the comboBox populated using addItem()?

Thanks,
Anand.

Modify the loop like this:

For Each c In ComboBox1.List
iCt = iCt + 1
If CInt(c) = Range("B11") Then
ComboBox1.ListIndex = iCt
Exit Sub
End If
Next c

Be careful about types. When I fill the List with numbers, they are
taken as Text.

Hth,
Merjet

On May 22, 4:43 pm, merjet wrote:
Modify the loop like this:

For Each c In ComboBox1.List
iCt = iCt + 1
If CInt(c) = Range("B11") Then
ComboBox1.ListIndex = iCt
Exit Sub
End If
Next c

Be careful about types. When I fill the List with numbers, they are
taken as Text.

Hth,
Merjet

Now I want to create this as function say, prefillComboBox(cbox as
Object, ValRg as Range, matchVal as string).
However, I'm not sure how I can use the range passed through this
function inside the function. the Range() method does not work with
the ValRg parameter.
Appreciate any help.

thanks,
Anand.