PDA

View Full Version : Solved: Folder picker to output path chosen



sassora
12-03-2008, 06:51 AM
Hi,

I am using the folder picker in Excel to output the path selected on the spreadsheet. This seems to work fine most of the time but if I find a few paths in succession then. A cell way down the page in column D is activated. Any ideas why?


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If user is double clicking in column D then run GetFolder macro
If Target.Column = 4 Then GetFolder
End Sub

Sub GetFolder()
'Ensure cells are not "active"
Range("D" & ActiveCell.Row).Select
ChDir "C:\"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
Range("E" & ActiveCell.Row).Value = .SelectedItems(1)
Range("E" & ActiveCell.Row).Select
ActiveCell.Hyperlinks.Add ActiveCell, .SelectedItems(1)
End If
Range("E" & ActiveCell.Row).Select
End With

End Sub

Bob Phillips
12-03-2008, 07:10 AM
Not seeing it, but maybe all that selecting is throwing it



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'If user is double clicking in column D then run GetFolder macro
If Target.Column = 4 Then Call GetFolder(Target)
Cancel = True
End Sub

Sub GetFolder(ByRef Target As Range)
'Ensure cells are not "active"
ChDir "C:\"
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
Target.Offset(0, 1).Value = .SelectedItems(1)
Target.Hyperlinks.Add Target.Offset(0, 1), .SelectedItems(1)
End If
End With

End Sub

sassora
12-03-2008, 07:21 AM
It's because I am double clicking on the border of the cell which is then doing autofill which is hard to notice since all of the cells in the column are the same down to about row 150. Is it possible to stop that from happening?