How do I make a selection from a combobox on my VBA userform populate a textbox?

**ChristianMba** · 10-13-2012, 04:35 PM

Hi guys

I am a VBA Newbie and have found the posts on the forum to be incredibly helpful. I have a userform and what I would like to happen is when a user makes a selection from the combobox it populates a textbox on the userform. I was able to customise the following code I found on a thread in the forum:

Please Login or Register  to view this content.

This worked really well in populating the textbox, however, the problem I have is that I would like the code to display the value linked to the selection. At present, the selection made in the combobox and what is displayed in the textbox do not correspond.

e.g.

Name	Nation
Henry	France
Messi	Argentina
Ronaldo	Portugal
Rooney	England
Fabregas	Spain

At the moment, when 'Messi' is selected the corresponding nation comes up as 'France' instead of Argentina. I don't think it is as simple as changing the number in the ListIndex because different users will be selecting different data so their positions will vary.

What do I need to adjust to ensure the two values remain linked?

Thanks in advance for your help.

Chris :)

**Bishonen** · 10-13-2012, 04:40 PM

please attach a example workbook. This will make it way easier to help you out.

thanks

**ChristianMba** · 10-13-2012, 05:06 PM

Hi Bishonen

Thanks for the reply.

As it stands, the my workbook now contains sensitive data which I wouldn't be allowed to publish. I have tried to adapt it to post it on here, but I can't seem to find a way to do that without messing up the way it currently functions.

Is there something I can adjust in this code:

Please Login or Register  to view this content.

which would tell the textbox to only retrieve data from the same row as the combobox selection?

So no matter what row the combobox selection is on, the textbox will always retrieve the value of the same row, along by one column - Might it have something to do with offset?

Thanks

Christian

**Bishonen** · 10-13-2012, 05:16 PM

rooney.xlsm

dunno - seems to me, that your code works.

**ChristianMba** · 10-15-2012, 12:34 PM

Thanks for your reply and help Bishonen.

I think I've identified the problem - I'm just not sure how to solve it.

Attached is a skeletal version of the database I have created. I work in education and I have to gather sensitive data from a number of different users. Each user has a log-in which limits the access they have when inputting information into the database i.e. if you are an employee at Green High, you are only able to access the students that attend Green High.

If you open my workbook, the following explanation will make more sense.

- Click on the button on the Menu sheet
- Using the combobox for the username, a range of names will show up
- When you select a name, the corresponding date of birth shows up at the bottom of the userform

If you look at the sheet entitled 'EYFSList', you will see that 'Lucy Loo' goes to Red High and her birthday is on '12/12/2010'. However, while the userform is active, it shows 'Mike Stand' as having his birthday on '12/12/2010' when his birthday is actually '13/01/2011'.

* It seems that because the userform is only looking for students that attend Green High, it skips over Lucy Loo because she attends Red High, but does not completely skip all the information linked to Lucy Loo, thus displaying incorrect information. I would like the userform to ignore all the data linked to Red High and only to display the data applicable to the user being selected.*

What do I need to change in my code to stop this from happening?

Thanks

Chris :)

**Bishonen** · 10-15-2012, 01:00 PM

hi there Christian,

without analyzing your whole userform, I can come up with such simple idea:

Please Login or Register  to view this content.

This should do the trick. I'm not really sure whether you'd like just to get it working or to preserve the last method you have chosen.

**ChristianMba** · 10-16-2012, 04:07 AM

Hi Bishonen

This is fantastic! Thank you for your help.

I will use this code and adjust it to my needs, although I'm not sure what all the terms mean. Would you mind giving a simple explanation of what the following parts of the code are doing:

Please Login or Register  to view this content.

My final workbook will have a total of 12 worksheets. Will this affect the way that the code works?

Thanks

Christian

Christian

**Bishonen** · 10-16-2012, 05:22 AM

Hi Christian,

as long as the second sheet in your workbook is the one with the names and birthdates, then yes, it will work even with 100 additional worksheets. If you'd like to make it a bit less error prone, change the number

worksheets(2)

to the whole worksheet name.

**ChristianMba** · 10-16-2012, 01:08 PM

Hi Bishonen

Thank you for all your help! It worked.

You rock!

Christian

How do I make a selection from a combobox on my VBA userform populate a textbox?

LinkBack

Thread Tools

Rate This Thread

Display

How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Re: How do I make a selection from a combobox on my VBA userform populate a textbox?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions