Added some functionality to a project I am working on and now I'm getting the above error if I leave the project open over night.
I added a userform that allows the user to change the default save directory. Anyone see the problem?
userform code:
Private Sub UserForm_Initialize()
Dim myString As String
myString = Range("ctrWorkDir")
If myString = "" Then
myString = ActiveWorkbook.Path
End If
dirBox.Text = myString
End Sub
Private Sub editButton_Click()
Dim myString As String
Me.Hide
myString = GetFolder(dirBox.Text)
dirBox.Text = myString
Me.Show
End Sub
Private Sub saveButton_Click()
Dim myRange As Range
Set myRange = Range("ctrWorkDir")
If Not FolderExists(dirBox.Text) Then
MsgBox "Invalid folder, try again."
Exit Sub
End If
Me.Hide
myRange = dirBox.Text
Load formOptions
With formOptions
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
Unload dirEdit
End Sub
Private Sub cancelButton_Click()
Dim myString As String
myString = Range("ctrWorkDir")
Me.Hide
If myString = "" Then
myString = ActiveWorkbook.Path
End If
dirBox.Text = myString
Load formOptions
With formOptions
.StartUpPosition = 0
.Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
.Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
.Show
End With
Unload dirEdit
End Sub
"folder picker"
Function GetFolder(InitDir As String) As String
Dim fldr As FileDialog
Dim sItem As String
sItem = InitDir
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
If Right(sItem, 1) <> "\" Then
sItem = sItem & "\"
End If
.InitialFileName = sItem
If .Show <> -1 Then
sItem = InitDir
Else
sItem = .SelectedItems(1)
End If
End With
GetFolder = sItem
Set fldr = Nothing
End Function
folder validation
Option Explicit
Private Declare Function PathFileExists Lib "shlwapi" Alias "PathFileExistsA" (ByVal pszPath As String) As Long
Private Declare Function PathIsDirectory Lib "shlwapi" Alias "PathIsDirectoryA" (ByVal pszPath As String) As Long
Public Function FileExists(pstrFile As String) As Boolean
FileExists = (PathFileExists(pstrFile) = 1)
If FileExists Then FileExists = (PathIsDirectory(pstrFile) = 0)
End Function
Public Function FolderExists(pstrFolder As String) As Boolean
FolderExists = (PathFileExists(pstrFolder) = 1)
If FolderExists Then FolderExists = (PathIsDirectory(pstrFolder) <> 0)
End Function
Thanks in advance!
Bookmarks