Enable / Disable User Form Fields Based on Input.




I've designed a "large" user form that inputs form entries into Excel. This
has been tested and works. I now need a feature that enables / disables a
"Text" and "Check box" based on if the user has typed something in or if the
"check box" has been used.


Text Box 1 - "Do You have a phone number? If so, what is it?" (test entered
Check Box1 - "No"

This would be the simplest one on the form. The other one contains 2 or more
text input boxes and a check box.

The Code:

Here is what I have entered so far, it is only partially working. If I check
the box, I can not un-check it or sometimes can not enter text into the text

Private Sub txt3_Change()
If txt3.Value = True Then
chk1.Enabled = False
ElseIf txt3.Value = False Then
chk1.Enabled = True
chk1 = True
End If
End Sub

The FULL Code:

If anyone is interested in what the code for the whole UserForm I have
created looks like ... Here it is!

Private Sub UserForm_Initialize()
Dim myBoolean As Boolean
myBoolean = True
clientname.Value = ""
clientnum.Value = ""
clientdate.Value = ""
With cbo1
.AddItem "Acura"
.AddItem "Auto Center"
.AddItem "Bend"
.AddItem "Bought Elsewhere"
.AddItem "Chevrolet"
.AddItem "Client Care Center"
.AddItem "Client Care Issue"
.AddItem "Commercial/Fleet"
.AddItem "Ford - New"
.AddItem "Honda"
.AddItem "Kendall Auto Credit"
.AddItem "Lewiston"
.AddItem "Lexus"
.AddItem "Out of Market"
.AddItem "Sold"
.AddItem "Sold - Integration"
.AddItem "Ordered Vehicle"
.AddItem "Other Not Listed"
.AddItem "Toyota"
End With
cbo1.Value = ""
With cbo2
.AddItem "Other Not Listed"
.AddItem "Larry Schamber"
.AddItem "Brenda Brown"
.AddItem "Patrick Mielke"
.AddItem "Kim Derham"
.AddItem "David Foust"
.AddItem "Pete Yaskovic"
.AddItem "Terry Bazner"
.AddItem "Deb Hibler"
.AddItem "Carson Fields"
.AddItem "Bradd Borneman"
.AddItem "Richard Derrick"
.AddItem "Michael Gillingham"
.AddItem "Chris Bourn"
.AddItem "Patrice Woida"
.AddItem "Will Baas"
.AddItem "Tami Hartwig"
.AddItem "Paul Shelby Casey"
.AddItem "Breylan Deal"
.AddItem "Internet Dept at Kendall Toyota"
.AddItem "Chuck Spresser"
.AddItem "Larry Bowser"
.AddItem "Rod Bay"
.AddItem "Dave Donaldson"
.AddItem "Jed Tunick"
.AddItem "Marc Chenoweth"
.AddItem "Monte Love"
.AddItem "Orphanclient Branch07"
.AddItem "Arlin Lemmon"
.AddItem "Bud Buller"
.AddItem "Carolyn Turnipseed"
.AddItem "Dorman Harry"
.AddItem "Duane Farnham"
.AddItem "Hans Moi"
.AddItem "Jeff Liggett"
.AddItem "Jim Blewett"
.AddItem "Joe Kirshner"
.AddItem "Josh Schooley"
.AddItem "Julie Smith"
.AddItem "Kelly Moninger"
.AddItem "Kevin Ro"
.AddItem "Kirk Powell"
.AddItem "Martin Neustel"
.AddItem "Mike Perkins"
.AddItem "Mike Vargas"
.AddItem "Nathan Winegardner"
.AddItem "Oliver Peck"
.AddItem "Orphanclient Branch01"
.AddItem "Orphanclient Branch05"
.AddItem "Rob Sandmeyer"
.AddItem "Steven Roemer"
.AddItem "Sunnie Koh"
.AddItem "Tamara Richards"
.AddItem "Tracy Beeman"
End With
cbo2.Value = ""
With cbo3
.AddItem "Other Not Listed"
.AddItem "Vehiclemart"
.AddItem "Costco Auto Program"
.AddItem "Reynolds Web Solutions"
.AddItem "AutoWeb"
.AddItem "Dealix"
.AddItem "Autobytel"
.AddItem "The Cobalt Group, Inc."
.AddItem "BAT Quick Quotes"
.AddItem "FD"
.AddItem "Vehix.com"
.AddItem "GM 3rd Party"
.AddItem "Autotrader.com"
.AddItem "FVFD"
.AddItem "KAC LMCC"
.AddItem "Honda Quick Quote"
.AddItem "Kendall Auto Credit"
.AddItem "Unknown"
.AddItem "FD -USED"
.AddItem "GMDL-DC Dealer New Vehicle - General"
.AddItem "BAT Specials(English)"
.AddItem "Subaru Of America"
.AddItem "Honda Website"
.AddItem "Autobytel AHM"
.AddItem "Hyundai Quick Quote"
.AddItem "Leadsclusive"
.AddItem "Toyota Configurator"
.AddItem "Toyota Dealer Locator (English)"
.AddItem "Acura Website"
.AddItem "Chevrolet"
.AddItem "CarFax"
.AddItem "Acura Quick Quote"
.AddItem "DC New Quote Ford"
.AddItem "KBB.com Lead via AHM"
.AddItem "DC New Contact Sales"
.AddItem "Cobalt Web Site"
.AddItem "Scion Configurator"
.AddItem "Brochure Request"
.AddItem "rey13466"
.AddItem "FD -ATC"
.AddItem "Internet"
.AddItem "Hand Raiser - Web"
.AddItem "GMDL-DU Dealer Used Vehicle - General"
.AddItem "Scion Dealer Locator"
.AddItem "GMDL-DP Dealer Price Quote"
.AddItem "ReynoldsTest"
.AddItem "Vehix.com Web Site"
.AddItem "GMBuyPower"
.AddItem "Consumer Lead Inquiry from TCUV"
.AddItem "Referral"
.AddItem "Autobytel.com"
.AddItem "Rastar - Off Lease - AHM"
.AddItem "Prospect Lead from Seattle Drive Days"
.AddItem "Kendall Website"
End With
cbo3.Value = ""
With cbo4
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "More than 10"
End With
cbo4.Value = ""
opt1 = False
opt2 = False
opt3 = False
opt4 = False
opt5 = False
opt6 = False
opt7 = False
opt8 = False
opt9 = False
opt10 = False
opt11 = False
opt12 = False
opt13 = False
opt14 = False
opt15 = False
opt16 = False
chk1 = False
chk2 = False
txt2.Value = ""
txt3.Value = ""
txt4.Value = ""
txt5.Value = ""
txt6.Value = ""
txt7.Value = ""
txt8.Value = ""
txt9.Value = ""
txt10.Value = ""
txt11.Value = ""
txt12.Value = ""
txt13.Value = ""
txt14.Value = ""
txt15.Value = ""
txt16.Value = ""
txt17.Value = ""
txt18.Value = ""
txt19.Value = ""
txt20.Value = ""
txt21.Value = ""
txt22.Value = ""
txt23.Value = ""
txt24.Value = ""
txt25.Value = ""
txt26.Value = ""
txt27.Value = ""
txt28.Value = ""
txt29.Value = ""
txt30.Value = ""
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub CommandButton1_Click()
Call UserForm_Initialize
End Sub
Private Sub CommandButton2_Click()
ActiveWorkbook.Sheets("Email Lead Review Data").Activate
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = clientname.Value 'Client Name Field
ActiveCell.Offset(0, 1) = clientnum.Value 'Client Number Field
ActiveCell.Offset(0, 2) = clientdate.Value 'Date Created Field
ActiveCell.Offset(0, 3) = cbo1.Value 'What is the current status
of this prospect?
ActiveCell.Offset(0, 4) = cbo2.Value 'Who was this prospect
assigned to?
ActiveCell.Offset(0, 5) = cbo3.Value 'What was the source of this
ActiveCell.Offset(0, 6) = cbo4.Value 'Number of time Clients Name
is in CM?
ActiveCell.Offset(0, 7) = txt2.Value 'Date First Appeared
If opt1 = True Then 'Does the Client Profile
Contain a mailing address? (YES or NO)
ActiveCell.Offset(0, 8).Value = "YES"
ElseIf opt2 = True Then
ActiveCell.Offset(0, 8).Value = "NO"
End If
If txt3 = True Then 'Does the Client Profile
Contain a Phone number?
ActiveCell.Offset(0, 9) = txt3.Value
ElseIf chk1 = True Then
ActiveCell.Offset(0, 9).Value = "NO"
End If
If txt4 = True Then 'Is this Client a prior
ActiveCell.Offset(0, 10) = txt4.Value
ElseIf txt5 = True Then
ActiveCell.Offset(0, 11) = txt5.Value
ElseIf chk2 = True Then
ActiveCell.Offset(0, 10).Value = "NO"
End If
ActiveCell.Offset(0, 12) = txt6.Value 'How many Prospect Cards for
this Client?
ActiveCell.Offset(0, 13) = txt7.Value
ActiveCell.Offset(0, 14) = txt8.Value
ActiveCell.Offset(0, 15) = txt9.Value
ActiveCell.Offset(0, 16) = txt10.Value
ActiveCell.Offset(0, 17) = txt11.Value 'Time lead received by
ActiveCell.Offset(0, 18) = txt12.Value
ActiveCell.Offset(0, 19) = txt13.Value
ActiveCell.Offset(0, 20) = txt14.Value 'Time lead received by CM:
ActiveCell.Offset(0, 21) = txt15.Value
ActiveCell.Offset(0, 22) = txt16.Value
ActiveCell.Offset(0, 23) = txt17.Value 'Time lead assigned to
Primary Salesperson:
ActiveCell.Offset(0, 24) = txt18.Value
ActiveCell.Offset(0, 25) = txt19.Value
ActiveCell.Offset(0, 26) = txt20.Value 'Time lead responded to by
Personal Email:
ActiveCell.Offset(0, 27) = txt21.Value
ActiveCell.Offset(0, 28) = txt22.Value
ActiveCell.Offset(0, 29) = txt23.Value 'Time lead responded to by
Phone Call:
ActiveCell.Offset(0, 30) = txt24.Value
ActiveCell.Offset(0, 31) = txt25.Value
If opt3 = True Then 'Did the first personal
email responce address the clients inquiry?
ActiveCell.Offset(0, 32).Value = "YES"
ElseIf opt4 = True Then
ActiveCell.Offset(0, 32).Value = "NO"
End If
If opt5 = True Then 'Did the first personal
email responce address the clients inquiry?
ActiveCell.Offset(0, 33).Value = "Automated"
ElseIf opt6 = True Then
ActiveCell.Offset(0, 33).Value = "Personal"
End If
If opt7 = True Then 'Was there contact with the
ActiveCell.Offset(0, 34).Value = "YES"
ElseIf opt8 = True Then
ActiveCell.Offset(0, 34).Value = "NO"
End If
If opt9 = True Then 'Was an appointment set?
ActiveCell.Offset(0, 35).Value = "YES"
ElseIf opt10 = True Then
ActiveCell.Offset(0, 35).Value = "NO"
End If
If opt11 = True Then 'Was the appointment kept?
ActiveCell.Offset(0, 36).Value = "YES"
ElseIf opt12 = True Then
ActiveCell.Offset(0, 36).Value = "NO"
End If
If opt13 = True Then 'Did any outgoing email
contain a picture of a vehicle?
ActiveCell.Offset(0, 37).Value = "YES"
ElseIf opt14 = True Then
ActiveCell.Offset(0, 37).Value = "NO"
End If
If opt15 = True Then 'Were there significant
notes in the file from the primary salesperson?
ActiveCell.Offset(0, 38).Value = "YES"
ElseIf opt16 = True Then
ActiveCell.Offset(0, 38).Value = "NO"
End If
ActiveCell.Offset(0, 39) = txt26.Value 'Total number of personal
emails received from the client:
ActiveCell.Offset(0, 40) = txt27.Value 'Total number of emails sent
to client:
ActiveCell.Offset(0, 41) = txt28.Value 'Total number of Phone call
ActiveCell.Offset(0, 42) = txt29.Value 'Analysis completed by:
ActiveCell.Offset(0, 43) = txt30.Value 'Date:
End Sub

Private Sub txt3_Change()
If txt3.Value = True Then
chk1.Enabled = False
ElseIf txt3.Value = False Then
chk1.Enabled = True
chk1 = True
End If
End Sub

Thank you:

Thank you in advance for any assistance. I only wished my skills were a bit
better as something the seems so simple would not perplex me so much.




Vasant Nanavati

Will this combination work?

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
TextBox1 = vbNullString
End If
End Sub

Private Sub TextBox1_Change()
If Len(TextBox1) > 0 Then
CheckBox1.Value = False
CheckBox1.Value = True
End If
End Sub

It's not surprising that if you disable the CheckBox, you can't uncheck it.

"Mike "MrMunka" Gillingham"

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question