combo box and .listindex

[dirt]

I have a combobox on a user form that I populate using the .additem method.
I want the listindex to be displayed on a worksheet that is hidden. I can
do this by assigning the .listindex property to the sheet and cell.
However, when the user selects an item from the combo box, the .listindex
integer also appears in whichever cell was active (wherever the cursor is in
the active sheet) when the user triggered the userform.

My question, how do I prevent the .listindex from being copied to the active
cell?

TIA,

D

 

[Bob Kilmer]

How are you coding the assignment? Are you assigning it to a cell in the
active sheet, inadvertently. Show us some code, eh?

 

[dirt]

to populate the combobox:

Private Sub UserForm_Activate()
'populate the Category combo box

With ufNewAlbum.cobCategory
.RowSource = ""
For i = 4 To 13
.AddItem Sheets("Inputs").Cells(i, 14)
Next i

End With

End Sub

to transfer the list index value to a specific sheet and cell:

Private Sub cobCategory_Click()
' record user selection
Selection = ufNewAlbum.cobCategory.ListIndex

Sheets("Inputs").Cells(14, 15).Value = Selection

End Sub

to close and reset the listindex:

Private Sub CommandButton2_Click()
'close without update
ufNewAlbum.cobCategory.ListIndex = -1 'clear the reference to music category

ufNewAlbum.Hide 'hide the userform

End Sub

I found that I cannot use a reference to a sheet other than the active sheet to set the combobox properties - such as control source, listindex, etc. Any reference to another sheet (ie:sheets("blahblah").range("moreblah")) doesn't work.

I hope this is clearer, the code I have will work for me despite the apparent limitations on the property assignment, but I need to eliminate the mysterious display of the .listindex value on the active cell!

Thanks again,

Dan

 

[Bob Kilmer]

Don't use "Selection" as a variable. It is an Excel keyword. It is the
active selection - what the user has selected - the active cell usually.

From Help:
"election Property
Returns the selected object in the active window, for an Application object,
and a specified window, for a Wndows object."

---------------------------------------------------------------
Option Explicit
'Sggest: go to Tools>Options>Editor.
'Check "Require Variable Declaration" so
'Option Explicit is on automatically.

'---------------------------------------------------------------
Private Sub UserForm_Activate()
'populate the Category combo box
ufNewAlbum.cobCategory.RowSource = "[Book1.xls]Inputs!$N$4:$N$13"
End Sub
'---------------------------------------------------------------
'to transfer the list index value to a specific sheet and cell:
Private Sub cobCategory_Click()
' record user selection
'(Don't use "Selection" as a variable! It is an Excel keyword)

Dim UserSelection As Integer
UserSelection = ufNewAlbum.cobCategory.ListIndex

Workbooks("Book1.xls").Sheets("Inputs"). _
Cells(14, 15).Value = UserSelection

'or just

Workbooks("Book1.xls").Sheets("Inputs"). _
Cells(14, 15).Value = ufNewAlbum.cobCategory.ListIndex

End Sub
'---------------------------------------------------------------

Regards,
Bob

to populate the combobox:

Private Sub UserForm_Activate()
'populate the Category combo box

With ufNewAlbum.cobCategory
.RowSource = ""
For i = 4 To 13
.AddItem Sheets("Inputs").Cells(i, 14)
Next i

End With

End Sub

to transfer the list index value to a specific sheet and cell:

Private Sub cobCategory_Click()
' record user selection
Selection = ufNewAlbum.cobCategory.ListIndex

Sheets("Inputs").Cells(14, 15).Value = Selection

End Sub

to close and reset the listindex:

Private Sub CommandButton2_Click()
'close without update
ufNewAlbum.cobCategory.ListIndex = -1 'clear the reference to music
category

ufNewAlbum.Hide 'hide the userform

End Sub

I found that I cannot use a reference to a sheet other than the active sheet
to set the combobox properties - such as control source, listindex, etc.
Any reference to another sheet (ie:sheets("blahblah").range("moreblah"))
doesn't work.

I hope this is clearer, the code I have will work for me despite the
apparent limitations on the property assignment, but I need to eliminate the
mysterious display of the .listindex value on the active cell!

Thanks again,

Dan