+ Reply to Thread
Results 1 to 5 of 5

Validating data entry to multiple textboxes in a userform

  1. #1
    Registered User
    Join Date
    10-19-2017
    Location
    Houston, TX, USA
    MS-Off Ver
    2016
    Posts
    9

    Validating data entry to multiple textboxes in a userform

    Hello all,

    Problem:
    Cannot redirect the user back to the Userform to re-enter correct data into textboxes after a messagebox appears due to an incorrect entry.

    Explanation:
    1) The userform consists of thirty (30) textboxes that are categorized under separate frames based on the type of data (geometry, material properties...etc).

    2) The userform consists of ten (10) checkboxes. Based on the selected number of checkboxes, a certain number of the textboxes are enabled for data entry that would be used further in the code for calculations. For example, if a user selects Internal Pressure checkbox, only 7/30 textboxes are enabled for data entry. If they select Internal Pressure and Buckling, 12/30 textboxes are enabled and so on.

    3) The textboxes are error checked for 2 conditions: no value entered and a value of 0 entered.
    a) If there are no errors, the textbox.value is then stored in a declared variable to be used for an initial calculation.
    b) If there are errors, a message box is prompted but the code continues on with the incorrect entry regardless. (would like to redirect user here after every error).

    5) The result of the initial calculation is range checked to see if it falls within a specified upper and lower bounds.
    a) If there are no errors, the declared variable is used for secondary calculations.
    b) If there are errors, a message box is prompted but the code continues on with the incorrect entry regardless.(would like to redirect user here again after every error).

    6) The error check, initial calculation, range check and secondary calculation for each of the checkboxes are under one Sub, and are called when the Submit (in my case it is Compute) button is clicked.

    I would like the code to redirect the user back to the form to input the correct data rather than continuing on. I have seen examples where Exit Sub is used but since I continue using the variables in the same Sub, it is not relevant. Only thing I can think of is maybe some sort of While loop.

    Any help would be appreciated.

    Thank you.

    Here is a sample of the code (I cannot show the full code since there are confidential information but this example shows only one of the enabled textboxes). In this case the Private Sub Compute_Click() calls Sub UT_Compute() which calls Sub BP_UT():

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,915

    Re: Validating data entry to multiple textboxes in a userform

    Your problem is not clear from just the code you posted, and if you cannot post the file or all code we are going to have trouble figuring it out.

    Normally the way this works is you have bunch of fields where the user inputs data. Sometimes these can be error-checked at the moment the user enters the data (e.g., entering "abc" in a textbox where a number is required) and sometimes you have to wait until the user has completed their data entry (required textbox left blank). The way the latter is done is an error-check function when the user clicks a Submit button. (In your case this looks like the Compute button.) If the error check passes, then the Sub finishes up any appropriate action then calls
    Please Login or Register  to view this content.
    If there is an error, instead the Sub notifies the user (typically with MsgBox) and does not call Hide.

    The code you posted above does not close the form so
    I would like the code to redirect the user back to the form to input the correct data rather than continuing on.
    after this code runs the user is still on the form, and I don't know what you mean by "continuing on."

  3. #3
    Registered User
    Join Date
    10-19-2017
    Location
    Houston, TX, USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Validating data entry to multiple textboxes in a userform

    6StringJazzer,

    Thanks for your reply.

    The code I have is very cumbersome (about 4500 lines long) and I cannot post it all but I will try to explain as much as I can. The portion I show is where the action is. The rest of it is just different variations of what I posted.

    To elaborate more, the results from the secondary calculations are displayed in two locations: an excel worksheet and a second form showing outputs. The second form is displayed once the user clicks the Compute button. I am not hiding the input Userform as I would like the user to still look at their entries.

    ...the latter is done is an error-check function when the user clicks a Submit button
    Are you referring to something like On Error GoTo or Resume Next?

    after this code runs the user is still on the form, and I don't know what you mean by "continuing on."
    I should have been clearer in using "continuing on" I meant the following happens when the user clicks the OK on the message box:
    - If the error is the textbox being empty or having a value of 0, then the whole code crashes due to an error in calculation (usually division by 0).
    - If the error is the textbox having a value outside the lower and upper bounds, the code still proceeds with the calculation and display the second form with results.

    I hope this clarifies my points.

  4. #4
    Registered User
    Join Date
    10-19-2017
    Location
    Houston, TX, USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Validating data entry to multiple textboxes in a userform

    Even thought the code I posted does not close the form, it does not allow the user to re-enter values after clicking OK on the messagebox.

  5. #5
    Registered User
    Join Date
    10-19-2017
    Location
    Houston, TX, USA
    MS-Off Ver
    2016
    Posts
    9

    Re: Validating data entry to multiple textboxes in a userform

    Ok quick update. I have figured out where my issue was.

    I decided to remove the data entry check from Sub Bp_UT and rather include it in the Compute_Click(). I also had to add an Exit Sub after the message box to allow the user to go back.

    I have also managed to a much more concise method of checking the data entry (since my textboxes are inside frames that are inside other frames) without having to repeat the same lines over and over.

+ 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. Saving data from multiple userform textboxes in two separate columns
    By Shazza-D in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2017, 02:50 PM
  2. Send data to specific range using multiple textboxes from userform
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2017, 02:07 AM
  3. [SOLVED] IF multiple textboxes in Userform = Nothing
    By Catsonheat in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-16-2017, 05:48 PM
  4. Validating data entry through a combo box
    By LoftySuth in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2014, 01:48 PM
  5. [SOLVED] Validating Textboxes in UserFrom
    By AndyE in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2014, 05:32 AM
  6. Search worksheet for data in multiple textboxes in userform and display in another workshe
    By SierraKilo78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2013, 09:59 AM
  7. Validating textboxes
    By chrisjames25 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2013, 11:58 AM

Tags for this Thread

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