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
LVL 1
GeekamoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
GrahamSkanRetiredCommented:
OK. Thanks for keeping us in the picture. Good luck
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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:

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

Field Names:

txtCustNum
txtCustName
txtContNum
txtContName

txtItem1Num
txtItem1Qty
txtItem1Desc

txtShipName
txtShipAdd1
txtShipCity
txtShipState
txtShipZip

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()

'CLEAN UP

    Unload SampleRequest

'CREATE VARIABLES

    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

'POPULATE VARIABLES

    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
    
'COMPILE EMAIL BODY

    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>"

'EMAIL OUTPUT

    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>"
        .Display
    End With

End Sub

Open in new window


~ Geekamo
0
GrahamSkanRetiredCommented:
You can use some code like this.
Private Sub TextBox1_Change()
    If TextBox1.Text = "" Then
        TextBox1.BackColor = vbYellow
    Else
        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()
    TextBox1_Change
End Sub

Open in new window

0
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
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.