 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007named 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, 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. > Hi, > [quoted text clipped - 14 lines] > > Thanks, Try: .RowSource = "PoolTypes" Personally, I find this more self-documenting: .RowSource = worksheets("sheet9999").Range("PoolTypes").address(external:=true) > Hi, > [quoted text clipped - 14 lines] > > Thanks,  SignatureDave Peterson 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? > Try: > ..RowSource = "PoolTypes" [quoted text clipped - 20 lines] > > > > Thanks, I don't understand. Which one is poorly documented? > 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 [quoted text clipped - 28 lines] > > > > Dave Peterson SignatureDave Peterson 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 > Try: > ..RowSource = "PoolTypes" [quoted text clipped - 20 lines] > > > > 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 > Hi, > [quoted text clipped - 49 lines] > > > > Dave Peterson SignatureDave 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 > Hi, > [quoted text clipped - 49 lines] > > > > Dave Peterson SignatureDave Peterson 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, > Any chance that you renamed that _initialize procedure to something like: > frmPoolTypes_Initialize [quoted text clipped - 56 lines] > > > > > > 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, > [quoted text clipped - 110 lines] > > > > Dave Peterson SignatureDave Peterson 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 > Yep. > [quoted text clipped - 132 lines] > > > > > > 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 [quoted text clipped - 158 lines] > > > > Dave Peterson SignatureDave Peterson 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") > Yep. > [quoted text clipped - 132 lines] > > > > > > Dave Peterson 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. > Yep. > [quoted text clipped - 132 lines] > > > > > > 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. [quoted text clipped - 139 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, Thank you for your help. I FINALLY got it. Sorry to take so long and thank you for your patience. > Try the previous suggestion and don't modify that code. > [quoted text clipped - 141 lines] > > > > > > Dave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.