Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Populating ComboBox Methods
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 237
Default Populating ComboBox Methods
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Populating ComboBox Methods
Isn't that the method you presented in your original post?

for each cell in Worksheets(8).Range("A1:A100"). _
SpecialCells(xlConstants)
Combobox1.AddItem cell.Value
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 237
Default Populating ComboBox Methods
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Populating ComboBox Methods
Does excel automatically know what you want to populate the combobox with
similar to knowing how many sheets in a workbook? No. If you want to
create a defined name that is defined with a dynamic range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 237
Default Populating ComboBox Methods
Thank you.

Works.


-----Original Message-----
Does excel automatically know what you want to populate
the combobox with
similar to knowing how many sheets in a workbook? No.
If you want to
create a defined name that is defined with a dynamic
range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.



.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Populating ComboBox Methods
Yes below is what I used, I just didnt want to have to
create a variable...

Dim rng As Range
With Worksheets(8)
Set rng = .Range("C1:C100")
End With
For Each Cell In rng
If Cell.Value < "" Then
ComboBox23.AddItem Cell.Value
End If
Next



-----Original Message-----
Isn't that the method you presented in your original post?

for each cell in Worksheets(8).Range("A1:A100"). _
SpecialCells(xlConstants)
Combobox1.AddItem cell.Value
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next


.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Populating ComboBox Methods
forgot to take out the loop in the second one:

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

or to use Worksheets(1)

Private Sub UserForm_Initialize()
sNm = Worksheets(1).name
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET('" & sNm & "'!A1,,,COUNTA('" & sNm & "'!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Does excel automatically know what you want to populate the combobox with
similar to knowing how many sheets in a workbook? No. If you want to
create a defined name that is defined with a dynamic range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.





Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 237
Default Populating ComboBox Methods
Why when I use the below code... I get the error Could
not set the rowsource property. Invalid property use.



Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub


-----Original Message-----
forgot to take out the loop in the second one:

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

or to use Worksheets(1)

Private Sub UserForm_Initialize()
sNm = Worksheets(1).name
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET('" & sNm & "'!A1,,,COUNTA('" & sNm & "'!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Does excel automatically know what you want to populate
the combobox with
similar to knowing how many sheets in a workbook? No.
If you want to
create a defined name that is defined with a dynamic
range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a
specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.





.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Populating ComboBox Methods
I can't say. It works fine for me.

--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
Why when I use the below code... I get the error Could
not set the rowsource property. Invalid property use.



Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub


-----Original Message-----
forgot to take out the loop in the second one:

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

or to use Worksheets(1)

Private Sub UserForm_Initialize()
sNm = Worksheets(1).name
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET('" & sNm & "'!A1,,,COUNTA('" & sNm & "'!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Does excel automatically know what you want to populate
the combobox with
similar to knowing how many sheets in a workbook? No.
If you want to
create a defined name that is defined with a dynamic
range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a
specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.





.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Transpose technique not populating ListFillRange of ActiveX combobox JimC[_2_] Excel Discussion (Misc queries) 2 September 6th 08 01:07 PM
Populating Combobox Methods Todd Huttenstine[_2_] Excel Programming 10 January 18th 04 10:19 PM
Populating sheet names in combobox Todd Huttenstine[_2_] Excel Programming 3 December 21st 03 12:11 AM
Combobox populating based on Option Button Todd Huttenstine[_2_] Excel Programming 7 November 9th 03 10:18 PM
populating a combobox on a worksheet Tim Marsh[_2_] Excel Programming 2 November 3rd 03 12:44 PM


All times are GMT +1. The time now is 09:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement