Hi folks,
I am stumped.
I have a workbook that has been working fine for many months and has now thrown up an error.
The situation is that a userform opens a central file and saves a new record/updates an existing record then saves the file.
The file path/name is hard coded into the VBA.
The error is that even though the file opened is correct and the data copied over is correct, when I come to the activeworkbook.save line, it defaults to saving the file into my documents.
Has anyone ever encountered this issue?
I thought it was a variable that needed setting to nothing however this has not solved the issue.
Code
Sub submit_record()
Dim key As Long
Dim answer As Integer
Dim trans_date As Date
Dim Cancel As Integer
Dim CloseMode As Integer
Application.DisplayAlerts = False
Do
tempstrD = InputBox("Please enter the Transaction Date")
trans_date = tempstrD
Loop While (Len(tempstrD) = 0)
If UserForm1.booking_type_box.Value = "Hotel" Then
Do
tempStr = InputBox("Please enter the Conferma Reference")
Conferma_ref = tempStr
Loop While (Len(tempStr) = 0)
Else
End If
answer = MsgBox("Is there a Freshdesk ticket associated with this payment?", vbYesNo + vbQuestion, "Freshdesk Ticket?")
If answer = vbYes Then
Do
tempStr = InputBox("Please enter the associated Freshdesk ticket number")
ticket_id = tempStr
Loop While (Len(tempStr) = 0)
Else
End If
Workbooks.Open Filename:="C:\Example\CENTRAL CC LOG 2016.xlsx", Password:="Password1"
Sheets("2016").Activate
Sheets("2016").Range("A4").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
Debug.Print ActiveCell.Row
key = ActiveCell.Offset(-1, 0).Value + 1
ActiveCell = key
ActiveCell.Offset(0, 1) = trans_date
ActiveCell.Offset(0, 2) = UserForm1.supplier_name_box
ActiveCell.Offset(0, 3) = UserForm1.supplier_ref_box
ActiveCell.Offset(0, 4) = UserForm1.UK_amount_box
ActiveCell.Offset(0, 5) = UserForm1.Int_amount_box
ActiveCell.Offset(0, 6) = UserForm1.currency_type_box
ActiveCell.Offset(0, 7) = UserForm1.traveller_name_box
ActiveCell.Offset(0, 8) = UserForm1.trav_Date_box
ActiveCell.Offset(0, 9) = UserForm1.Account_box
ActiveCell.Offset(0, 10) = UserForm1.trip_num_box
ActiveCell.Offset(0, 12) = UserForm1.consultant_name
ActiveCell.Offset(0, 13) = UserForm1.Booker_name_box
ActiveCell.Offset(0, 14) = UserForm1.reason_box
ActiveCell.Offset(0, 11) = Conferma_ref
ActiveCell.Offset(0, 18) = UserForm1.cards_box
ActiveCell.Offset(0, 15) = ticket_id
ActiveCell.Offset(0, 16) = UserForm1.booking_type_box
ActiveCell.Offset(0, 17) = Format(trans_date, "MMM")
Range("A1").Select
'EmptyClipboard
Debug.Print ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
ActiveWorkbook.Save
ActiveWorkbook.Close
'Application.DisplayAlerts = True
End Sub
Display More
All help gratefully appreciated.
Cheers
Dave