Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
I'm using the following code to place a pop-up listbox on the worksheet
whenever the user clicks on a cell in column A. The listbox comes up fine with the correct list. My question is - when the user clicks on the listbox, how do I capture their selection? When a listbox is on a userform, I know I can use "listbox1.value". I haven't been able to figure out how to do this when the listbox is not on a form but is "shape" on the worksheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 1 Then Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) _ .ControlFormat.ListFillRange = "Sheet2!a1:a18" End If End Sub Thanks!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub -- Regards, Tom Ogilvy "Dave H" wrote in message ... I'm using the following code to place a pop-up listbox on the worksheet whenever the user clicks on a cell in column A. The listbox comes up fine with the correct list. My question is - when the user clicks on the listbox, how do I capture their selection? When a listbox is on a userform, I know I can use "listbox1.value". I haven't been able to figure out how to do this when the listbox is not on a form but is "shape" on the worksheet. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveCell.Column = 1 Then Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) _ .ControlFormat.ListFillRange = "Sheet2!a1:a18" End If End Sub Thanks!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
You can set a linked cell to hold the selected item's index:
With Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) .ControlFormat.ListFillRange = "j1:j18" .ControlFormat.LinkedCell = "h1" End With -- Jim Rech Excel MVP "Dave H" wrote in message ... | I'm using the following code to place a pop-up listbox on the worksheet | whenever the user clicks on a cell in column A. The listbox comes up fine | with the correct list. My question is - when the user clicks on the listbox, | how do I capture their selection? When a listbox is on a userform, I know I | can use "listbox1.value". I haven't been able to figure out how to do this | when the listbox is not on a form but is "shape" on the worksheet. | | Private Sub Worksheet_SelectionChange(ByVal Target As Range) | | If ActiveCell.Column = 1 Then | | Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, | 150) _ | .ControlFormat.ListFillRange = "Sheet2!a1:a18" | | End If | | End Sub | | Thanks!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
Just note that the linked cell will display the index to the selected item
in the list rather than the value of the item in the list (as displayed). -- Regards, Tom Ogilvy "Jim Rech" wrote in message ... You can set a linked cell to hold the selected item's index: With Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) .ControlFormat.ListFillRange = "j1:j18" .ControlFormat.LinkedCell = "h1" End With -- Jim Rech Excel MVP "Dave H" wrote in message ... | I'm using the following code to place a pop-up listbox on the worksheet | whenever the user clicks on a cell in column A. The listbox comes up fine | with the correct list. My question is - when the user clicks on the listbox, | how do I capture their selection? When a listbox is on a userform, I know I | can use "listbox1.value". I haven't been able to figure out how to do this | when the listbox is not on a form but is "shape" on the worksheet. | | Private Sub Worksheet_SelectionChange(ByVal Target As Range) | | If ActiveCell.Column = 1 Then | | Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, | 150) _ | .ControlFormat.ListFillRange = "Sheet2!a1:a18" | | End If | | End Sub | | Thanks!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
Jim did say that, so I guess I should have said
As a point of emphasis and as stated by Jim, note . . . In any event, to show the displayed value, you can use the macro I suggested. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Just note that the linked cell will display the index to the selected item in the list rather than the value of the item in the list (as displayed). -- Regards, Tom Ogilvy "Jim Rech" wrote in message ... You can set a linked cell to hold the selected item's index: With Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) .ControlFormat.ListFillRange = "j1:j18" .ControlFormat.LinkedCell = "h1" End With -- Jim Rech Excel MVP "Dave H" wrote in message ... | I'm using the following code to place a pop-up listbox on the worksheet | whenever the user clicks on a cell in column A. The listbox comes up fine | with the correct list. My question is - when the user clicks on the listbox, | how do I capture their selection? When a listbox is on a userform, I know I | can use "listbox1.value". I haven't been able to figure out how to do this | when the listbox is not on a form but is "shape" on the worksheet. | | Private Sub Worksheet_SelectionChange(ByVal Target As Range) | | If ActiveCell.Column = 1 Then | | Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, | 150) _ | .ControlFormat.ListFillRange = "Sheet2!a1:a18" | | End If | | End Sub | | Thanks!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listboxes on worksheets
Thank you gentlemen. Your suggestions work perfectly! I tried for several
hours to find some info on this in VBA Help; that wasn't much help but your answers fixed my problem! Dave "Tom Ogilvy" wrote: Jim did say that, so I guess I should have said As a point of emphasis and as stated by Jim, note . . . In any event, to show the displayed value, you can use the macro I suggested. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Just note that the linked cell will display the index to the selected item in the list rather than the value of the item in the list (as displayed). -- Regards, Tom Ogilvy "Jim Rech" wrote in message ... You can set a linked cell to hold the selected item's index: With Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, 150) .ControlFormat.ListFillRange = "j1:j18" .ControlFormat.LinkedCell = "h1" End With -- Jim Rech Excel MVP "Dave H" wrote in message ... | I'm using the following code to place a pop-up listbox on the worksheet | whenever the user clicks on a cell in column A. The listbox comes up fine | with the correct list. My question is - when the user clicks on the listbox, | how do I capture their selection? When a listbox is on a userform, I know I | can use "listbox1.value". I haven't been able to figure out how to do this | when the listbox is not on a form but is "shape" on the worksheet. | | Private Sub Worksheet_SelectionChange(ByVal Target As Range) | | If ActiveCell.Column = 1 Then | | Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100, | 150) _ | .ControlFormat.ListFillRange = "Sheet2!a1:a18" | | End If | | End Sub | | Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with listboxes | Excel Programming | |||
Listboxes 2 | Excel Programming | |||
listboxes | Excel Programming | |||
Listboxes | Excel Programming | |||
Linked ListBoxes | Excel Programming |