Userform textboxes and retreiving values 1

[mleosu]

I have a userform that allows the user to choose an order template from a combobox. I also have a ton of textboxes in the userform to return values based on the template chosen. I do not want the user to change the results in the textboxes... look dont touch. then they can approve the template or choose another and the template chosen will be returned to the spreadsheet.

I have searched for a way to do this but haven't put my finger on anything... can you point me in the right direction?


link to the userform:

http://www.ppbcsingles.org/BRINKS/template-userform.jpg

link to the table:

http://www.ppbcsingles.org/BRINKS/template-values.jpg

thanks in advance for any help to get me started.
--emily

 

[SgtJarrow]

From the image...this is a Visual Studio based application....

Set the .Enabled property for each box to False and set the .Backcolor property of each one to White as well.

This will lock them but they will look normal.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

look dont touch
So, don't use TextBox but Label !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

okay - but how do i call the values from the table based on the template chosen... if i were working strictly in excel, i would use a vlookup, but i am not sure how to do that in a VBA userform

 

[SgtJarrow]

Well, just because a control is locked, does not mean code can't affect it. It just means the USER cannot do anything to it. You can still set it through VB(A) code.

You will have to create a connection the the Excel spreadsheet, find the row you want and then populate the fields...I haven't worked with VB->Excel too much so that is not my strong point.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[fumei]

Uh, PH gave the answer. Use Labels, not Textboxes. In fact, that should be a general rule.

Textboxes ARE for user input, that is what they are for. Yes, you can do some locking of them with .Enable, but really...textboxes are for users to enter text into. If you are just displaying text, use Labels. That is what THEY are for.

Gerry
My paintings and sculpture

 

[mleosu]

okay - so i have labels instead of textboxes... i am still trying to figure out how to call the information from the table based on the template chosen

any help is appreciated

 

[Andrzejek]

Since you populate text boxes with values from your excel, why not do the same with all labels to the left of text boxes? Pull all data from your xls.

This way, if anybody will ever want to re-word any of the rows in excel, you don't have to go to your VBA to do the same.

This way, you still can claim 16 hours of work if that happens, and go fishing instead....

---- Andy

 

[mleosu]

okay - but how do I populate the labels/textboxes with the data???

 

[PHV]

How did you populate the TextBoxes ?
I would use a vlookup
In an UserForm you may play with the WorksheetFunction object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mleosu]

i didnt populate them, i cant figure out how.... i was saying that if i were in an excel worksheet, i would use a vlookup, but i dont see how i can do that in the userform... i will look into the worksheet function

 

[mleosu]

okay - this is what i came up with, but it says that my function isnt defined...and it highlights the error in "Private Sub ComboBox1_Change()"

Private Sub ComboBox1_Change()
Dim x As Integer
x = OrderTemplateSettings.ComboBox1.Text

Workbooks("newlocationsetup.xls").Worksheets("Templates ").Activate

'CURRENCY
OrderTemplateSettings.Label82.Value = HLookup(x, Range("A2:AE65").Value, 1, False) 'ones
OrderTemplateSettings.Label83.Value = HLookup(x, Range("A2:AE65").Value, 2, False) 'twos
OrderTemplateSettings.Label84.Value = HLookup(x, Range("A2:AE65").Value, 3, False) 'fives
OrderTemplateSettings.Label85.Value = HLookup(x, Range("A2:AE65").Value, 4, False) 'tens
OrderTemplateSettings.Label86.Value = HLookup(x, Range("A2:AE65").Value, 5, False) 'twenties
OrderTemplateSettings.Label87.Value = HLookup(x, Range("A2:AE65").Value, 6, False) 'fifties
OrderTemplateSettings.Label88.Value = HLookup(x, Range("A2:AE65").Value, 7, False) 'hundreds

 

[mleosu]

this is what i was looking for:

I had to change .value to .caption on the labels
I had to insert Application.Worksheetfunction before the Hlookup

 

[combo]

Have you considered one two-column listbox, with first column for descriptions and the second for values, instead of the set of labels?

combo

 

[mleosu]

no, i didnt consider, having the labels allows me to group and move around to make the form look like I want it to. i have already done all the work for this project, but maybe next time i will think about the list box... thanks for the idea

 

[mleosu]

PROBLEM FIXED - USED LABELS...

but what i was really looking for was how to populate the label with data from the spreadsheet... this is how I did it:

OrderTemplateSettings.Label82.Caption = Application.WorksheetFunction.HLookup(x, Range("A2:AF65").Value, 2, False)