Excel List Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

whitethomas12@gmail.com - 16 Jan 2008 00:37 GMT

I need help to create a Macro to fill a list box with names from
another sheet and then when the list box changes to activate another
macro that will display some charts.

So far I only have the following code:

Range("B5").Select
ActiveWorkbook.Names.Add Name:="EmployeeNames", RefersToR1C1:= _
"=Employees!R1C1:R230C1"
ActiveWorkbook.Names.Add Name:="EmployeeNames", RefersToR1C1:= _
"=Employees!R1C1:R230C1"
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=EmployeeNames"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I got it from using th emacro recorder. Now I am stuck

Can someone please help me

Thank you

Reply to this Message

JLGWhiz - 16 Jan 2008 03:25 GMT

Assume the listbox is on Worksheets(1) and the range
with the data is on Worksheets(2).Range("A2:A21"):

Private Sub fillLstBx()
Dim ws1, ws2 As Worksheet
ws1.ListBox1.RowSource = ws2.Range("A2:A21").Address
End Sub

The above code could go into either the worksheet which
has the listbox or delete the private from the title line
and put it in the VBA module.

The following code would go into the listbox click event.
Right click the control and click view code to open the code
window. myMacro would be the name of the macro you want to
call.

Private Sub ListBox1_Click()
myMacro
End Sub

> I need help to create a Macro to fill a list box with names from
> another sheet and then when the list box changes to activate another
[quoted text clipped - 27 lines]
>
> Thank you

Reply to this Message

JLGWhiz - 16 Jan 2008 03:30 GMT

I don't know what I was thinking about. I used bad syntax on the row source.
Use this modified code as your guideline.

> I need help to create a Macro to fill a list box with names from
> another sheet and then when the list box changes to activate another
[quoted text clipped - 27 lines]
>
> Thank you

Reply to this Message

whitethomas12@gmail.com - 16 Jan 2008 03:49 GMT

> I don't know what I was thinking about. I used bad syntax on the row source.
> Use this modified code as your guideline.
[quoted text clipped - 33 lines]
>
> - Show quoted text -

OK, I found the answer. I didn't know the difference between a
listbox and a validation list; I got them confused. My code above was
for creating a validation list.

Below is my code to run another macro upon selection change.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range) ',

With Sh
If .Name = "Sheet5" Then

If Target.Address = "$B$3" Then
'MsgBox Target.Value
Run "test_listbox2"
End If
End If
End With
End Sub

This was a bit tricky being that Workbook_SheetChange by default works
on all sheets.

Thank you for your reply and your help

Reply to this Message

JLGWhiz - 16 Jan 2008 04:31 GMT

This was a bit tricky being that Workbook_SheetChange by default works

> on all sheets.

Then why not use Workbook_Change for just the sheet the code is in?
You don't have to use the SheetChange just because it pops up on the screen.
You can delete it and click the declarations window to select another choice
from the drop down list.

> > I don't know what I was thinking about. I used bad syntax on the row source.
> > Use this modified code as your guideline.
[quoted text clipped - 58 lines]
>
> Thank you for your reply and your help

Reply to this Message

whitethomas12@gmail.com - 16 Jan 2008 15:29 GMT

> This was a bit tricky being that Workbook_SheetChange by default works
>
[quoted text clipped - 70 lines]
>
> - Show quoted text -

I think I understand what you are saying. How I coded the
Workbook_SheetChange will only work on Sheet5. The tricky part was to
find out how I could make it work on only my selected sheet and not
all sheets.

Reply to this Message