Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination: DataValidation auto appear + show all rows
any idea ? -- helmekki ------------------------------------------------------------------------ helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939 View this thread: http://www.excelforum.com/showthread...hreadid=378289 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination: DataValidation auto appear + show all rows
???.......... -- helmekki ------------------------------------------------------------------------ helmekki's Profile: http://www.excelforum.com/member.php...fo&userid=6939 View this thread: http://www.excelforum.com/showthread...hreadid=378289 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create 1 master list from a combination of rows and columns | Excel Discussion (Misc queries) | |||
selecting data from a combination of columns & rows. | Excel Discussion (Misc queries) | |||
Auto show data but without using Auto Filter | Excel Discussion (Misc queries) | |||
Why don't all my data rows show up in auto-filter? | Excel Worksheet Functions | |||
How to show all the possible combination of a set of numbers? | Excel Worksheet Functions |