Solved: Referencing a named range for a UserForm?

[infinity]

Hey all!

I have searched on VBAExpress for something that would solve my problem and could not find anything. I am trying to do a couple of different things with a UserForm.

1. I want either a list box or combo box that will get its contents from a range that is located on another sheet in the same workbook. This list is a single column but will change in length fairly regularly through code I have already written in another module which is working perfectly. I was trying to set the RowSource property but I cannot get it to work and take into account when the list changes. I figured it may be better to reference it with a named range but I do not know how to pass the named range to the list box or combo box. What this list box/combo box will do is search for a match on the sheet that calls the user form then move to that cell.

2. On the same user form there will be 3 - 4 text boxes which will pass new data into the cells once the "Enter" button is pushed. I know how to pull up a blank userform and pass the entrys in the userform to cells but what I would like is once the list box/combo box moves to the cell it searched for I would like the text boxes on the user form to populate with any values that are currently in those cells (if any) or blank if no values exist which can then be overwritten with new values to be passed to the cells. Any suggestions? :banghead:

[mdmackillop]

1. Use a dynamic range name eg
Name: MyData Formula: =OFFSET(data!$A$2,0,0,COUNTA(data!$A:$A)-1,1)
Use MyData as the rowsource for you combo.

[Norie]

Let's start with your first question.

I asked in the other thread how/when you are populating the listbox but you never answered.

You should easily be able to use a named range for that.

Option Explicit

Private Sub UserForm_Initialize()

Worksheets("Sheet2").Range("A1:A5").Name = "DataList"

ListBox1.RowSource = "DataList"

End Sub
Obviously the range is hard-coded here but that can easily be changed to take in to account the changing length of the data.

Once this part is sorted then you can start thinking about the 2nd part.:)

[mikerickson]

To pass data to a Useform, I use the userform's .Tag property.
Sub showUF()
With UserForm1
.Tag = "myNamedRange"
.Show
End With
End Subwith this in the userforms Activate event.Private Sub UserForm_Activate()
With Me
On Error Resume Next
.ListBox1.List = Range(.Tag).Value
On Error GoTo 0
End With
End SubThe Initialize event runs before the line .Tag = "myNamedRange" is excecuted so the Activate routine has to be used.

[infinity]

I am still having problems, I have attached a sample workbook showing exactly what I am trying to accomplish hopefully this will clear things up. Please forgive my ignorance about UserForms, I am about 75% self taught in VBA and 25% VBax taught from all you wonderful professional coders out there that sacrafice your time and knowledge to help people like me, and in all honesty, UserForms are very new to me.

If you pull up the UserForm from the button "Show User Form" this is the form I am trying to use for this project. The ComboBox should be pulling its data from the tab "Creditors", which is a named range called "Creditors" in my original workbook, this list will change with each bill that is added or deleted so it will need to pull the current list no matter how long it is.

Once the user selects a bill from the list, the code will find that match from column B and go to that cell reference. After it finds the users choice the first 2 TextBoxes "New Bill" and "Due Date") will always be empty because if there was a value in either of those two cells (ex, $F29 & $F30 or $F48 & $F49 etc. based on the users selection) it will have already been moved to the past due area of that bill so at this point there should be no value in those cells. The text boxes on the UserForm that would need to be populated, if there is a value in these cells is "Past Due Bill" and "Cancellation Date", these would need to be populated with the values that are in the cells. I hope this makes sense, if not I will see if I can clarify more. Thank you so much for your time, much appreciated.

[Norie]

infinity

There is no attachment.:)

Is the list of data contiguous? ie no spaces between creditors.

If it is it would be a good thing because it will be easy to locate the data for each creditor using the Listbox's ListIndex property - see attached.

Also when I reread your latest post it appears you are storing data in rows not columns, not a good idea.

mike

Why the extra step using Tag?

[mdmackillop]

To pass data to a Useform, I use the userform's .Tag property.

A useful trick Mike. I'll remember that. For more than one value, I suppose you could pass a list and use Split to extract them.
Regards
Malcolm

[mikerickson]

mike

Why the extra step using Tag?Its a technique for passing any variable to a UF (as a string). If infinity is using the same UF for different named ranges, something like this would be useful. If the listbox uses only one named range, hardcoding it into the Intialize event would be better.

[infinity]

I do not know why my attachment did not go across. To answer your question Norie, no the data I am searching for is not contiguous but it is all in column B and it will be unique in that column. In other words if I am looking for "My Next Bill" there will only be one cell in column B that will have that value. I am sure that once my ComboBox finds the users selection the other TextBoxes on the userform can be populated with a column and row offset... at least that is what I am thinking, unfortunately I do not know how to populate these fields. I am going to try and re-attach the sample file. Thank you for your time, much appreciated!!!

Scott.

P.S. I have tried again to post the attachment but it keeps failing. It says "You MAY post attachments" but for some reason it will not attach. The file is 4.61MB, is that too large??? Please advise.

[infinity]

I see on the file attachment window that there is a 1mb limit to .xls files to attach. I have stripped this workbook of anything that is not necessary to convey my situation to you it is still 4.59mb. I have no idea how to make this file smaller is there a way of attaching files larger than 1mb?

Scott

[mdmackillop]

You can send zipped files.

[infinity]

Here it is as a .zip file, don't know why I didn't think of that. Thanx mdmackillop.

[infinity]

I am still having issues, I appreciate your suggestions but in all reality I do not know how to make it work. Was this file helpful at all in conveying my problem? Thank you much for your time. Have a great day!