Data Validation
 

I would like to have my drop down box in excel read D1a - cleaning, but when
I click on it, only D1a appears in the cell. Is there any way of
accomplishing this task. I am a novice to excel and have never used VBA so I
may need a detailed explantation.

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
Dim nPos As Long
With Range("F1")
nPos = InStr(1, .Text, "-")
If nPos Then
On Error Resume Next
Application.EnableEvents = False
.Value = Trim(Left(.Text, nPos - 1))
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

In many versions of XL, selecting a value from a Validation dropdown
doesn't fire any events, so you may need to enter

=F1

in an out-of-the-way cell (which could be hidden).

Of course, change F1 to your desired cell.


In article ,
"KimStarbase" wrote:

I would like to have my drop down box in excel read D1a - cleaning, but when
I click on it, only D1a appears in the cell. Is there any way of
accomplishing this task. I am a novice to excel and have never used VBA so I
may need a detailed explantation.