Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003. Data/Validation/Settings - Allow/List: sizing list? | Excel Discussion (Misc queries) | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
How to Create a macro from drop down list (Validation List) in excel | Excel Programming | |||
Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
My Excel drop-down list eliminates from list options chosen. Help | Excel Programming |