• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Force User Input

Hello Experts,

I have created a userform within Outlook.  Are there any fields in the properties pane that I can enable, or even some VBA code that I can put inplace to force user to type something into them?

See attached screenshot.  The fields in yellow are required fields.  Ideally, I want the "Generate Email" button to be disabled until required fields are populated with a value.

force user input
Thank you in advance for your help!

~ Geekamo
  • 4
  • 3
1 Solution
You will have to write some code to check the values if the various text boxes. There are several ways that you can use this code. You can use some code to raise a MsgBox when a button is clicked.
What I tend to do is to enable or disable the button when the value if the text boxes is changed with some code like this:
Private Sub txtInPut1_Change()
    CommandButton1.Enabled = Me.ValidateBoxes
End Sub

Private Sub txtInPut2_Change()
    CommandButton1.Enabled = Me.ValidateBoxes
End Sub

Function ValidateBoxes() As Boolean
    If txtInput1.Value = "" Then
        Exit Function
    End If
    If txtInput2.Value = "" Then
        Exit Function
    End If
    ValidateBoxes = True
End Function

Open in new window

GeekamoAuthor Commented:
@ GrahamSkan,

I havn't had a chance to test out your solution, but I think I will have some type to impliment it tonight.  I will let you know the outcome...

~ Geekamo
OK. Thanks for keeping us in the picture. Good luck
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

GeekamoAuthor Commented:
@ GrahamSkan,

Ok, finally weeded out other issues with my code I was having - now I can get back to this!  Thanks for being patient!  :)

I have added your code into mine, changed around some of the names, but nothing is working.  I'll post all the info again, with specifics - so hopefully you'll be able to troubleshoot what is going on.

(Below, I am making another lil' request - hopfully it's not a headache for you to figure out)

UserForm Screenshot:

Anything highlighted in red - is a field I need some type of value from the user.

Field Names:




When this UserForm opens, I would like...

The "Generate Email" button (named: GenerateEmail) to appear grayed out / disabled.  If the user clicks it, NOTHING should happen.

I would also like, all of the fields I mentioned above to be highlighted in a light yellow color, applied to the inbox box - that way visually the user can see exactly what field needs a value.

Once the user enters value into the input boxes, then they go back to their normal white color - and if all required fields are filled in - then the "Generate Email" button starts working again.

Is all that possible?

Here is the latest version of my code that I am working with.

Option Explicit

Private Sub GenerateEmail_Click()


    Unload SampleRequest


    Dim mai As MailItem
    Dim ItemNum(1 To 10) As String
    Dim ItemQty(1 To 10) As Integer
    Dim ItemDesc(1 To 10) As String
    Dim i As Integer
    Dim strText, OrderNotes As String
    Dim CustNum, CustName, ContNum, ContName As String
    Dim ShipName, ShipAdd1, ShipAdd2, ShipCity, ShipState, ShipZip, ShipAttn As String
    Dim Item1Num, Item2Num, Item3Num, Item4Num, Item5Num, Item6Num, Item7Num, Item8Num, Item9Num, Item10Num As String
    Dim Item1Qty, Item2Qty, Item3Qty, Item4Qty, Item5Qty, Item6Qty, Item7Qty, Item8Qty, Item9Qty, Item10Qty As String
    Dim Item1Desc, Item2Desc, Item3Desc, Item4Desc, Item5Desc, Item6Desc, Item7Desc, Item8Desc, Item9Desc, Item10Desc As String


    CustNum = txtCustNum.Value
    CustName = StrConv(txtCustName.Value, vbUpperCase)
    ContNum = txtContNum.Value
    ContName = StrConv(txtContName.Value, vbUpperCase)

    OrderNotes = txtNotes.Value

    ShipName = StrConv(txtShipName.Value, vbUpperCase)
    ShipAdd1 = StrConv(txtShipAdd1.Value, vbUpperCase)
    ShipAdd2 = StrConv(txtShipAdd2.Value, vbUpperCase)
    ShipCity = StrConv(txtShipCity.Value, vbUpperCase)
    ShipState = StrConv(txtShipState.Value, vbUpperCase)
    ShipZip = txtShipZip.Value
    ShipAttn = StrConv(txtShipAttn.Value, vbUpperCase)

    ItemNum(1) = txtItem1Num.Value
    ItemNum(2) = txtItem2Num.Value
    ItemNum(3) = txtItem3Num.Value
    ItemNum(4) = txtItem4Num.Value
    ItemNum(5) = txtItem5Num.Value
    ItemNum(6) = txtItem6Num.Value
    ItemNum(7) = txtItem7Num.Value
    ItemNum(8) = txtItem8Num.Value
    ItemNum(9) = txtItem9Num.Value
    ItemNum(10) = txtItem10Num.Value
    ItemQty(1) = Val(txtItem1Qty.Value)
    ItemQty(2) = Val(txtItem2Qty.Value)
    ItemQty(3) = Val(txtItem3Qty.Value)
    ItemQty(4) = Val(txtItem4Qty.Value)
    ItemQty(5) = Val(txtItem5Qty.Value)
    ItemQty(6) = Val(txtItem6Qty.Value)
    ItemQty(7) = Val(txtItem7Qty.Value)
    ItemQty(8) = Val(txtItem8Qty.Value)
    ItemQty(9) = Val(txtItem9Qty.Value)
    ItemQty(10) = Val(txtItem10Qty.Value)
    ItemDesc(1) = txtItem1Desc.Value
    ItemDesc(2) = txtItem2Desc.Value
    ItemDesc(3) = txtItem3Desc.Value
    ItemDesc(4) = txtItem4Desc.Value
    ItemDesc(5) = txtItem5Desc.Value
    ItemDesc(6) = txtItem6Desc.Value
    ItemDesc(7) = txtItem7Desc.Value
    ItemDesc(8) = txtItem8Desc.Value
    ItemDesc(9) = txtItem9Desc.Value
    ItemDesc(10) = txtItem10Desc.Value

    strText = "<b><u>SAMPLE REQUEST</b></u>"
    strText = strText & "<br><br>"
    strText = strText & "<b><u>CUSTOMER INFORMATION</u></b>"
    strText = strText & "<br><br>"
    strText = strText & "<b>Customer: </b>" & CustNum & " | " & CustName
    strText = strText & "<br>"
    strText = strText & "<b>Contact: </b>" & ContNum & " | " & ContName
    strText = strText & "<br><br>"
    strText = strText & "<b><u>ITEM(S) REQUESTED</b></u> - (Item # / Quantity / Description)"
    strText = strText & "<br><br>"
    For i = 1 To 10
        If ItemQty(i) <> 0 Then
            strText = strText & "<b>Item " & i & "</b>" & " - " & ItemNum(i) & " | " & ItemQty(i) & " | " & ItemDesc(i)
            strText = strText & "<br>"
        End If
    Next i

    strText = strText & "<br>"
    strText = strText & "<b><u>SHIP TO INFORMATION</u></b>"
    strText = strText & "<br><br>"
    strText = strText & ShipName
    strText = strText & "<br>"
    strText = strText & ShipAdd1
    strText = strText & "<br>"
    strText = strText & ShipAdd2
    strText = strText & "<br>"
    strText = strText & ShipCity & ", " & ShipState & " " & ShipZip
    strText = strText & "<br>"
    strText = strText & "ATTN: " & ShipAttn
    strText = strText & "<br><br>"
    strText = strText & "<b><u>ADDITIONAL INFORMATION</u></b>"
    strText = strText & "<br><br>"
    strText = strText & OrderNotes
    strText = strText & "<br><br>"


    Set mai = Application.CreateItem(olMailItem)
    With mai
        .To = "geekamo@me.com"
        .CC = "geekamo@me.com"
        .Subject = "Sample (RQ) | " & CustNum & " - " & CustName & " | " & ContNum & " - " & ContName
        .HTMLBody = "<p style='font-family:calibri'>" & strText & "</p>"
    End With

End Sub

Open in new window

~ Geekamo
You can use some code like this.
Private Sub TextBox1_Change()
    If TextBox1.Text = "" Then
        TextBox1.BackColor = vbYellow
        TextBox1.BackColor = vbWhite
    End If
End Sub

Open in new window

However when the form is first opened, the background colour will be whatever is set at design time. To overcome this, you can call the change event code from the UserForm_Initialize event.
Private Sub UserForm_Initialize()
End Sub

Open in new window

GeekamoAuthor Commented:
@ GrahamSkan,

Thank you very much for the additional code.  I just implemented the color change code and it's working flawlessly!  I love this!  And I love that the code is simple to understand - great for me! :)
ColorLet me take a stab at the intitial code, I'll let you know how it turned out.

~ Geekamo
GeekamoAuthor Commented:
@ GrahamSkan,

This all has worked beautifully!  It is doing exactly what I wanted.  Thank you so much for your help!  I can't wait until I finish this project sometime this week - and it's thanks to all of you experts here! So excited!

~ Geekamo
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now