Directory Validation

Posted by Ben on April 14, 2001 5:17 AM

Can validation be used on a cell so that a valid directory name can only be entered? e.g. Ben but c:\windows\

Also, is it possible to write a macro that creates the directory in the cell if isn't there, but doesn't if it is?

Thanks

Posted by Dax on April 14, 2001 6:28 AM

Ben,

You can do both. You could use the FileSystem Object. Right click on the sheet tab and click View Code. In the Tools, References menu option you'll need to add a reference to the Microsoft Scripting Runtime library. Then enter something like this:-

Dim FSObj As New Scripting.FileSystemObject

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TypePath As String, Answer As Long

'Will only expect path names in column 1 (A). Change
'this value for a different column e.g. D=4
If Target.Column &LT;&GT; 1 Then Exit Sub

TypePath = Target.Value
If TypePath = "" Then Exit Sub
If FSObj.FolderExists(TypePath) = False Then
Answer = MsgBox("This folder does not exist. Would" & vbCrLf & _
"you like to create it?", vbYesNo, "Folder doesn't exist")
If Answer = vbYes Then
FSObj.CreateFolder TypePath
End If
End If

End Sub

Hope this helps,
Dax.