Link a txtbox value to a specific record?

**SAsplin** · 08-28-2009, 06:40 AM

Greetings experts,

Apologies if this question has already been answered, but my searches haven't yielded anything - although I probably didn't express myself very well and I am very new to VBA!

Basically, I have a large spreadsheet with basic data including names, dates and a unique identifier number (UIN). I have created a questionnaire in a userform (with a UIN txtbox) and want to link the answers to the specific individuals on the worksheet.

i.e. If UIN 201 fills in the questionnaire on the userform it will enter the details on the spreadsheet next to the details already entered for UIN 201.

This is probably a really simple action and I'm making a big meal out of it!

Any help would be gratefully appreciated.

Thanks

Steve

**StephenR** · 08-28-2009, 06:46 AM

It might help to attach a sample workbook, but it sounds as if you could use some sort of lookup formula, e.g. VLOOKUP(textbox value, UIN data, column)

**SAsplin** · 08-28-2009, 07:04 AM

I have attached an example of the spreadsheet I am using.

I would like to use the questions entered on the userform to populate columns G through J for the specific person filling in the form.

Hope this makes things a bit clearer.

Steve

**StephenR** · 08-28-2009, 07:18 AM

There doesn't appear to be a userform in your attachment?

**royUK** · 08-28-2009, 07:53 AM

Her's a quick example based on your data

**SAsplin** · 08-28-2009, 08:40 AM

Roy -I tried your example but it came up with:

Compile error:

Method or data member not found.

I've attached the sample spreadsheet again with a simple userform. I'd like any data entered in the question txtboxes to appear on the spreadsheet beside the relevant UIN number entry (rows G trough J).

Thanks,

Steve

**royUK** · 08-28-2009, 09:17 AM

That's strange it works fine for me. What are you expecting in your textboxes?

**StephenR** · 08-28-2009, 09:18 AM

Add a command button and assign this code to it:

Please Login or Register  to view this content.

**SAsplin** · 08-28-2009, 09:25 AM

Strange - it runs ok now. However, I didn't want it to pull data from the sheet when you enter the UIN.

The data entered in to the text boxes (just text) would be completely new and would need to be added on to the end of the data for e.g. UIN 201 (row G onwards).

**SAsplin** · 08-28-2009, 09:45 AM

Thanks StephenR - that seems to work. If there were a number of sheets, where in the code would I specify which sheet to add the data to?

**royUK** · 08-28-2009, 09:48 AM

Originally Posted by SAsplin

Strange - it runs ok now. However, I didn't want it to pull data from the sheet when you enter the UIN.

The data entered in to the text boxes (just text) would be completely new and would need to be added on to the end of the data for e.g. UIN 201 (row G onwards).

That wasn't clear in your post.

I wouldn't rely on a textbox for entering the UIN, better to limit the user options to avoid errors, hence a combobox.

Remove the combobox coding, then add a button and place this code in the button's click event

Please Login or Register  to view this content.

**StephenR** · 08-28-2009, 09:50 AM

See the With line.

Please Login or Register  to view this content.

**SAsplin** · 08-28-2009, 09:57 AM

Thanks guys, you've been really helpful (as always!). Think I've got it sorted now.

Thanks again,

Steve

**royUK** · 08-28-2009, 10:02 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**SAsplin** · 08-28-2009, 10:21 AM

Sorry!! Still having troubles. The solution Stephen gave works fine with UIN's that are purely numerical, but some have a letter in eg. A201.

The code string doesn't seem to like this!

**StephenR** · 08-28-2009, 10:26 AM

A bit clunky but seems to work. You would be better off with Roy's suggestion of a combo box though.

Please Login or Register  to view this content.

**SAsplin** · 08-28-2009, 10:58 AM

Sorry to be a pain, but I'm still having some niggly problems with this. I agree that a combo box would be more useful, but there are over 3000 entries on the spreadsheet - which would be a mammoth task to add to a combo box - especially as more are added each day.

Another problem would seem to be the length of some UIN's we have - some are thirteen digits long e.g. B654389234901 which I believe is too long for Clng?

**royUK** · 08-28-2009, 11:09 AM

maybe use CStr & the Find function

**StephenR** · 08-28-2009, 11:13 AM

Are you asking or telling? If it's text it doesn't need converting. If not, there are probably other approaches.

EDIT: picking up on Roy's suggestion:

Please Login or Register  to view this content.

**SAsplin** · 08-28-2009, 11:19 AM

I believe a figure such as B654389234901 is too long for CLng to handle. This type of number forms some of our UIN's. When this type of UIN is entered in to the above solutions it always reports an error.

**SAsplin** · 08-28-2009, 11:29 AM

Wow! You guys are fantastic. Apologies for the numerous problems with this and thank you so much for your solutions. I'm now more than happy to mark this one up as 'Solved!'

Steve

Link a txtbox value to a specific record?

LinkBack

Thread Tools

Rate This Thread

Display

Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Re: Link a txtbox value to a specific record?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions