Posts by Qwest336

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    The form is expanding so much that there are constantly a lot of fields that need to be hidden based on choices made in the process. In some of the more simple iterations, I have used conditions to set labels and field visibility but that code gets cumbersome as it's on a per field basis. Here's an example of one:



    If there is a better way, I certainly don't know it. How would you handle a scenario where a combobox choice determines the visibility of multiple fields?

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Paging royUK!


    Quick question regarding your solution above.


    If I needed to add another Multipage object inside of the previous Multipage object, is there any way that I can make those controls follow the same behavior?


    For example: If the active page of Multipage1 has three textboxes and a combobox that populates Multipage 2. Multipage 2 has three text boxes on page 1 and two textboxes on page 2.


    Can the code you provided be modified so that the three textboxes and the combobox on the active page of Multipage1 are required as well as the three text boxes on the active page of Multipage2...but NOT the two text boxes on the inactive pages (page 2) of Multipage2?

  • Textbox: Date Format and Validation

    Re: Textbox: Date Format and Validation


    Thanks for your response, cytop.


    That code worked fine...I added it to the AfterUpdate event instead of the exit event as something seemed more preferential about that behavior. However, I'm trying to put the focus back on that textbox when there is an error and it's not working. Instead, it's going to the next input field in the Tab Order. Can you tell what I did wrong?


  • Textbox: Date Format and Validation

    Good morning all,


    I guess I'm handling parts of my data validation too rigidly. Currently, I enforce validation of dates using the following code:


    Code
    Private Sub tbUHAEffectiveDate_AfterUpdate()
'Data Validation for Date Format
If Not tbUHAEffectiveDate.Value Like "##[/]##[/]####" Then
        MsgBox "Please enter in MM/DD/YYYY format"
        tbUHAEffectiveDate.SetFocus
End If
End Sub


    I also default the box to show them the format in the Userform Initialize event:


    Code
    tbUHAEffectiveDate.Value = "MM/DD/YYYY"


    However, the users would like to be able to enter their date format in formats resembling a date and have it converted on the back end instead. For example, if the user enters "01012016", "1/1/2016", "1-1-16", etc., any of those should be acceptable and converted.


    This information is being submitted to a table on a worksheet in this workbook as follows and that part doesn't matter much to me because the table can convert it there.





    However, I am passing that information from some of the date fields to an .HTMLbody tag where I perform a calculation for the number of workdays using the following code:


    Code
    Dim DateExpected As Date
   DateExpected = WorksheetFunction.WorkDay(Date, 2)


    And then:


    Code
    Format(DateExpected, "Long Date")


    And I'm worried that a date in an improper format will result in an invalid operation performed.



    Does anyone have a way to take entry resembling a date format and convert it to a date on the AfterUpdate event or in the Submit Click Event so that the conversion happens prior to the other actions?


    What are your best practices when it comes to handling date fields?


    As usual, any help is greatly appreciated!

  • With Outmail - Conditional Email String for .HTMLBody tag

    Good morning all,


    I am currently using the With Outmail statement and the .HTMLbody tag to compose an HTML-formatted email. It works fine, however, since it is HTML, the end user is going to get a long email with much of it not needed. Here is the current code:



    What I am attempting to do is to use an If Else Statement combined with a String to use the field "cmbEscalation_Type" to determine which String the .HTMLBody tag should use. This way, I can make a different .HTMLbody formatted email for each of the choices in the cmbEscalation_Type combobox. I started off with the first line and I'm getting a "Compile error: Object Required" Error. It's pointing to the last value in the string, "EscalationsForm.tbRescDescription" I haven't even dealt with the change in conditions yet, but here is the code that generates that error:



    Any help with the first part or even the conditional portion is greatly appreciated by this VBA Novice + (Still a novice, but gaining experience, lol). Thanks!

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Figured it out! Thanks again for all of your help. What I did was modify your code to include a lookup for the "Tag" property then I included the word "Required" into that property for all the controls I wanted it to check. Instead of only using a Message Box, I also added some coloration to the Required field. Here's the modified code and THANKS AGAIN for all of your help!


  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Uh oh...one problem.


    Quote

    On Page 1 of the MultiPage1, there are three Text Boxes. A TextBox for SSN (Required), a TextBox for Date (Required), and a Multiline TextBox for Details (Not Required).
    Pg1.JPG
    On Page 2 of the MultiPage1, there are three Text Boxes. A TextBox for Height (Required), a TextBox for Weight, and a Multiline TextBox for Details (Required).
    Pg2.JPG


    In that response, I talked about not all of the fields on each page being Required for submission. Is there any way to specify which are mandatory and which are not? If I had Check boxes, can I make those fields not required? On each Multipage, there are only between 2 and 6 fields that are required.


    Thoughts?

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Quote from royUK;770872

    I'm not sure why you posted all that code. I posted code to check if the textboxes on the active page of the MultiPage have been completed. which is what you asked for. The code replaces your If ... ElseIf code to check the TextBoxes.


    Thank you SO much. It works like a dream! The problem I had originally had nothing to do with the code to check for the active page of the Multipage...It was including "Page 1, Page 2, etc" in the list for the Combobox because I was already populating that field with a Named Reference (dynamic with Offset for growth) in the following code:


    Code
    For Each cell In [Escalation_Type]
Me.cmbEscalation_Type.AddItem cell
Next cell


    Anywho...it works perfectly! Thanks again for all your help and for putting up with me! :)

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Quote from royUK;770850

    I can't see what sensitive information would be given away by having the controls & code on the userform.


    Try this code




    Okay...I guess you're right. Sorry for the scariness, lol. I've stripped out the SSN Lookups, which were the main issues that I had, and I'm attaching the file.


    I added your code into another version but I can't get it to work properly so I've taken it back to where it was prior to the attempt. Please excuse the very basic nature of my coding as this is my first venture into Userforms.



    Thanks for the help thus far...

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    Hello RoyUK...sorry if I'm making this more complicated. I'm just trying to convey the issue without giving out sensitive information.


    I check for required fields using the following Looped If Else statement:

    Code
    If Me.tbName.Value = "" Then
 Me.tbName.SetFocus
 MsgBox "'Name' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub


 ElseIf Me.tbEmail.Value = "" Then
 Me.tbEmail.SetFocus
 MsgBox "'Email' is a mandatory field...", vbOKOnly, "Required Field"



    I then use the

    Code
    Private Sub cmdSubmitEmail_Click()

    to start the Required field check, send the email using


    And send the information to a table in the worksheet:


    Code
    Dim Sheet2 As Worksheet
Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
nr = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheet2.Cells(nr, 1) = tbName
Sheet2.Cells(nr, 2) = tbEmail




    I can add the above loop to all the fields, but then, on the Click Event for the submit button, it's going to tell me that they are empty which, of course, they are, but by design.

  • Userform: Required Fields on Active Multipage Only

    Re: Userform: Required Fields on Active Multipage Only


    My apologies RoyUK. A lot of the populated fields is proprietary and/or sensitive so I can't put up the workbook code. Instead, I made a quick sample just to give you an idea. I hope this works.


    On the main portion of the form, there are three fields (All Required Fields). A TextBox for Name, a TextBox for Email, and a ComboBox with three choices.


    Code
    Private Sub UserForm_Initialize()
'Populates Combobox
With ComboBox1
     .AddItem "Page 1"
     .AddItem "Page 2"
     .AddItem "Page 3"
End With
End Sub


    On Page 1 of the MultiPage1, there are three Text Boxes. A TextBox for SSN (Required), a TextBox for Date (Required), and a Multiline TextBox for Details (Not Required).
    [ATTACH=CONFIG]69133[/ATTACH]
    On Page 2 of the MultiPage1, there are three Text Boxes. A TextBox for Height (Required), a TextBox for Weight, and a Multiline TextBox for Details (Required).
    [ATTACH=CONFIG]69134[/ATTACH]
    (I just included Page 3 as blank..but there would be input fields there too with mixes of Required and Not Required)


    The Selection in ComboBox1 determines which Page of the Userform is active with the following code:

    Code
    Private Sub ComboBox1_Change()
'Changes Multipage based on ComboBox1 selection
With Me
        .MultiPage1.Value = .ComboBox1.ListIndex
    End With
End Sub


    The user will ALWAYS only be filling out one page of information.


    When the form is submitted, it sends the information to a table and submits the results via Outlook. In the example, I have filled out all of the information in Page1. However, I can't submit the form because of the Required Fields on Page 2 for 'Height' and 'Details'.


    I would like to make the Userform so that when I select Page 1, only the required fields on that page are required for submission. I want it to treat the fields on Page 2 as non-required, unless I activate Page 2 by selecting that option in Combobox1.


    I hope this clarifies...sorry I couldn't give more code.

  • Userform: Required Fields on Active Multipage Only

    Hello everyone,


    I can't wait until I am one of the experts answering these questions instead of one of the novice members posting them!


    I have a Userform that contains a Multipage object. I've coded the Multipage so that the page that is active is linked to the selection in a combobox using the ListIndex property. And I have 6 fields in the top (not on the multipage) that are common to each submission that are required and work perfectly.


    What I would like to do is to make all of the fields only on the currently active Multipage required but leave the fields on the inactive Multipages as not required. The required fields would need to switch if a new page is activated with a new selection from the combobox.


    Hopefully I'm being clear enough. But please let me know if you need clarification. Thanks, in advance for any help provided!

  • 2 Business Days from date entered

    Good morning all,


    VBA Novice here!


    I'm making a userform that returns an email using the "With Outmail" with statement to send .HTMLbody code as a message. In the first line of the subject, I want to inform the submitter that we will respond to their request by close of business on the 2nd business day from the submission date. However, the current "code" I used doesn't account properly for business days.


    Can anyone help me modify the code so that it accounts for business days? This is part of the Click event procedure for a "Submit" button where "tbTodaysDate" is a locked text box field that auto-populates today's date..




    Any help is greatly appreciated as I've seen some answers using functions but I don't know how to properly deploy them, nor do I understand what they are doing.