Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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. "Karen53" wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
Try:
..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
I don't understand.
Which one is poorly documented? Joel wrote: 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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
Hi,
Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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 Karen53 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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" wrote: 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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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. Karen53 wrote: 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" wrote: 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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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") "Dave Peterson" wrote: 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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
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" wrote: 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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
named range as rowsource
Try the previous suggestion and don't modify that code.
Karen53 wrote: 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" wrote: 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(ext ernal:=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. Karen53 wrote: 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(ext ernal:=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" wrote: 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 wrote: Hi, Thanks to you both for your replies. I have tried these: With frmPoolTypes.lstPoolList .RowSource = Worksheets("Table").Range("PoolTypes").Address(ext ernal:=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" wrote: Try: ..RowSource = "PoolTypes" Personally, I find this more self-documenting: ..RowSource = worksheets("sheet9999").Range("PoolTypes").address (external:=true) Karen53 wrote: 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, -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.RowSource by row range | Excel Programming | |||
ListBox RowSource set to range of in-active sheet - possible? | Excel Programming | |||
range name as rowsource of listbox? | Excel Programming | |||
Forms - Named Range as the RowSource | Excel Programming | |||
Dynamic range not resized for RowSource usage | Excel Programming |