Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo box and .listindex
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo box and .listindex
How are you coding the assignment? Are you assigning it to a cell in the
active sheet, inadvertently. Show us some code, eh? "dirt" wrote in message . .. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo box and .listindex
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" wrote in message ... How are you coding the assignment? Are you assigning it to a cell in the active sheet, inadvertently. Show us some code, eh? "dirt" wrote in message . .. 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo box and .listindex
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 ToolsOptionsEditor. '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 rt" wrote in message ... 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" wrote in message ... How are you coding the assignment? Are you assigning it to a cell in the active sheet, inadvertently. Show us some code, eh? "dirt" wrote in message . .. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform problem (Listindex) | Excel Discussion (Misc queries) | |||
trouble with a listindex value... | Excel Programming | |||
listindex | Excel Programming | |||
ListIndex | Excel Programming | |||
ListIndex = cell value? | Excel Programming |