Enable / Disable User Form Fields Based on Input.

[Mike MrMunka Gillingham]

Problem:

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.

Example:

Text Box 1 - "Do You have a phone number? If so, what is it?" (test entered
here)
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
field.

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 "EDMUNDS DEALER DIRECT"
.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 = ""
clientname.SetFocus
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
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'
'BEGIN 1ST SECTION
'
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
prospect?
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
Customer?
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
'
'BEGIN 2ND SECTION
'
ActiveCell.Offset(0, 17) = txt11.Value 'Time lead received by
provider:
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
'
'BEGIN 3RD SECTION
'
If opt7 = True Then 'Was there contact with the
Client?
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
activities:
ActiveCell.Offset(0, 42) = txt29.Value 'Analysis completed by:
ActiveCell.Offset(0, 43) = txt30.Value 'Date:
Range("A2").Select
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.

-Mike

 

[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
Else
CheckBox1.Value = True
End If
End Sub

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

"Mike "MrMunka" Gillingham"