Row source question.

H

Hydra

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub





Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
 
D

Dave Peterson

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?
 
H

Hydra

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.
 
D

Dave Peterson

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.
 
H

Hydra

Well, if I add a listbox to the form, with the same row source, then it works.

I deleted the combo box and put in a new one with a different name, sme row
source, same blank result.
 
H

Hydra

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.
 
D

Dave Peterson

Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.
Click to expand...
 
H

Hydra

OK Brand new workbook, copied in ONLY the old data, then renamed the tabs to
match those in the old workbook.

Created a brand new userform and put a listbox and combobox on it.

Then I copied in my code to initialize the form:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myRange As Range
Set ws = Worksheets("IMp")
Set myRange = ws.Range("b1", "b30")
'MsgBox Worksheets("Imp").Range("d1:d32").Address(external:=True)

'ListBox1.RowSource = "Imp!$d$1:$d$32"
'ComboBox1.RowSource = "Imp!$d$1:$d$32"

ListBox1.RowSource = "MajorEvents!$b$1:$b$32"
ComboBox1.RowSource = "MajorEvents!$b$1:$b$32"

'Either set of Row sources works for the listbox but not for the combobox.


End Sub


Grrrr.





Dave Peterson said:
Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

Dave Peterson said:
Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

You've commented out the range from the Imp worksheet.
You haven't used the syntax I suggested.
You haven't said what's in those cells.

This worked fine for me:

Option Explicit

Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
Set myRng1 = .Range("A1:A30")
Set myRng2 = .Range("b1:B30")
End With

With myRng1
.Formula = "=cell(""address"",a1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)

End Sub



OK Brand new workbook, copied in ONLY the old data, then renamed the tabs to
match those in the old workbook.

Created a brand new userform and put a listbox and combobox on it.

Then I copied in my code to initialize the form:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myRange As Range
Set ws = Worksheets("IMp")
Set myRange = ws.Range("b1", "b30")
'MsgBox Worksheets("Imp").Range("d1:d32").Address(external:=True)

'ListBox1.RowSource = "Imp!$d$1:$d$32"
'ComboBox1.RowSource = "Imp!$d$1:$d$32"

ListBox1.RowSource = "MajorEvents!$b$1:$b$32"
ComboBox1.RowSource = "MajorEvents!$b$1:$b$32"

'Either set of Row sources works for the listbox but not for the combobox.


End Sub

Grrrr.

Dave Peterson said:
Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
H

Hydra

AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????



Dave Peterson said:
Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

Dave Peterson said:
Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
H

Hydra

I got the same result on both worksheets. I used the Imp workheet first
because it is the Active sheet, but I really want the data that is on the
MajorEvent sheet. when I tried to access that previously, it threw an error
saying it could not set the rowsource, if I used that sheet, but the imp
sheet worked. now they both work the same.

For this test the data is numbers 1 through 30.
If I get this to work it will be short text strings.



I don't understand what this does:

Set wks = Workbooks.Add(1).Worksheets(1)

It looks like it adds a worksheet, which i don't need.
Should this be Set wks = Worksheets("MajorEvents") ??

So I interpreted it as Set wks = workbooks("book2").Workheeets("MajorEvents)

So now I have:


Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks("Book2.xls").Worksheets("MajorEvents")

With wks
Set myRng1 = .Range("b1:b30")
Set myRng2 = .Range("b1:B30")
End With

'both control boxes are looking at the same data.

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)


End Sub

This results in a list box that says
$b$1
$b$2
$b$3
$b$4....

And when I look at the woorksheet, the data in th cells appears to have been
replaced with the cell addresses. The list box is reporting correctly what is
showing in the cells.

The combo box appears blank until you hit the down arrow key and then it
displays
$b$1
(blank)
(blanK) ...etc although there are no "rows" to access, just the space where
the rows should be.

Then if I hit down arrow again, the $b$1nges to $b$2 expected, since it is
the next data.

Is there some property that needs to be set for number of rows visible, or
something? What hppened to my test data? why is it now showing the cell
addresses?

What is the purpose of:

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With


???? Is this a test to see if it is looking in the right place?




Dave Peterson said:
You've commented out the range from the Imp worksheet.
You haven't used the syntax I suggested.
You haven't said what's in those cells.

This worked fine for me:

Option Explicit

Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
Set myRng1 = .Range("A1:A30")
Set myRng2 = .Range("b1:B30")
End With

With myRng1
.Formula = "=cell(""address"",a1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)

End Sub



OK Brand new workbook, copied in ONLY the old data, then renamed the tabs to
match those in the old workbook.

Created a brand new userform and put a listbox and combobox on it.

Then I copied in my code to initialize the form:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myRange As Range
Set ws = Worksheets("IMp")
Set myRange = ws.Range("b1", "b30")
'MsgBox Worksheets("Imp").Range("d1:d32").Address(external:=True)

'ListBox1.RowSource = "Imp!$d$1:$d$32"
'ComboBox1.RowSource = "Imp!$d$1:$d$32"

ListBox1.RowSource = "MajorEvents!$b$1:$b$32"
ComboBox1.RowSource = "MajorEvents!$b$1:$b$32"

'Either set of Row sources works for the listbox but not for the combobox.


End Sub

Grrrr.

Dave Peterson said:
Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

The code was just sample code to see if it worked.

Set wks = Workbooks.Add(1).Worksheets(1)
creates a new workbook with a single worksheet. Then uses that single
worksheet. It doesn't touch the existing workbook.

It fills a couple of ranges with formulas and converts them to values.


I got the same result on both worksheets. I used the Imp workheet first
because it is the Active sheet, but I really want the data that is on the
MajorEvent sheet. when I tried to access that previously, it threw an error
saying it could not set the rowsource, if I used that sheet, but the imp
sheet worked. now they both work the same.

For this test the data is numbers 1 through 30.
If I get this to work it will be short text strings.

I don't understand what this does:

Set wks = Workbooks.Add(1).Worksheets(1)

It looks like it adds a worksheet, which i don't need.
Should this be Set wks = Worksheets("MajorEvents") ??

So I interpreted it as Set wks = workbooks("book2").Workheeets("MajorEvents)

So now I have:

Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks("Book2.xls").Worksheets("MajorEvents")

With wks
Set myRng1 = .Range("b1:b30")
Set myRng2 = .Range("b1:B30")
End With

'both control boxes are looking at the same data.

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)


End Sub

This results in a list box that says
$b$1
$b$2
$b$3
$b$4....

And when I look at the woorksheet, the data in th cells appears to have been
replaced with the cell addresses. The list box is reporting correctly what is
showing in the cells.

The combo box appears blank until you hit the down arrow key and then it
displays
$b$1
(blank)
(blanK) ...etc although there are no "rows" to access, just the space where
the rows should be.

Then if I hit down arrow again, the $b$1nges to $b$2 expected, since it is
the next data.

Is there some property that needs to be set for number of rows visible, or
something? What hppened to my test data? why is it now showing the cell
addresses?

What is the purpose of:

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

???? Is this a test to see if it is looking in the right place?

Dave Peterson said:
You've commented out the range from the Imp worksheet.
You haven't used the syntax I suggested.
You haven't said what's in those cells.

This worked fine for me:

Option Explicit

Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
Set myRng1 = .Range("A1:A30")
Set myRng2 = .Range("b1:B30")
End With

With myRng1
.Formula = "=cell(""address"",a1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)

End Sub



OK Brand new workbook, copied in ONLY the old data, then renamed the tabs to
match those in the old workbook.

Created a brand new userform and put a listbox and combobox on it.

Then I copied in my code to initialize the form:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myRange As Range
Set ws = Worksheets("IMp")
Set myRange = ws.Range("b1", "b30")
'MsgBox Worksheets("Imp").Range("d1:d32").Address(external:=True)

'ListBox1.RowSource = "Imp!$d$1:$d$32"
'ComboBox1.RowSource = "Imp!$d$1:$d$32"

ListBox1.RowSource = "MajorEvents!$b$1:$b$32"
ComboBox1.RowSource = "MajorEvents!$b$1:$b$32"

'Either set of Row sources works for the listbox but not for the combobox.


End Sub

Grrrr.

:

Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Check to see if your range has hidden rows.

If so, unhide the rows and you'll see those empty cells.
AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????

Dave Peterson said:
Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

ps.

Try that code to see if worked and report back.

Dave said:
The code was just sample code to see if it worked.

Set wks = Workbooks.Add(1).Worksheets(1)
creates a new workbook with a single worksheet. Then uses that single
worksheet. It doesn't touch the existing workbook.

It fills a couple of ranges with formulas and converts them to values.
I got the same result on both worksheets. I used the Imp workheet first
because it is the Active sheet, but I really want the data that is on the
MajorEvent sheet. when I tried to access that previously, it threw an error
saying it could not set the rowsource, if I used that sheet, but the imp
sheet worked. now they both work the same.

For this test the data is numbers 1 through 30.
If I get this to work it will be short text strings.

I don't understand what this does:

Set wks = Workbooks.Add(1).Worksheets(1)

It looks like it adds a worksheet, which i don't need.
Should this be Set wks = Worksheets("MajorEvents") ??

So I interpreted it as Set wks = workbooks("book2").Workheeets("MajorEvents)

So now I have:

Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks("Book2.xls").Worksheets("MajorEvents")

With wks
Set myRng1 = .Range("b1:b30")
Set myRng2 = .Range("b1:B30")
End With

'both control boxes are looking at the same data.

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)


End Sub

This results in a list box that says
$b$1
$b$2
$b$3
$b$4....

And when I look at the woorksheet, the data in th cells appears to have been
replaced with the cell addresses. The list box is reporting correctly what is
showing in the cells.

The combo box appears blank until you hit the down arrow key and then it
displays
$b$1
(blank)
(blanK) ...etc although there are no "rows" to access, just the space where
the rows should be.

Then if I hit down arrow again, the $b$1nges to $b$2 expected, since it is
the next data.

Is there some property that needs to be set for number of rows visible, or
something? What hppened to my test data? why is it now showing the cell
addresses?

What is the purpose of:

With myRng1
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

???? Is this a test to see if it is looking in the right place?

Dave Peterson said:
You've commented out the range from the Imp worksheet.
You haven't used the syntax I suggested.
You haven't said what's in those cells.

This worked fine for me:

Option Explicit

Private Sub UserForm_Initialize()

Dim wks As Worksheet
Dim myRng1 As Range
Dim myRng2 As Range

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
Set myRng1 = .Range("A1:A30")
Set myRng2 = .Range("b1:B30")
End With

With myRng1
.Formula = "=cell(""address"",a1)"
.Value = .Value
End With

With myRng2
.Formula = "=cell(""address"",b1)"
.Value = .Value
End With

Me.ComboBox1.RowSource = myRng1.Address(external:=True)
Me.ListBox1.RowSource = myRng2.Address(external:=True)

End Sub




Hydra wrote:

OK Brand new workbook, copied in ONLY the old data, then renamed the tabs to
match those in the old workbook.

Created a brand new userform and put a listbox and combobox on it.

Then I copied in my code to initialize the form:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim myRange As Range
Set ws = Worksheets("IMp")
Set myRange = ws.Range("b1", "b30")
'MsgBox Worksheets("Imp").Range("d1:d32").Address(external:=True)

'ListBox1.RowSource = "Imp!$d$1:$d$32"
'ComboBox1.RowSource = "Imp!$d$1:$d$32"

ListBox1.RowSource = "MajorEvents!$b$1:$b$32"
ComboBox1.RowSource = "MajorEvents!$b$1:$b$32"

'Either set of Row sources works for the listbox but not for the combobox.


End Sub

Grrrr.

:

Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
H

Hydra

The thing may have been working all along. The row width was set to zero.
Why would the default value for a control be set to invisible?

Anyway, what I really wanted was a list box with multiselect set to extended.
But, Id like to allow th euser to add to the list, as in a combobox.

Is there something like multiselect for the combobox?



Hydra said:
AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????



Dave Peterson said:
Try using the other syntax in a brand new test workbook.
A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I don't understand what you mean by the default value for a control being set to
invisible. In a combobox, there is no default choice. So there's nothing to
display.

If you want to select one of the items, check one of the earlier posts. There's
code in there to set the value.

You could do something that would retrieve the first selection from the
combobox, and then allow the user to choose again (and retrieve that) and then
the third...but that sounds nutty to me. Why not just use a listbox?


The thing may have been working all along. The row width was set to zero.
Why would the default value for a control be set to invisible?

Anyway, what I really wanted was a list box with multiselect set to extended.
But, Id like to allow th euser to add to the list, as in a combobox.

Is there something like multiselect for the combobox?

Hydra said:
AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????



Dave Peterson said:
Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
H

Hydra

For some reason, when I selected the combobox tool and put in the combo box,
the list width property was set to zero, therefore the list was invisible
when I tried to use the box. If I create a new combo box the list width
defaults to zero.


Dave Peterson said:
I don't understand what you mean by the default value for a control being set to
invisible. In a combobox, there is no default choice. So there's nothing to
display.

If you want to select one of the items, check one of the earlier posts. There's
code in there to set the value.

You could do something that would retrieve the first selection from the
combobox, and then allow the user to choose again (and retrieve that) and then
the third...but that sounds nutty to me. Why not just use a listbox?


The thing may have been working all along. The row width was set to zero.
Why would the default value for a control be set to invisible?

Anyway, what I really wanted was a list box with multiselect set to extended.
But, Id like to allow th euser to add to the list, as in a combobox.

Is there something like multiselect for the combobox?

Hydra said:
AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????



:

Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I've never seen excel do this.

But thanks for reporting it back.
For some reason, when I selected the combobox tool and put in the combo box,
the list width property was set to zero, therefore the list was invisible
when I tried to use the box. If I create a new combo box the list width
defaults to zero.

Dave Peterson said:
I don't understand what you mean by the default value for a control being set to
invisible. In a combobox, there is no default choice. So there's nothing to
display.

If you want to select one of the items, check one of the earlier posts. There's
code in there to set the value.

You could do something that would retrieve the first selection from the
combobox, and then allow the user to choose again (and retrieve that) and then
the third...but that sounds nutty to me. Why not just use a listbox?


The thing may have been working all along. The row width was set to zero.
Why would the default value for a control be set to invisible?

Anyway, what I really wanted was a list box with multiselect set to extended.
But, Id like to allow th euser to add to the list, as in a combobox.

Is there something like multiselect for the combobox?

:

AHA!

When I apply the code to the list box,i get what I would expect: A list
showing twelve or so items and a scroll bar to see the rest.

But the combo box appears empty. If I click in the box, there is a blinking
entry cursor where the top row should be, but the combo box does not respond
to any mouse clicks.

However, if I hit the down arrow button then the first entry in the list
appears. If I keep hitting the down arrow button, each successive entry
appears where the first row should be.

?????



:

Try using the other syntax in a brand new test workbook.

Hydra wrote:

A listbox with listbox1.rowsource = "[Impbuilder]!Imp!$d$1:$d$32"

works OK

But if I use listbox1.rowsource = "[Impbuilder]!MajorEvents!$d$1:$d$32"

It thows an error.

The combo box won't work with either one.
Deleted the combobox and put in a new one, same result.

:

Try this:

msgbox worksheets("Imp").range("d1:d32").address(external:=true)

You'll see that it includes the workbook name, worksheet name and the cell
address.

I'm not sure what full screen is, but if that means it looks like you could fit
32 entries in that area, then I'd look back at the worksheet and check to see
what's in that range. Maybe you're off by a column.

Hydra wrote:

None of them have worked for me.

I get a form with a command button to close the form
I get a control for the combo box, with a cursor in the box, but no data.
the combo box has a scroll bar, but it goes full screen, there being nothing
to scroll to.

What Does ----- address(external:=true) ----Do?

What i really want is

Me.ctrlMajorEventList.RowSource = "'Major Event'!$d$1:$d$32" where major
event is a different sheet.

Does that mean I ahave to define ws as "Major Event" instead of "Imp" which
is the active sheet??

Closing and reopening excel has no effect. I have trid different ranges
with different kinds of data. I cannot assign the row source directly in the
properties table: that throws an error.

:

Your first line of code worked fine for me:

Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32"

I like this syntax better:
Me.ctrlMajorEventList.RowSource _
= worksheets("Imp").range("d1:d32").address(external:=true)

But that's just my preference--your code worked fine for me.


When you say that the combobox was blank, do you mean the value of the combobox
was blank--or that the dropdown showed nothing (an empty list)?

If you wanted to show a choice in that combobox, you could add:
With Me.ctrlmajorEventList
.RowSource = Worksheets("imp").Range("D1:d32").Address(external:=True)
.ListIndex = 0 'or 0 to 31 (0 based)
End With

But I bet you didn't mean that.

When you use the dropdown arrow, do you see a very short dropdown area--like
there's nothing assigned to the combobox?

Or do you see a dropdown that looks like it would support 32 entries?

If you see the first, then something bad is happening (I don't have a clue).

But if you see the second, I'd check to make sure that D1:D32 on Imp actually
has data in it.

ps. And one more thing to make sure it's not a pc display problem.

If you close excel, then reopen it, then open your workbook and show the
userform, does it appear ok?






Hydra wrote:

I have a user form that contains a combo box.

The rowsource is a named range in Excel. If I put the named range in the
properties box of the combobox on the form, the form and combobox control
appears when called but the combo box is blank.

The row souce is on a different worksheet inthe same workbook from the sheet
that is active when the form is called. I have tried renaming the rowsource
when the form is initialized as follows (Iterations commented out).

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Set ws = Worksheets("Imp")
'Me.ctrlMajorEventList.RowSource = "Imp!$d$1:$d$32" (shows blank data
against current weeksheet, where data exists.
'Me.ctrlMajorEventList.RowSource = MajorEventList (shows Blank data, major
eventlist is a rangename)
'Me.ctrlmajorEventList.Rowsource = "''Major events'!b1:b30" ) (Shows blank
data agianst a separte sheet where data exists)

End Sub

Whether I use:
Me.ctrlEventList.RowSource = MajorEventList

or

Me.ctrlEventList.RowSource = "'Major Events'!b1:b30"

I get no error message, but I also get no data showing in the form.

I call the form using:

frmSelectMajorEvent.Show

When it opens, it should initialize itself, right? Or do I need to open the
form and also call the initalize routine to initialize the data source?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Userform ComboBox Row Source 1
Blank item in listbox, how to remove 3
UserForm - pointing control source to new sheet - long delay 5
list box row source 1
listbox inconsistencies 7
Combobox strange behaviour 0
Datasheet combo box empty 1
UserForm Question 5

Top