Referencing a named range for a UserForm?

S

Soaring Eagle

New Member
Joined
Apr 16, 2009
Messages
7
Hey all,

I have posted this problem on VBA Express also, If you would like to see the history of my post and the replies here is the link...

http://www.vbaexpress.com/forum/showthread.php?t=26269

I am trying to populate a ComboBox on a user form with a list that is on a different sheet in the same workbook. This list will be popluated by code that I have already written and that part works great, but I am new to UserForms and do not know how to populate the ComboBox with the values in the list. I have had several suggestions from all the wonderful Vbaxers but to no avail, I am still having problems. I have posted a sample of the workbook on VBA Express and would do the same here but for some reason it says "You may not post attachments". If you want to see the sample workbook I have it attached on VBA Express. There are actually two different things I am trying to accomplish.

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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Soaring Eagle/Infinity

I've looked at your attachment and read your posts on VBA but I still don't understand what you want to do.

If you simply want to populate the combobox with the values on the creditors use this.
Code: 
Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "'CREDITORS'!A2:A5"
End Sub
If you want data from the other sheet then you'll need to explain things further.

For a start the layout of the data is not particularly conducive to working with.:)
 
Upvote 0
Hi Norie,

Fancy meeting you here. I appreciate your time and response, the problem with

ComboBox1.RowSource = "'CREDITORS'!A2:A5"

is that it will not take into account when the list changes which will happen quite regularly. Any other suggestions? I know it is not the best layout for coding but it is the best layout for my purposes and there are so many calculations going on in the background and so much code already written for this layout I do not want to redesign the entire thing, I think once I know how to populate the combo box and then populate the text boxes based on the selection made in the combo box I can work through the rest with offsets. Thank you again for your help.
 
Upvote 0
Well if you want a bit of dynamism.
Code: 
LastRow = Worksheets("CREDITORS").Range("A" & Rows.Count).End(xlUp).Row
 
Combobox1.RowSource = "'CREDITORS'!A2:A" & LastRow
Simple as that, no named dynamic ranged names needed really, and that's probably why things were getting a bit confused over on VBX.

As for the structure of the other worksheet, I really don't see how it's conducive at all.

You should probably be storing the data in a flat-file format ie fields for customer/bill details, bill type, addresses etc

Then use that data to produce the overall bill/invoice as you have it on Bills worksheet.
 
Upvote 0
Okay, that makes more sense now. I added the code you gave me and ran it and it is saying that the variable is not defined and highlighting "ComboBox1", that is the current name of the combo box so I am not sure why it is looking for a variable name. Here is the code...

Code: 
Sub ShowMyUserForm2()
 
    Dim lastRow As Object
    Bill_Payment.Show
   'clear the contents of the combo box
   Bill_Payment.ComboBox1.Clear
    If Not bResponse Then End
 
    If bResponse Then
 
lastRow = Worksheets("CREDITORS").Range("A" & Rows.Count).End(xlUp).Row
 
ComboBox1.RowSource = "'CREDITORS'!A2:A" & lastRow
        MsgBox "Good Job!!!"
    End If
    Unload Bill_Payment
End Sub

What am I doing wrong??? :confused:
 
Upvote 0
Don't put the code in the sub that shows the form, use that to show the form, nothing else.

If you want to populate the combobox use the initialize event of the userform as I did in post #2.

A couple of points.

1 LastRow is not an Object, it's a variable.

2 What is bResponse and what's it's purpose?
 
Upvote 0
I forgot to answer your question... the bResponse is to determine which button the user pushes, either the OK or Cancel button and do something based on that choice. Thanx again!
 
Upvote 0
If that's what bResponse is there's some code missing, because I don't see any that asks the user to make any sort of input/choice.:eek:
 
Upvote 0
Yes the code for that is behind the buttons on the userform and all it does is unload and clear the form. I am having another problem though, I thought the code would have been similar to populate the text boxes as it was for the combo box but it does not appear to be. Once the user makes a selection in the combo box the text boxes should populate with a value, if there is a value in the cell, based on the selection. For example if the user selects one of the creditors that comes up in the combo box it finds that creditor on my "Bills" sheet and selects that cell, that part works fine, what I want it to do is if there is any value in the cells that are controled by each text box I want that value to appear in the text box on the user form so that it can either be over written or saved with the value that is already there, if there is no value in the cell then I want the textbox to be blank. I noticed there is no RowSource property for text boxes but there is a ControlSource property so that is what I used but it is not working. If I put a cell reference in the control source in the properties window it pulls in the value that is in that cell but the cell should be determined by the users selection with an offset to those cells, does that make sense???

Code: 
Private Sub Payment_Creditor_Name_Change()
        Dim billSelection
            billSelection = Update_Bill.Payment_Creditor_Name
            Columns("B:B").Select
            Selection.Find(What:=billSelection, After:=ActiveCell, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
            ActiveCell.Select
            With Update_Bill
                .Past_Due_Amount.ControlSource = ActiveCell.Offset(4, 7).Value
                .Cancellation_Date.ControlSource = ActiveCell.Offset(4, 8).Value
            End With
 
 
End Sub

Thank you!

Scott
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA Error on userform (Run-time error '91')
Replies
5
Views
79
cerberus1845
C
S
  • Solved
add a message box if a match using a userform list box doesn't match
Replies
7
Views
111
still learning
S
T
  • Question
Refer to a sheet selected by combobox
Replies
2
Views
73
dmt32
D
G
  • Question
Trouble pre-setting userform values based off user input
Replies
2
Views
64
gc2001
G
A
  • Question
VBA Userform List Box to Populate upon entering a specific text in Column B, copy data upon selection and paste data in the same Row in another Column
Replies
3
Views
171
arijitirf
A

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top