named range as rowsource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a listbox that I am trying to set the rowsource with VBA. If I set
the rowsource manually in the properties window to mynameedrange, my code
works great. I'm having trouble with the code setting it with VBA.

Here's what I have:

With frmPoolTypes.lstPoolList
.RowSource = Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

What am I doing wrong?

Thanks,
 
When I get an error I try to break the problem into pieces. I defined a name
in the spreadsheet then tried the statement below and it failed

Set a = Range("PoolTypes")

Then I tried the code below and it worked.

Set a = ActiveSheet.Range("PoolTypes")

or

Set a = Sheets("Sheet1").Range("PoolTypes")


There are a lot of functions and metods that I do not have memorized. I
thought name in VBA needed a worksheet reference, but wasn't sure. So I ran
a test to be sure before giving you advice.
 
Try:
..RowSource = "PoolTypes"

Personally, I find this more self-documenting:
..RowSource = worksheets("sheet9999").Range("PoolTypes").address(external:=true)
 
Dave: I don't recommend giving poorly documented microsoft internal tricks is
wise to people who barely know how to program VBA. What will they do when
they look at the code in a month and have no idea what the code really does?
 
Hi,

Thanks to you both for your replies.

I have tried these:

With frmPoolTypes.lstPoolList
.RowSource =
Worksheets("Table").Range("PoolTypes").Address(external:=True)
and I tried
.RowSource = "PoolTypes"
and I tried
.RowSource = Sheets("Table").Range("PoolTypes")
End With

The form comes up but with no data. I get no error messages.

I have double checked the names of my form, listbox, sheet and range.

I can set the caption for the form ok, but I'm not getting the data range.

What else could I be missing?

Thanks
 
The only thing I (don't) see is where you put this code.

Is it in the userform_initialization procedure?

All 3 of these worked ok for me:

Option Explicit
Private Sub UserForm_Initialize()
Me.lstPoolList.RowSource = "PoolTypes"
Me.lstPoolList.RowSource = "'Table'!PoolTypes"
Me.lstPoolList.RowSource = ThisWorkbook.Worksheets("table") _
.Range("Pooltypes").Address(external:=True)
End Sub
 
Any chance that you renamed that _initialize procedure to something like:
frmPoolTypes_Initialize
????

If you did, name it back to what excel expects it to be.
UserForm_Initialize
 
Hi Dave,

Here is my complete code for the form. This resides in the forms module.
I have the new value being obtained by a textbox when they click 'Save'.


Sub frmPoolTypes_Initialize()

Load frmPoolTypes

Me.Caption = "Pool Types"

With Me.lstPoolList
'.RowSource =
Worksheets("Table").Range("PoolTypes").Address(external:=True)
'.RowSource = "PoolTypes"
.RowSource = Sheets("Table").Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

frmPoolTypes.Show

End Sub



Private Sub cmdSave_Click()

Dim Choice As Long
Dim NewPool As String

Choice = lstPoolList.ListIndex

NewPool = txtNewPool

Range("PoolTypes").Item(Choice, 1).Value = NewPool

Unload frmPoolTypes

End Sub


Private Sub cmdCancel_Click()

Unload frmPoolTypes

End Sub


Do you see anything I've missed?

Thanks,
 
Yep.

You didn't use the correct procedure name.

Sub Userform_Initialize()

Me.Caption = "Pool Types"

With Me.lstPoolList
.RowSource _
= Worksheets("Table").Range("PoolTypes").Address(external:=True)
'.RowSource = "PoolTypes"
'.RowSource = Sheets("Table").Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

End Sub

I didn't look at the other routines.
Hi Dave,

Here is my complete code for the form. This resides in the forms module.
I have the new value being obtained by a textbox when they click 'Save'.

Sub frmPoolTypes_Initialize()

Load frmPoolTypes

Me.Caption = "Pool Types"

With Me.lstPoolList
'.RowSource =
Worksheets("Table").Range("PoolTypes").Address(external:=True)
'.RowSource = "PoolTypes"
.RowSource = Sheets("Table").Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

frmPoolTypes.Show

End Sub

Private Sub cmdSave_Click()

Dim Choice As Long
Dim NewPool As String

Choice = lstPoolList.ListIndex

NewPool = txtNewPool

Range("PoolTypes").Item(Choice, 1).Value = NewPool

Unload frmPoolTypes

End Sub

Private Sub cmdCancel_Click()

Unload frmPoolTypes

End Sub

Do you see anything I've missed?

Thanks,
Click to expand...
 
Thanks Dave. I've corrected that but I'm getting a type mismatch error
message but nothing is highlighting in yellow. I've double checked the anmes
of the userform, listbox, and named range in this procedure. I don't see
anything.

Sub Userform_Initialize()

Load frmPoolTypes

Me.Caption = "Pool Types"

With Me.lstPoolList
.RowSource = Sheets("Table").Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

frmPoolTypes.Show

End Sub
 
Try the suggestion that I posted in the last response.

I removed the "load", "show" lines for a reason.
You also changed the .rowsource line from what I suggested.


Thanks Dave. I've corrected that but I'm getting a type mismatch error
message but nothing is highlighting in yellow. I've double checked the anmes
of the userform, listbox, and named range in this procedure. I don't see
anything.

Sub Userform_Initialize()

Load frmPoolTypes

Me.Caption = "Pool Types"

With Me.lstPoolList
.RowSource = Sheets("Table").Range("PoolTypes")
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With

frmPoolTypes.Show

End Sub
Click to expand...
 
I've isolated it to my rowsource statement. "Table" is the name of the sheet
and "PoolTypes" is my named range. I don't get it.


..RowSource = Sheets("Table").Range("PoolTypes")
 
Ok, I took out the Sheets("Table") and left just the "PoolTypes" and it
works. What's wrong with my Sheets? That is the name of the sheet.
 
Try the previous suggestion and don't modify that code.
Ok, I took out the Sheets("Table") and left just the "PoolTypes" and it
works. What's wrong with my Sheets? That is the name of the sheet.
Click to expand...
 
Hi Dave,

Thank you for your help. I FINALLY got it. Sorry to take so long and thank
you for your patience.
 

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

RowSource 4
Combobox Rowsource 2
VBA Addin and range names 4
listbox 1
ListBox RowSource Property 2
setting rowsource 1
Changed variable value 6
Public Const & RowSource 4

Back
Top