I have this code
It is looking up a product flavor (from sheet "Data") and matching its blend score (sheet "Data") to determine the necessary adjustment to the equipment. I know I can do this in the userform I just don't know the proper syntax
Please Login or Register to view this content.
One easy option is to copy the result from the cell that has this formula into the textbox.
To use it in VBA code you can put the formula inside a call to Evaluate; you will need to double all the quotes.
A user can type into a textbox. Is the user allowed to change this value?
Hi 6StringJazzer,
On the userform there are 22 option buttons that correlate to the product flavors in sheet "Data," column 1. As well there is an input textbox where the user will input the blend score (correlating to sheet "Data," column 2).
After the user selects the option button and blend score I want it to essentially lookup the textbox and option button and perform the calculation included in the vlookup then display the result in another textbox.
I had originally set up a search row withIn this scenario it uses the excel vlookup after the user submits the data; however, this will not work because I need the value prior to the user submitting the data.Please Login or Register to view this content.
My new code will be in this command button; however, what is the syntax to do this?
This is what it should do:
How can I have it determine the selected option buttonPlease Login or Register to view this content.
Last edited by jdpaul88; 11-19-2018 at 02:55 PM.
Bump 11/21/18 at 9:02 AM CST
In your example formula, what value do you mean by "Selected Option Button"? The option button is either TRUE or FALSE. I suspect you mean some value associated with it, but I don't know what.
Your description is much more complicated than what was suggested by your original post so I would suggest attaching your file so I see the whole picture. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
There is no built-in way to find out which option button is selected. Do you have just one group of option buttons, or more than one and using GroupName? Here is a sample of finding out which button is selected and using the caption in your formula, which may or may not be what you need.
There is no practical way for me to test this without your file.
Your formula refers to $TextBox.Value but $ is invalid for a textbox so I assume you left it in by mistake.Please Login or Register to view this content.
I have declared/grouped all pertinent 22 option buttons in userform_initialize() as
OptB1.Groupname = "Product"
OptB2.Groupname = "Product"
...
OptB22.Groupname = "Product"
I added the module code and it looks like this now
I placed the evaluate code in the textbox_change() where the calculated value should be displayedPlease Login or Register to view this content.
the code is:
Please Login or Register to view this content.
The only issue is that it is not displaying the value in TBoxMic; I do have a command button I could place this code in but it seems it is missing some code to add the resulting value to TBoxMic
Note: In the module code I added the word "Option" to change the codetoPlease Login or Register to view this content.
I currently have the evaluate code in the SelectedOptionButton_Click()Please Login or Register to view this content.
and everything is compiling without a sub undefined error; however, as mentioned, I need the piece to place the value
Something like TBoxMic = Evaluate.Value??
Best Regards,
Jon
Last edited by jdpaul88; 11-21-2018 at 03:37 PM.
I need to spend some time to digest this, but Product needs to be in quotes in this line of your code.
I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors. If Option Explicit were used in this code, it would have flagged Product as an undeclared variable.Please Login or Register to view this content.
It would really make this faster if I had your file.
Pete,
See attached
Here is your file with the code corrected to work. However, I don't know if the final answer being calculated by the formula is the one you want. You will have to test that. Hopefully the code will be clear now that it's all complete and in one place, but let me know if you have questions.
You only have one set of option buttons so you don't need to use the GroupName field. It can be left blank.
I corrected an error in the code that builds the formula; it was missing a parenthesis at the end.
Hi Jeff,
Awesome work. I will test the code for functionality; following confirmation testing I will mark this thread as solved.
Regards,
Jon
It occurred to me that it might be better for your user interface to use a combobox to select the number, rather than a big collection of option buttons. If you are interested in that alternative let me know.
Jeff,
Is this suggestion of a combobox more for aesthetics or for a reduction in code?
It probably wouldn't reduce the code (which shouldn't be a goal in and of itself) but might be a nice interface for the user and a smaller footprint for the form. It also means that if you add more options, you don't have to change the form (i.e., add more option buttons). I'm not trying to convince you to do it, just throwing it out and as an option.
Jeff,
How would the code change if I have declared a set of option buttons with a groupname?
You would change this one line
to whatever your groupname isPlease Login or Register to view this content.
If you only have one group of buttons, you don't need a groupname. If you have more than one group then it's needed.Please Login or Register to view this content.
Thanks Jeff,
What needs to change when the option button caption changes from an integer to a word; I played around with your code and replaced the caption value of option button "1" to "New"
When I run the code I get run-time error
Could not set the value property, type mismatch
Do we need to declare the caption button to include letters?
When you use Evaluate, the VLOOKUP is expecting a number. To use strings, you must put the value in value in quotes. I have completely overhauled the code to solve this problem. The solution involves coding the logic in VBA instead of using Evaluate on a worksheet formula, which I probably should have done in the first place.
Please Login or Register to view this content.
Works great! Awesome help Jeff
Thanks.
One other thing. How do I send a picture to back in the userform; I want the option buttons to overlay on an image. The image still blocks the viewing of the option buttons when I perform the send to back operation
I don't know how to force controls on top of the picture. If you want to use the picture as your form background, then you can go to the Picture property of the form, rather than using a Picture control. Then everything is on top of the image.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks