Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate combo box from worksheet
Hi All,
I am new to excel vba coding. I hope you can help me out. I have created a combo box in Userform. Now I need to populate the data. The data is stored in sheet1 of service.xls file. How can I populate my combo box with the data in the service.xls file? I would use the .AddItem but there are a lot of data. Thanks, Tracktraining -- Learning |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate combo box from worksheet
Maybe...
Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range 'Service.xls has to be open! With Workbooks("service.xls").Worksheets("sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell End Sub tracktraining wrote: Hi All, I am new to excel vba coding. I hope you can help me out. I have created a combo box in Userform. Now I need to populate the data. The data is stored in sheet1 of service.xls file. How can I populate my combo box with the data in the service.xls file? I would use the .AddItem but there are a lot of data. Thanks, Tracktraining -- Learning -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate combo box from worksheet
thanks Dave!
Is there a way to do it without having the service.xls open? thanks again. -- Learning "Dave Peterson" wrote: Maybe... Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range 'Service.xls has to be open! With Workbooks("service.xls").Worksheets("sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell End Sub tracktraining wrote: Hi All, I am new to excel vba coding. I hope you can help me out. I have created a combo box in Userform. Now I need to populate the data. The data is stored in sheet1 of service.xls file. How can I populate my combo box with the data in the service.xls file? I would use the .AddItem but there are a lot of data. Thanks, Tracktraining -- Learning -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate combo box from worksheet
You could use a bunch of formulas in a hidden sheet that point back at the cells
you want brought back. There are other ways, but that seems easiest to me. ======= On the other hand, your code could open the workbook, add the info to the combobox, close the workbook. Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range Dim ServWkbk as workbook application.screenupdating = false 'hide it from the user set servwkbk = nothing on error resume next set servwkbk = workbooks.open(filename:="C:\folder\services.xls", _ readonly:=true) on error goto 0 if servwkbk is nothing then msgbox "file not found" else 'Service.xls has to be open! With servwkbk.worksheets("Sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell end if application.screenupdating = true End Sub (untested, uncompiled. watch for typos!) tracktraining wrote: thanks Dave! Is there a way to do it without having the service.xls open? thanks again. -- Learning "Dave Peterson" wrote: Maybe... Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range 'Service.xls has to be open! With Workbooks("service.xls").Worksheets("sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell End Sub tracktraining wrote: Hi All, I am new to excel vba coding. I hope you can help me out. I have created a combo box in Userform. Now I need to populate the data. The data is stored in sheet1 of service.xls file. How can I populate my combo box with the data in the service.xls file? I would use the .AddItem but there are a lot of data. Thanks, Tracktraining -- Learning -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate combo box from worksheet
And I forgot the close statement in the code!
...all that code servwkbk.close savechanges:=false '<--- added application.screenupdating = true End Sub Dave Peterson wrote: You could use a bunch of formulas in a hidden sheet that point back at the cells you want brought back. There are other ways, but that seems easiest to me. ======= On the other hand, your code could open the workbook, add the info to the combobox, close the workbook. Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range Dim ServWkbk as workbook application.screenupdating = false 'hide it from the user set servwkbk = nothing on error resume next set servwkbk = workbooks.open(filename:="C:\folder\services.xls", _ readonly:=true) on error goto 0 if servwkbk is nothing then msgbox "file not found" else 'Service.xls has to be open! With servwkbk.worksheets("Sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell end if application.screenupdating = true End Sub (untested, uncompiled. watch for typos!) tracktraining wrote: thanks Dave! Is there a way to do it without having the service.xls open? thanks again. -- Learning "Dave Peterson" wrote: Maybe... Option Explicit Private Sub UserForm_Initialize() Dim myRng As Range Dim myCell As Range 'Service.xls has to be open! With Workbooks("service.xls").Worksheets("sheet1") Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With 'can you use the entire range of values? Me.ComboBox1.List = myRng.Value 'or if you wanted to loop For Each myCell In myRng.Cells Me.ComboBox1.AddItem myCell.Value Next myCell 'or if you wanted to check a value 'and add the item from a different column For Each myCell In myRng.Cells If LCase(myCell.Value) = "some value here" Then Me.ComboBox1.AddItem myCell.Offset(0, 1).Value Next myCell End Sub tracktraining wrote: Hi All, I am new to excel vba coding. I hope you can help me out. I have created a combo box in Userform. Now I need to populate the data. The data is stored in sheet1 of service.xls file. How can I populate my combo box with the data in the service.xls file? I would use the .AddItem but there are a lot of data. Thanks, Tracktraining -- Learning -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Populate Combo Box When Opening a Worksheet | Excel Programming | |||
Populate one combo box based on the selection of another combo box | Excel Programming | |||
using a worksheet range to populate a combo box in excel | Excel Programming | |||
Best way to populate worksheet from 2 combo boxes | Excel Worksheet Functions | |||
Populate a combo box from a worksheet with VBA | Excel Programming |