I have a purchase order generator which previously worked without a problem and all different computers at work all running different excel versions from 2007 (on Vista), 2010 (W7) and 2013 (W7). For some reason, it has now stopped working on anything other than 2013. I have some ActiveX combo boxes where you select certain suppliers and job nubers etc from the dropdown list, then a 'generate' button makes the purchase order. On the computers where it doesn't work, none of the ActiveX controls function. The dropdown list doesn't work. I can't manually run the code or anything. It just doesn't function.
This started when another user (2010) used the document to create a new order. He generated it and it all worked fine. Then the program stopped working on my computer (2013). It still worked fine on his. The only way I could resurrect it was to copy all the code and make an entirely new excel document. It was like it computer corrupted it - but it still worked on his computer. So now he isn't allowed to make orders any more as it corrupts the document for everyone but him. But then thinking all was well, the document no longer works on any other computer than one with 2013, except his on 2010! I even tried using old backups (which I wrote on my computer only so no-one has ever opened them but me) and those don't work on anyone else' computer either but ones in 2013.
It's all very strange and makes no sense to me. Only solution I've got right now is to upgrade everyone to excel 2013 which isn't ideal and is expensive.
Suggestions?
Cheers
Private Sub GenerateREQ_Click()
ThisWorkbook.Save
Sheets("REQ List").Select
ActiveSheet.Unprotect
' Specify Variables - rename to cbUser etc (combo box)
User = UserList.Value
Supplier = SupplierList.Value
Job = JobList.Value
' Activate REQ List, Generate New Number and populate list
Sheets("REQ List").Activate
ActiveSheet.Range("B65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell = "REQ" & ActiveCell.Offset(0, -1)
ReqNo = ActiveCell
Hyperlinks.Add Anchor:=ActiveCell, _
Address:=ReqNo & ".xlsx", _
TextToDisplay:=ReqNo
ActiveCell.Offset(0, 1).Select
ActiveCell = "J" & Job
ActiveCell.Offset(0, 1).Select
ActiveCell = Supplier
ActiveCell.Offset(0, 1).Select
ActiveCell = Date
ActiveCell.Offset(0, 1).Select
ActiveCell = User
ActiveCell.Offset(0, 1).Select
ActiveCell = User & "-" & Job & "-" & ActiveCell.Offset(0, -6)
PoNo = ActiveCell
' Set Supplier Contact Details
Sheets("Supplier").Select
ActiveSheet.Range("A2:A1000").Find(Supplier).Select
ActiveCell.Offset(0, 1).Select
Contact = ActiveCell
ActiveCell.Offset(0, 1).Select
Address1 = ActiveCell
ActiveCell.Offset(0, 1).Select
Address2 = ActiveCell
' Set User Details
Sheets("User").Select
ActiveSheet.Range("B2:B1000").Find(User).Select
ActiveCell.Offset(0, -1).Select
UserName = ActiveCell
' Fill PO sheet
Worksheets("REQ Template").Range("E5") = "Attn. " & Contact
Worksheets("REQ Template").Range("E6") = Supplier
Worksheets("REQ Template").Range("E7") = Address1
Worksheets("REQ Template").Range("E8") = Address2
Worksheets("REQ Template").Range("E10") = "Attn. " & UserName
Worksheets("REQ Template").Range("D15") = ReqNo
Worksheets("REQ Template").Range("F15") = "J" & Job
Worksheets("REQ Template").Range("I11") = PoNo
Worksheets("REQ Template").Range("I15") = Date
' Fill GST Box
If GSTBox.Value = True Then
Worksheets("REQ Template").Range("K43").Formula = "=(K41+K42)*0.1"
Else: Worksheets("REQ Template").Range("K43").Formula = "=0"
End If
' Generate New Document
' On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = True
TryAgain:
Flname = ReqNo
If Flname <> "" Then
Set NewWkbk = Workbooks.Add
ThisWorkbook.Sheets("REQ Template").Copy Before:=NewWkbk.Sheets(1)
NewWkbk.SaveAs ThisWorkbook.Path & "\" & Flname
If Err.Number = 1004 Then
NewWkbk.Close
MsgBox "File Name Not Valid" & vbCrLf & vbCrLf & "Try Again."
GoTo TryAgain
End If
ActiveWorkbook.Close
End If
'Clear PO Sheet
Worksheets("REQ Template").Range("E5:E10").ClearContents
Worksheets("REQ Template").Range("D15").ClearContents
Worksheets("REQ Template").Range("F15").ClearContents
Worksheets("REQ Template").Range("I11") = ""
Worksheets("REQ Template").Range("I15") = ""
Worksheets("REQ Template").Range("K43").Formula = "=(K41+K42)*0.1"
GSTBox = True
Sheets("REQ List").Select
ActiveSheet.Range("B2:G10000").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Range("B65536").End(xlUp).Select
ActiveCell.Offset(0, 7).Select
ThisWorkbook.Save
'Opens new PO
Workbooks.Open (ThisWorkbook.Path & "\" & ReqNo & ".xlsx")
End Sub
Private Sub UserList_GotFocus()
Sheet1.UserList.ListFillRange = "User!" & Sheet4.Range("B2", Sheet4.Range("B65536").End(xlUp)).Address
End Sub
Private Sub SupplierList_GotFocus()
Sheet1.SupplierList.ListFillRange = "Supplier!" & Sheet5.Range("A2", Sheet5.Range("A65536").End(xlUp)).Address
End Sub
Private Sub JobList_GotFocus()
Sheet1.JobList.ListFillRange = "Job!" & Sheet6.Range("A2", Sheet6.Range("A65536").End(xlUp)).Address
End Sub
Bookmarks