Forcing an entry

Butters

New Member
Joined
Jun 24, 2008
Messages
16
Hi Everyone

I am not sure how I could do this so I was hoping to get some assistance.

I have an Excel sheet which serves as a data input form. Certain cells are restricted based upon a pull down list generated using the "Validation" option.

I would like to set something up whereby if the user selects a certain choice from a pull down then they are forced to also enter a value in another cell right next to it.

For example, say you are entering information on cars and your first column pull down menu has "Ford, GM, Toyota, Honda" etc. as choices, if a user selects, say Honda, I would like to force the user to enter something in the next column using a pull down menu like "Civic, Accord, CRV, Odyssey" etc. otherwise I'd like to leave it blank.

Any assistance with this would be greatly appreciated! Thanks!!:)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You could put code in the Worksheet_Change event which would fire when a particular cell value [your drop down] changes. You could even code it so that the cursor moves to then next cell over and populate that cell with a new drop down list of car models based on the car manufacturer chosen in the first cell [i.e. the second cell's validation list would depend on what mfgr was chosen from the first drop down list].
 
Upvote 0
Hi, Butters,
WELCOME to the BOARD!!!!!

take a look at this "famous" thread
(the link is already pointing to the "most relevant post")
http://www.mrexcel.com/board2/viewtopic.php?t=40&start=26
in the mean time Aladin wrote something "new"
http://www.mrexcel.com/board2/viewtopic.php?t=267483
see also this explanation
http://www.contextures.com/xlDataVal02.html
and
http://www.mrexcel.com/board2/viewtopic.php?t=15030
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

kind regards,
Erik
 
Upvote 0
Welcome to the Board!

Take a look at Juan Pablo Gonzalez/Aladin Akyurek's post on Dependent Data Validation.

You can't force a user to make an entry per se, but this method will populate the relevant selection information.

Hope that helps,
 
Upvote 0
Here's an example of what I was referring to in msg #2. This assumes you have a manufacturer's drop down list in cell A2 containing "Chevy,Ford,Honda".

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A2"), Target) Is Nothing Then
        Range("B2").Select
        Select Case Range("A2").Value
            Case "Chevy"
                Range("B2").Validation.Delete
                Range("B2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                           xlBetween, Formula1:="Camaro,Monte Carlo,Corvette"
            Case "Ford"
                Range("B2").Validation.Delete
                Range("B2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                           xlBetween, Formula1:="Escape,Focus,Mustang"
            Case "Honda"
                Range("B2").Validation.Delete
                Range("B2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                           xlBetween, Formula1:="Accord,Civic,Pilot"
        End Select
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top