I have a workbook that on clicking 'Submit' on a User Form opens another workbook and places data from the User Form into the as yet unopened workbook. Some time back someone on here kindly helped me with some coding to do just that and it works great. I have now added another User Form and want this to update to the workbook as previously. I've used the exact same coding and the file reference is correct but when I click 'Submit' I get an error message saying "FileName \ Path Not Found". I've put the code below, it's a straight lift but I've had to change a few bits to protect the company details, I've replaced it with "XXXX".
Any help would be greatly appreciated.
Any help would be greatly appreciated.
Code:
Private Sub CMD_Update_Click()
'Check if Master Workbook Is In Use - Also needs additional code in (Function FileInUse(ByVal FileName As String) As Boolean) below!!
Application.ScreenUpdating = False 'Stops screen from showing during check
Dim Response As VbMsgBoxResult
Dim FullFileName As String
FullFileName = "\\XXXX\Returns v.2.0.xlsx"
If Dir(FullFileName, vbDirectory) <> vbNullString Then
Do
If FileInUse(FullFileName) Then
Response = MsgBox("Database in use." & Chr(10) & _
"Do You Want To Retry?", 37, "File In Use")
'cancel pressed
If Response = 2 Then Exit Sub
Else
Exit Do
End If
Loop
Else
MsgBox FullFileName & Chr(10) & "FileName \ Path Not Found", 48, "Not Found"
Exit Sub
End If
'Adds new workplace to database
Set wbk = Workbooks.Open("\\XXXX\Returns v.2.0.xlsx", WriteResPassword:="XXXX", IgnoreReadOnlyRecommended:=True) 'Sets Workbook
Dim Found As Range
Cancel = 0
If Me.CboNewWorkplace.value = "" Then
Cancel = 1
CboNewWorkplace.SetFocus
End If
If Cancel = 1 Then
MsgBox "Enter a new workplace", vbCritical, "XXXX"
Exit Sub
End If
Set Found = Sheets("Authorised Users").Range("A:A").Find(What:=Me.TxtPIN.value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Found Is Nothing Then
MsgBox "PIN Number Not Found In Database", vbCritical, "XXXX"
Else
Sheets("Authorised Users").Cells(Found.Row, Columns.Count).End(xlToLeft).Offset(0, -1).value = Me.CboNewWorkplace.value
End If
'Closes master data workbook
wbk.Save
wbk.Close
'Clear UsrFrmNewWorkplace entries
TxtPIN.value = ""
TxtSurname.value = ""
TxtCurrentWorkplace.value = ""
CboNewWorkplace.value = ""
'Unload UsrFrmNewWorkplace
Unload Me
MsgBox " Your workplace has now been amended as requested." & vbCr & _
"" & vbCr & _
"Please check when you next use the system to ensure your workplace is correct", , "XXXX"
'Re-Load User Form
UsrFrmUserConsole.Show
End Sub
'Part of checking if Master Workbook is in use
Function FileInUse(ByVal FileName As String) As Boolean
End Function
On Error Resume Next
Open FileName For Binary Access Read Lock Read As #1
Close #1
FileInUse = CBool(Err.Number > 0)
On Error GoTo 0
End Function
