ExcelBanter - Combination: DataValidation auto appear + show all rows

Hi there

I use this code to make my Drop down Data validation drops
automatically,
the code :

Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo NoValidation
If Target.Validation.InCellDropdown Then
Application.SendKeys "%{Up}"

NoValidation:
Err.Clear
End If

End Sub
--------------------

In addtion to this i use another code to make the drop down data
validation show all the visible list rows of the Data Validation list.

the code :

Code:
--------------------
Dim oDpd As Object
Dim sFml1
Dim prvTarget As Range

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const dFixedPos As Double = "0.8"
Const dFixWidth As Double = "12.0" 'Change here to change WIDTH of the DropDown
Dim vld As Validation
Dim lDpdLine As Long

If Not prvTarget Is Nothing Then
If Not oDpd Is Nothing Then
If oDpd.Value = 0 Then
' prvTarget.Value = vbNullString
Else
prvTarget.Value = Range(Mid(sFml1, 2)).Item(oDpd.Value)
End If
Set prvTarget = Nothing
End If
End If

On Error Resume Next
oDpd.Delete
sFml1 = vbNullString
Set oDpd = Nothing
On Error GoTo 0

If Target.Count 1 Then
Set oDpd = Nothing
Exit Sub
End If

Set vld = Target.Validation
On Error GoTo Terminate
sFml1 = vld.Formula1
On Error GoTo 0

Set prvTarget = Target

lDpdLine = Range(Mid(sFml1, 2)).Rows.Count

With Target
Set oDpd = ActiveSheet.DropDowns.Add( _
.Left - dFixedPos, _
.Top - dFixedPos, _
.Width + dFixWidth + dFixedPos * 2, _
.Height + dFixedPos * 2)
End With
With oDpd
.ListFillRange = sFml1
.DropDownLines = lDpdLine
.Display3DShading = True

End With
Terminate:

End Sub
--------------------

*The Need:
I tried to combine both codes to make my drop down data validation
appears automatically and shows all the visible list rows of the Data
Validation list ?

but without success.................couold you pls help in the case*

--
helmekki

------------------------------------------------------------------------
helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939
View this thread: http://www.excelforum.com/showthread...hreadid=378289