Greetings again all-two part question here,
I have a user form I'm creating, see attached image, and I would like to populate a result in the other three text boxes when the "Property Case" field has been entered. Also, what code is needed to automatically show the form on startup of the document? I have it fine for floating, but can't quite get it to show on startup.
Thank you everyone!
Kondukt
User Form.PNG
UPDATE: I've been continuing to Google the crap outta this and found how to make it open on start up and to add formulas to text boxes but I'm running into a wall.
The above is what I'm using; however, it seems to error out with a "disambiguous" notice after I enter in the second code for the buttons. If I have it in there for one text box then it works just fine. As soon as I add the code for the second check box it doesn't work anymore. I've tried switching the "userform" to the form name "frmFloatingWindow" to no avail.
Please Login or Register to view this content.
I'm not understanding why it's bouncing back.
Edit: Or had edited the previous post and the forum decided to make a new post.
I got this to work by putting all the textbox# lines under one PrivateSub.
How do I code the attached form, using the "Property Case" field, to type in a value thus returning the vlookup extractions in the other cells/textboxes? As you can see I've tied the texboxes to a cell on the worksheet, but how do code the cell to reference the texbox?
I want to type in a value in the property case field and have it return the vlookup answers for the other text boxes.
Or, how can I do this effectively?
Friendly bump that's still needing to be resolved.
Bumpity bump. Didn't think this was going to be that difficult of a request.
Anyone home?
Where have you tied the textboxes to cells?
This code will only put intial values in the textboxes.
You mention doing a VLookup but give no details about your data and how it's structured.Please Login or Register to view this content.
Could you attach a sample workbook?
If posting code please use code tags, see here.
I don't really know how best to structure what I'm trying to do, hence why I am posting. The textboxes can be tied to cells as coded above, our simply entering in the sheet and cell (IOC!D25) under the "control source" via the textbox's properties.
What I am trying to do is with the attached image (See OP) of my control form I want to enter in a value under "Property Case" and once that has been entered, values from the database are then returned in the other three boxes via a formula-vlookup. How is this done?
It's hard to tell how it's done without knowing anything at all about your data.
Where is it located?
Which column should be looked in for property case?
Which column(s) have the values that should be returned to the textboxes?
Darn, I was kind of hoping there was a code/method that one could point me to for myself to learn/determine.
However, for the sake of my sanity I've attached the document.
The vlookup codes for the text boxes are-for transparency-as follows:
1.) VA Bid: =VLOOKUP(B25,DB!G2:W5000,4,"false")
2.) %: =VLOOKUP(B25,DB!G2:W5000,5,"false")
3.) Bid Date: =VLOOKUP(B25,DB!G2:W5000,6,"false")
These are all located on the IOC sheet, first tab of this workbook. The vlookups are searching through the database tab. I would attach the document, but adding the userform seems to have set the file size too large to upload.
The text boxes are static via a userform that is constantly displayed.
I am not sure how best to formulate/format/code/?? this to work most efficiently.
nublet here.
Sorry, but I'm a little confused.
Do you want to lookup the value in the texbox or the value in B25?
In the user form I provided an image of, in the text box labeled "Property Case" I want to be able to type in a value and once that is entered I would like the remaining three text boxes to return a value as determined by the vlookup formulas I posted.
My Google searches told me to use a cell to link the textbox to in order to return a formulated value. I dont know of any other way to add a formula into a textbox.
You don't need to use a cell link, the formulas could be done in the code.
Also, there's different methods that can be used for this sort of thing.Please Login or Register to view this content.
For example, you could use Find to find the row the property case is on.
Or Match.
Ok, I figured there had to be a way.
Then I would write it as
And so on per text box? Safe to presume "txtProperyCase" is telling where to look for the query when returning the value?Please Login or Register to view this content.
When exactly do you want this to happen?
That code seems to indicate it's when the form opens, but when the form opens the property case textbox will be empty.
PS txtPropertyCase is supposed to refer to the textbox where the user will enter the property case.
Ah, see. nublet here.
I only want the response to return a value when the textbox has a value entered in. So, it's simply a "private sub" type of event?
I really need to get my **** into a vba course.
A textbox has quite a few events, I think I'd pick Exit.
An easy way to access the events of a control is to open the userform module, select the control from the left dropdown and the event dropdown.
This should generate the proper code stub for the event.
PS In the previous code it should be UserForm_Initialize not <TheNameOfTheForm>_Initialize.
I entered in one to test and the code part of it in red was showing up red on the module too. It snaps to the "G2:5000" part.
I selected "Textbox3" in the module's left drop down and chose "exit" on the right drop down.
Please Login or Register to view this content.
You seem to have lost the quotes around G2:W5000.
Indeed I did; however, it is still not pulling the data.
Attached is a screen shot of my monitors showing on one the DB screen with just the cells needed filled out to test this little sucker and on the other is the VBA screen with the code as is.
Unfortunately it is not pulling the data into the text boxes. Text box three is the "Bidded On" box that we've started a test code on. There was no returned value.
And i also just realized it should be six and not four in the code for which column to pull.
By the way, thank you for the help as we work toward the goal.
There's not attachment.
If you are going to attach anything a workbook would be best.
You said it was too large, but you don't need to attach the actuall workbook. A cutdown version should do.
Good Morning again! Aaaaand back at it. This little bugger is my last hitch on my sheet and gah wish I could go Office Space on it.
Alright, so I would love to attach the document but after I created the userform the file went to 2.5mb. I've stripped the doc of any other unncessary modules but it's still sitting at 2.25mbs.
Suggestions?
TxtBox3.jpg
This image is the one I intended to attach before I had to take off yesterday.
Soooo. I really want to close this sucker out.
I've provided several images of the user form, and the vlookups I'm wanting to create in the textboxes referencing the "txtPropertycase" text box.
Helpful people are unable to provide the code for this?
Again, I can't shrink the document anymore than it already is.
What other information might I be able to specifically provide to get some generous folks to ignore my previous "wanting to learn" comment and just code it for meh?
Thanks dudes.
I posted code that should work.
Have you checked the code is being triggered?
You can do that by setting a breakpoint (F9) on the first line of the code, opening the userform, entering something in the property case textbox and tabbing out.
If the code is being triggered you should be transferred to the line of code where you set the breakpoint.
If that doesn't happen then the code isn't being triggered for some reason
Why is hard to tell why without an an example workbook - could be as simple as an incorrect name in the code.
Actually, which textbox have you added the code for?
It should be added for the property case textbox, not one of the textboxes, bidded on?, you want to fill with data based on what's been entered in the property case textbox.
PS What exactly have you tried to create a cutdown version of your workbook?
Have you tried removing formatting?
Did you try copying just relevant data and the relevant userform to a blank workbook?
Yes, I've added that exact code to the Property Case text box code and when tabbing it errors out asking to debug/end.
I've stripped this down to only two sheets, the main and the db..that data is only 73 rows...IT IS the userform spiking its size. I can't remove the user form because that's what we're trying to code. Can you pm me your email and I'll shoot it over to ya that way?Please Login or Register to view this content.
Have cleared ALL the formatting and all the coding say for the one in question, it is NOT dropping below 1.83 mb.
Last edited by Miskondukt; 02-14-2013 at 12:04 AM.
Something strange is going on, a userform can't take up that much room.
Anyway, what's the error message?
txterror.PNG
That little bugger.
Agreed, something strange and I kid you not the sheet I've stripped is STRIPPED! I don't get it. Hell, I even removed most of the buttons on the user form too. Will keep trying to find where this memory is at on it.
Finally got it under!
Further down rows were still formatted.
The error is caused by the VLookup failing.
It's easy to deal with the error, harder to find out why it happened.
Perhaps you should upload a sample file without a form.
PS You could try exporting the form (File>Export File...) and then importing (File>Import File...) it to a blank worksheet.
I did get it to post. the stripped down one.
In the code I posted way back I used txtPropertyCase in the VLookup for the name of the property case textbox.
On your form the property case textbox is called TextBox1.
So you need to change the lookup to reflect that.
That did it and I could have sworn I tried that variation too!
Awh man, ty for working this through to conclusion! jiminy christmas batman!
Now, am I able to assign formatting for date, currency and %s in the textboxes?
Yes, you can use VBA's Format function.
By the way, I notice that most of the properties have 2 entries.Please Login or Register to view this content.
VLookup will only pick up the first one.
For example for '201-317157' the lookup would return 21580 for VA BId from row 9, but in same column in the row below is 20750.
Is that going to be a problem?
No, it won't be a problem. That tab will be sorted to list most recent-which is what we're wanting to pull.
http://i.imgur.com/Ft3NPFX.png
Thank you so very much.
This can be marked closed/solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks