Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Enable / Disable User Form Fields Based on Input.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Enable / Disable User Form Fields Based on Input.
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,080
Default Enable / Disable User Form Fields Based on Input.
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"
wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Enable / Disable User Form Fields Based on Input.
On Wednesday, June 20, 2007 at 7:17:36 PM UTC-5, Mike "MrMunka" Gillingham wrote:
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

Hello People-

I am a sales director for a corporate dealership and trying to understand what the manufacturer"s" are doing sending us junk leads when I stumbled across your decade old thread after I googled GMDL Dealer price quote (a lead Chevrolet sends us that they charge for), as I was trying to find why General Motors, and even Toyota, both through their 3rd party lead provider billing sends us junk leads and charge the dealership. The "leads" will contain false phone, false email, false address, false email, false comments... like never having submitted a lead. Two questions 1) does code verify information to be correct, 2) can it track the fact that not one 3rd party lead provided were in the market?
I ask because greedy people are getting people fired over low closing percentages (lost a great salesman and good friend to suicide when his he was terminated for having a 2% closing rato, corporate wants us at 25%, national average is 8 or 12 percent, and they only count electronically submitted so called leads) . Where do these GM Onesource Third Party leads come from leads come from?
I
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Disable and enable dropdown combobox(Form Control) Vinod[_2_] Excel Discussion (Misc queries) 0 November 6th 07 07:30 PM
Trying to select a specific range based on the time value of user form input Jitranijam New Users to Excel 8 November 15th 06 12:52 AM
Enable/Disable Macros dialogue box when user opens Excel David Excel Discussion (Misc queries) 2 August 23rd 06 02:21 PM
Detecting Ctrl-Tabs in User Form input fields? Don Wiss Excel Programming 1 February 23rd 05 01:17 AM
How do I have excel not to prompt user to disable or enable a micro victor Excel Programming 2 January 9th 04 03:28 AM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement