+ Reply to Thread
Results 1 to 17 of 17

VBA Form Textbox Format Determined by Values of a Cell

  1. #1
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Question VBA Form Textbox Format Determined by Values of a Cell

    Hello Everyone,

    New here, and new to vba programming.

    Little explanation of what I'm trying to do. I am trying to create a user form for quality control inspectors to fill in and submit data.

    I have the below code for conditional formatting of a textbox, which does work and gets the job done but am wondering if I can makes those minimum/ maximum values be pulled from a separate worksheet in the same workbook file.

    In other words, I have the table on sheet1 where all the data is being submitted to and on sheet2 I have our table that states our minimum and maximum specs.

    Why I would like to do it that way is if our spec changes for any reason, Someone can simply change the value on sheet2 instead of having to sifting through the code to change it.

    Any help would be greatly appreciated.
    Please Login or Register  to view this content.
    Again, this code works and if anything input is not between 2.369 and 2.381 then the textbox they are entering that data into turns yellow.
    Last edited by 6StringJazzer; 10-29-2017 at 10:57 AM. Reason: please use code tags

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    The way I have done this sort of thing is to create a new sheet called Parameters then put the values on there. You can add a new sheet or use an existing one. It will enhance readability of the code if you make these values named ranges. For example, identify cells that will be called MinValue and MaxValue.
    Please Login or Register  to view this content.
    A more specific solution for your file can be provided if you attach it.

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you in your post this time because you are a new member. --6StringJazzer
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Thanks for the help Jeff,

    I'll give this a go when I get a chance and let you know if I got it to work.

    One other quick questions, is there a quick to put in infinite rather than use the "9999999"?

    Thanks again,
    Jon

  4. #4
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    I plugged in the Range code as seen below. I named my sheet as wsParameters and in that sheet H5=2.369 and I5=2.381 no matter what I type in textbox, it displays as yellow background even if the value is within the parameters.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Jonny117; 10-29-2017 at 12:02 PM.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Quote Originally Posted by Jonny117 View Post
    in that sheet H5=2.369 and I5=2.381
    In your code you used H15 and I15.

    Also you will have better luck using CODE tags instead of FORMULA tags.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Quote Originally Posted by Jonny117 View Post
    One other quick questions, is there a quick to put in infinite rather than use the "9999999"?
    No. There is no such thing an infinity on a computer. However the way this is usually done is:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Quote Originally Posted by 6StringJazzer View Post
    In your code you used H15 and I15.

    Also you will have better luck using CODE tags instead of FORMULA tags.
    H15 and I15 is correct, so the range is correct, I posted it wrong on here, with correct range it still stays yellow background.

    Well noted on the tags.

    Thanks,
    Jon

  8. #8
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Quote Originally Posted by 6StringJazzer View Post
    No. There is no such thing an infinity on a computer. However the way this is usually done is:

    Please Login or Register  to view this content.
    I'll give this variation a try and see if I have better luck with it.

    Thanks again for your help,
    Jon

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Quote Originally Posted by Jonny117 View Post
    H15 and I15 is correct, so the range is correct, I posted it wrong on here, with correct range it still stays yellow background.
    Would need to see your file to help further. 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.

  10. #10
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    See if this works.

    It's far from finished mind you, and I'm learning as I go.

    Jon
    Attached Files Attached Files
    Last edited by Jonny117; 10-29-2017 at 09:07 PM.

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    What was not stated in your description was that you have many of these statements, instead of just one. There is a more general solution than what I first suggested. Based on the structure of your data, you can look up the boundaries by finding size in column A of Parameters. Here is a revision to your code.

    Also note that you are using yellow fill if the OD value is outside the the required limits. You can do this in one statement instead of two.

    Your table and combobox have size values going up to 63 but your code only checks up to 16, so my code does the same thing as your code. If you want to cover all sizes, simply remove the initial If statement.

    Please Login or Register  to view this content.
    I don't know how your process works so I don't know how to test this.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Hi Jeff,

    If I can get that to work that would be GREAT!

    The idea is that the inspector selects the size in the Size Combobox that they are to input data, they then input the data in say the Pipe OD Textbox. The goal is say they select 10 in the size combobox and then enter a value in the pipe OD textbox that is outside the parameters, the box turns yellow but will display white if it falls within the parameters which for 10 is 10.735 and 10.765.

    With the file you provided, if I have 10 selected in the size combo box, no matter what value I type into the Pipe OD textbox it displays as yellow, even if the value falls between the parameters.

    As always, I appreciate the help.

    Jon

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Forcing conversion of the textbox contents to Double fixes it.

    Note that there is no error checking on what the user enters into the text box. If they enter something that is not a number, it will cause a runtime error.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Thanks Jeff, I'll try this out when I get a chance.

    I plan on doing some error checks and limit to numerical value and such.

    Thanks,
    Jon

  15. #15
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Hi Jeff,

    Fantastic! not only does that code work, I was able to modify it to also be used on the Hub Thk Textbox where the acceptable limits in the parameters sheet can change based on the size and SDR combinations picked in the Size and SDR combo boxes.

    Please Login or Register  to view this content.


    I am however having issues with modifying the same bit of code for the OAL Textbox and I'm not sure why, I would think it should be very similar to the above code with some changes like to the below.
    Edit:sorry, got it all working thus far. again, thanks Jeff for all the help with this. I'm sure you'll hear from me again with some other things as I am learning as I go. Big big thanks.

    Please Login or Register  to view this content.

    Hopefully the more completed parameters worksheet on the attached file gives a better idea of the limits I'm trying to display in the userform if the value entered is outside the set parameters.

    Thanks,
    Jon
    Attached Files Attached Files
    Last edited by Jonny117; 11-02-2017 at 09:31 PM.

  16. #16
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    Hello again, I hope everyone is ready for the new year.

    I am back to working on this userform for inspectors to enter data.

    I have mostly completed the first form, however, I am unable to figure out how to color a userform textbox based on a value in my parameters sheet when the parameter value is determined by two columns. In the picture below you can see that multiple rows have the "size" column value of "2" "3" etc. and also the "SDR" column also comes into play when I am trying to format the color of a text box if the value entered in said textbox falls outside those values.




    I have tried using something like the code seen below and some other variations trying to get something to work but haven't had any success.

    Please Login or Register  to view this content.
    Also, here is my current Excel file for review.

    Any help with this would be greatly appreciated as usual, kinda yanking my hair out on this.

    Regards,
    Jon
    Last edited by Jonny117; 12-22-2017 at 11:18 AM.

  17. #17
    Registered User
    Join Date
    10-29-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    11

    Re: VBA Form Textbox Format Determined by Values of a Cell

    I had some luck with the code below but only works for some size =/:

    Please Login or Register  to view this content.
    Last edited by Jonny117; 12-22-2017 at 12:33 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Referencing a textbox value from a determined value to a textbox range
    By gwelf in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-23-2017, 08:38 AM
  2. Replies: 3
    Last Post: 04-27-2015, 09:41 AM
  3. [SOLVED] Correct date format from textbox in form to table
    By eddijensen in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-09-2014, 05:28 PM
  4. [SOLVED] Construct a formula on a pre-determined cell for a pre-determined duration of cells
    By Shaun Gemiver in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 11:14 AM
  5. Replies: 2
    Last Post: 12-16-2011, 05:57 PM
  6. Date off form textbox entering as US format not european
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2009, 08:21 AM
  7. it is possible to format a Form TextBox Value?
    By jose luis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2005, 06:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1