named range as rowsource

[Karen53]

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,

 

[Joel]

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.

 

[Dave Peterson]

Try:
..RowSource = "PoolTypes"

Personally, I find this more self-documenting:
..RowSource = worksheets("sheet9999").Range("PoolTypes").address(external:=true)

 

[Joel]

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?

 

[Dave Peterson]

I don't understand.

Which one is poorly documented?

 

[Karen53]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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

 

[Karen53]

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,

 

[Dave Peterson]

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,

 

[Karen53]

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

 

[Dave Peterson]

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

 

[Karen53]

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")

 

[Karen53]

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.

 

[Dave Peterson]

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.

 

[Karen53]

Hi Dave,

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