Excel List Box
 

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

Excel List Box
 

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

" wrote:

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

Excel List Box
 

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

" wrote:

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

Excel List Box
 

On Jan 15, 7:30*pm, JLGWhiz wrote:
I don't know what I was thinking about. *I used bad syntax on the row source.
*Use this modified code as your guideline.



" wrote:
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- Hide quoted text -

- 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

Excel List Box
 

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.

" wrote:

On Jan 15, 7:30 pm, JLGWhiz wrote:
I don't know what I was thinking about. I used bad syntax on the row source.
Use this modified code as your guideline.



" wrote:
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- Hide quoted text -

- 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

Excel List Box
 

On Jan 15, 8:31*pm, JLGWhiz wrote:
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.