Drop down box default

I have a series of cells with drop down boxes, in a column (say column D), all drop down boxes in this column of cells having the same choices, and each drop down box having four choices for the user. These drop down boxes were created using Data Validation>List, etc. as usual. However I want any of these cells (say cell D4)  to default to the first of the drop down choices automatically as soon as data is entered in a cell in the same row in a column next to the column of drop down boxes (say in cell B4).
For example, if the user enters a number, any number, in cell B4, I want cell D4 to show the text "Normal hours" (the first of the choices in the drop down list) without having to actually select from the list in the drop down choices of cell D4 (this being time-consuming as the normal option will be "Normal hours"). On the other hand, sometimes another choice from the drop down box will be required (say "Overtime" or "Holiday"), and that needs to be available to the user if he doesn't want the default option of "Normal hours".
How is this achieved?
Thanks.
Richard.
BlosMusicAsked:
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.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Follow these steps to achieve that....

Right click on your Sheet Tab --> View code --> Paste the code given below into the opened code window --> Close VBA Editor (code window) --> Save your workbook as Excel Macro-Enabled Workbook.

The following code will put Normal Hours in column D if a numeric value is entered in the corresponding row in column B provided the column D in that row has a drop-down list.
Normal Hours is assumed as a value from your drop-down list you want to insert by default in col. D, if it is something else, please change it in the code as per your requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Then
   If Target <> "" And IsNumeric(Target) Then
      On Error Resume Next
      n = Target.Offset(0, 2).SpecialCells(xlCellTypeSameValidation).Count
      If Err = 0 Then
         Target.Offset(0, 2) = "Normal Hours"
      End If
   Else
      Target.Offset(0, 2) = ""
   End If
End If
End Sub

Open in new window

0
BlosMusicAuthor Commented:
Thanks. In fact the columns turn out to be F and E respectively, rather than D and B. How do I adapt the code to allow for that? Sorry to be a pain, but I am still unfamiliar with VBA.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
If Target.Count > 1 Then Exit Sub
If Target.Column = 5 Then
   If Target <> "" And IsNumeric(Target) Then
      On Error Resume Next
      n = Target.Offset(0, 1).SpecialCells(xlCellTypeSameValidation).Count
      If Err = 0 Then
         Target.Offset(0, 1) = "Normal Hours"
      End If
   Else
      Target.Offset(0, 1) = ""
   End If
End If
End Sub

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

BlosMusicAuthor Commented:
Hi.
Doesn't seem to work.
I have attached the spreadsheet.
Regards,
Richard
Input-hours-from-timesheets--Sylvia.xlsm
0
Martin LissOlder than dirtCommented:
Can you supply the password to unlock the Timesheet entries sheet, or a workbook where that sheet isn't protected?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Change the code to this....
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
If Target.Count > 1 Then Exit Sub
If Target.Column = 5 Then
   If Target <> "" And IsNumeric(Target) Then
         Target.Offset(0, 1) = "Normal Hours"
   Else
      Target.Offset(0, 1) = ""
   End If
End If
End Sub

Open in new window

Input-hours-from-timesheets--Sylvia.xlsm
0
Martin LissOlder than dirtCommented:
In this workbook the value in column F will change to the first value in column F's validation list ("Working hours") if any cell in columns C,D,E, J or K is changed and the value in the row in column F is blank.
28804039.xlsm
0
BlosMusicAuthor Commented:
Un-passworded spreadsheet attached
Input-hours-from-timesheets--Sylvia.xlsm
0
Martin LissOlder than dirtCommented:
Did you try the workbook I attached?
0
BlosMusicAuthor Commented:
I did - just got back and opened it - and it works. I will check carefully tomorrow, but looks good. Thanks!
0
Martin LissOlder than dirtCommented:
The code in the workbook I attached has the name of the sheet and cell address of the first cell where the data validation formula points to "hard coded". If the code works then you could change it to this which gets the name of the sheet and the address of the first cell from the validation formula itself so it's more flexible.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTgt As Range
Dim strParts() As String
Dim strSheetName As String
Dim strFirstCell As String

If Target.Count > 1 Then Exit Sub
With ActiveSheet
    Set rngTgt = Union(.Range("C10:E" & .UsedRange.Rows.Count), .Range("J10:K" & .UsedRange.Rows.Count))
    If Not Intersect(Target, rngTgt) Is Nothing Then
        If .Cells(Target.Row, "F") = "" Then
            ' Get the name of the sheet where the data validation formula resides
            strSheetName = Split(.Cells(Target.Row, "F").Validation.Formula1, "'")(1)
            ' Get the address of the first cell in the validation formula
            strParts = Split(.Cells(Target.Row, "F").Validation.Formula1, "$")
            ' StrParts(1) will contain something like "L" and
            ' strParts(2) will contain something like "3:"
            strFirstCell = strParts(1) & Replace(strParts(2), ":", "")
            .Cells(Target.Row, "F") = Sheets(strSheetName).Range(strFirstCell)
        End If
    End If
End With
End Sub

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
BlosMusicAuthor Commented:
Everybody very helpful, even if it took a while! But that's my fault I think, for asking weird questions, and not being clear enough.
0
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest youincluding these two new ones.
An Experts Exchange Shortcut for the Truly Lazy (for Apple OS X)
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
1
BlosMusicAuthor Commented:
I cannot see any way to award the points that your contribution deserved. Excuse my being a bit slow, but I can't see the place where I click to access the points.
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
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.