L
leerem
Hi Mike,
You gave me the code as attached below for listing the
contents of a folder:
Private Sub Worksheet_Activate()
ListBox1.Clear
MyPath = "C:\"
MyName = Dir$(MyPath & "*.xls") 'only looks for xl files
With ListBox1
Do While MyName <> ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .XLS from end of name
..AddItem MyName
MyName = Dir
Loop
End With
End Sub
The List box will need to be generated and activated only when the user
selects Sheet1.Range("D7")
Could this be added to an existing routine below - and if so how would I go
about doing it.......
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrow As Integer
If Target.Cells.Count > 1 Then
Exit Sub
End If
Lastrow = Sheets("sheet1").Cells(Rows.Count, "Y").End(xlUp).Row
If Not Application.Intersect(Range("I7"), Target) Is Nothing Then
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Y$3:$Y" & Lastrow
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
' Enter new code here
End Sub
You gave me the code as attached below for listing the
contents of a folder:
Private Sub Worksheet_Activate()
ListBox1.Clear
MyPath = "C:\"
MyName = Dir$(MyPath & "*.xls") 'only looks for xl files
With ListBox1
Do While MyName <> ""
MyName = Left(MyName, Len(MyName) - 4) 'removes .XLS from end of name
..AddItem MyName
MyName = Dir
Loop
End With
End Sub
The List box will need to be generated and activated only when the user
selects Sheet1.Range("D7")
Could this be added to an existing routine below - and if so how would I go
about doing it.......
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Lastrow As Integer
If Target.Cells.Count > 1 Then
Exit Sub
End If
Lastrow = Sheets("sheet1").Cells(Rows.Count, "Y").End(xlUp).Row
If Not Application.Intersect(Range("I7"), Target) Is Nothing Then
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$Y$3:$Y" & Lastrow
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
' Enter new code here
End Sub