Results 1 to 3 of 3

Code randomly stopped working in versions other than Excel 2013

Threaded View

  1. #1
    Registered User
    Join Date
    07-20-2014
    Location
    Gold Coast
    MS-Off Ver
    2013
    Posts
    8

    Code randomly stopped working in versions other than Excel 2013

    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
    Last edited by mlmusic; 12-21-2014 at 11:46 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Paste as image code not working on newer Excel versions
    By RenanCip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2014, 12:26 PM
  2. Macro code working fine in excel 2013 but not in excel 2010.
    By sere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-10-2014, 02:23 AM
  3. Excel 2013 isn't saving file versions.
    By TakTak in forum Excel General
    Replies: 0
    Last Post: 05-12-2014, 05:18 PM
  4. MSN Stock Quotes plugin stopped working (June 2013)
    By yozzdi in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-09-2013, 11:39 AM
  5. Excel code stopped working
    By Jollyfrog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-28-2010, 07:34 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1