UserForm ComboBox

[Office_Novice]

I have a pair of comboboxes that i would like to fill using Rowsource. I
need the source of each to be on different sheets that are hidden. I have
about given up. Any help woul be great.

 

[JLGWhiz]

This worked for me. I put the combo boxes on Sheet 1 but it should
work on a userform as well. Just change from Sheet(1) to UserForm1.

Sub hideSh()
ActiveWorkbook.Worksheets(2).Visible = False
ActiveWorkbook.Sheets(3).Visible = False
Sheets(1).ComboBox1.ListFillRange = "Sheet2!b2:b4"
Sheets(1).ComboBox2.ListFillRange = "Sheet3!c2:c4"
End Sub

 

[Dave Peterson]

You could use code:

Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range

With ThisWorkbook.Worksheets("Sheet 1")
Set myRng = .Range("a1:e" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With Me.ComboBox1
.ColumnCount = myRng.Columns.Count
.RowSource = myRng.Address(external:=True)
End With

End Sub


Or you could just type the address into the combobox's rowsource property:
'sheet 1'!a1:E99

 

[Office_Novice]

I Cant Get either of these to work. What am i doing wrong?

 

[Bob Flanagan]

Is myRng being set to what you want? I would use the following instead
(untested)

With ThisWorkbook.Worksheets("Sheet 1")
Set myRng = .Range(.Cells(1,1), .Cells(.Rows.Count,
1).End(xlUp).offset(0,4))
End With

Then check myRng with Msgbox myRng.address

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Dave Peterson]

I don't know.

What is the name of the worksheet that holds the data?

What is the address of the range that holds the data?

What happens when you tried each of them?

 

[Office_Novice]

WorkSheet Name = Cash

Range = The range is dynamic but it is in column A

Nothing happens

 

[Dave Peterson]

What did you use in code?
What did you type into that rowsource manually?

WorkSheet Name = Cash

Range = The range is dynamic but it is in column A

Nothing happens

 

[Office_Novice]

Private Sub UserForm_Initialize()
PayCB.RowSource = "Payee!A2:A456"
CreditCB.RowSource = "Credit!A2:A456"
End Sub


I can get one of these to work. But not both any ideas

 

[Dave Peterson]

I don't see how either of these are dynamic.

And I'd bet that you mistyped one of the worksheet names.

But that's a guess.

I guess you really didn't want the suggestion I made, huh?

Private Sub UserForm_Initialize()
PayCB.RowSource = "Payee!A2:A456"
CreditCB.RowSource = "Credit!A2:A456"
End Sub

I can get one of these to work. But not both any ideas

 

[Office_Novice]

Not to come across as ungreatful, I think y'all are great. I couldnt get
your suggestion to work in my application. I have the ability to add to th
combo box via user input box in vba thus wanting a dynamic range. But i do
appreciate your time. Thanks again.

 

[Dave Peterson]

Depending on what you mean by dynamic range, you may want to use something like:

With ThisWorkbook.Worksheets("Sheet 1")
Set myRng = .Range("a1:e" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

But I'm repeating myself.

Not to come across as ungreatful, I think y'all are great. I couldnt get
your suggestion to work in my application. I have the ability to add to th
combo box via user input box in vba thus wanting a dynamic range. But i do
appreciate your time. Thanks again.