Excel Macro suddenly starts giving incorrect ActiveCell Data

[simora]

Can you take a screenshot.
I'm trying to delete the attachment to see if I can upload a slightly different version.
Not sure if you can delete an attachment.

[simora]

Opened your file. I've never had a downloaded Excel file open with "protected view" warning. I can't seem to get around this and view code.

I will zip the file and upload a slightly different version.

[simora]

Hi Guys:

I think this zip file is the one that I intended to upload.
Sorry about that.

[simora]

There is only 1 Macro associated with the 90% Invoice Button.
There should be No protection & no password needed.
Here's the Macro.

Sub InvPost()
    ' For The 90% Invoice
    Dim myRow As Long
    Dim val As String           
    val = ActiveCell.Value
    myRow = ActiveCell.Row    
    Sheets("Gunsmoke").Select    
    ActiveCell.Select        
    If ActiveCell.Column <> 1 Then        
        MsgBox " Select a Lot Number "
        Exit Sub
    Else      
        ' This was added so that you can see what Lot # was selected
        MsgBox ActiveCell.Text      
        Sheets("Parts&Labor").Unprotect
        Sheets("Parts&Labor").Range("A18:E22").Locked = False                       
        ActiveCell.Interior.Color = vbYellow
        ActiveCell.Offset(0, 8).Value = Now()
        Range("AH1").Value = ActiveCell.Text
        Range("AJ1").Value = ActiveCell.Offset(0, 2).Value
        Sheets("Parts&Labor").Range("A18").Value = Sheets("Gunsmoke").Range("AM1").Value
        Sheets("Parts&Labor").Range("E8").Value = ActiveCell.Text
        Sheets("Parts&Labor").Range("E18").Value = ActiveCell.Offset(0, 11).Value
        Sheets("Parts&Labor").Range("E3").Value = Now()
        Sheets("Parts&Labor").Range("E4").Value = Sheets("Parts&Labor").Range("H9").Value + 1
        Sheets("Parts&Labor").Range("U2").Value = myRow
        ' Sheets("Parts&Labor").Shapes("Check Box 610").OLEFormat.Object.Value = 1        
    End If
    Sheets("Parts&Labor").Activate
    Sheets("Parts&Labor").Range("24:25").EntireRow.Hidden = False
    Sheets("Parts&Labor").Range("F3").Select
    Sheets("Parts&Labor").Range("A18:E22").Locked = True
    Sheets("Parts&Labor").Protect
End Sub

[June7]

Same issue opening file (with Excel 2010) so tried another computer (Excel 2021) - opens ok, except for notice about updating links.

Went to Gunsmoke sheet.
Clicked 90% button.
Code works correctly no matter what cell is selected.
Never goes to column Y. Stays where I started.

It is not necessary to Unlock cells for code to write data into them. Just Unprotect sheet.

Had to use Task Manager to close file because of your code - got error "can't find project or library".

I would use Access for this.

[simora]

June7

Thanks for your efforts.

There has to be something going on here because I sent the same exact file to other people and they ran the file the first time, NO PROBLEMS, then, when it was run the next time, it created the same problem that I was having.
It would either go to column Y, or it would keep going to the last cell that was previously selected.

I'll find a work around, but I'll really like to find out what's going on.

[June7]

What do you mean by "next time" - next time file is opened?

I ran procedure multiple times (even closed and reopened) and never encountered issue.

Can't replicate so can't offer a fix.

[simora]

June7


Thanks for trying.

I'm at a loss as to how some people have no problems running the file, while others have.

My workaround is to re-structure the code using an onclick event from column A.

Crude but workable.

[georgiboy]

Give the attached a try, I have tidied up some of the code, mainly removed the use of Activate and Select being used together on the same sheet. You would do better to write code in the future that does not use Select at all and qualify all of your ranges and worksheets.

I haven't the time to rewrite all of your code to qualify everything but if you Google what qualify a range/ worksheet means you should understand.

[simora]

georgiboy

Hi: Thanks for your input.

Unfortunately, the code itself is not the problem.
There's something going on with the workbook itself that I have not been able to isolate.
When I used your code with the file provided, everything worked as intended, however, when I copied your code into the actual program, the original problem resurfaced.
That suggests that there's some malicious code hidden in the actual workbook that is only affecting this specific worksheet, because all of the other sheets are unaffected.
And as I had previously stated. When I step through the code, it executes correctly.

I have rewritten the code; changed the Button & sheetName, and put everything into a new WorkBook.
The problem seems to have gone away. Hopefully for good.

Again: Thanks

[georgiboy]

When I used your file, I saved it as a '.xlsm' file, when I was done editing the code I saved it back to a ''.xls' file. Maybe if you were to do the same it may change something in the file. I am only clutching at straws here as I can't see what the problem could be.

[Gasman]