Excel Macro suddenly starts giving incorrect ActiveCell Data

I've had a macro running for over 3 years, but now it has suddenly started returning the incorrect column Number, even when the cursor is firmly in Column 1.

It keeps selecting column Y. EVERY TIME !
It doesn't matter what I select as the ActiveCell.
Any ideas what could be causing this ?
If a virus is a possibility, any suggestions as to where I should start looking.

Here's the code bit that I'm using which is causing the problem.

I added the MsgBox ActiveCell.Column at the end so that I could see what column it was selecting.

Code:

---

Sub GetCell()   
    Dim myRow As Long
    Dim val As String   
    val = ActiveCell.Value
    myRow = ActiveCell.Row   
    If ActiveCell.Column <> 1 Then
        MsgBox ActiveCell.Column
        MsgBox " Select a Lot Number "
        Exit Sub
    End If
    MsgBox ActiveCell.Column
End Sub

---

That code doesn't select anything, and is clearly not a virus.
If you had to add the line at the end to see the column number, then it must be 1 or the If block prior to that would have displayed its message.

Quote:

---

Originally Posted by simora

I've had a macro running for over 3 years, but now it has suddenly started returning the incorrect column Number, even when the cursor is firmly in Column 1.

It keeps selecting column Y. EVERY TIME !
It doesn't matter what I select as the ActiveCell.
Any ideas what could be causing this ?
If a virus is a possibility, any suggestions as to where I should start looking.

Here's the code bit that I'm using which is causing the problem.

I added the MsgBox ActiveCell.Column at the end so that I could see what column it was selecting.

Code:

---

Sub GetCell()   
    Dim myRow As Long
    Dim val As String   
    val = ActiveCell.Value
    myRow = ActiveCell.Row   
    ActiveCell.Select   
    MsgBox ActiveCell
    If ActiveCell.Column <> 1 Then
        MsgBox ActiveCell.Column
        MsgBox " Select a Lot Number "
        Exit Sub
    End If
    MsgBox ActiveCell.Column
End Sub

---

---

I did NOT include all of the code, but I have now added the selection part but it's still not displaying the cell that I have selected.

Hi:
I forgot to mention that when I step through the code using F8 from the VBA editor, it executes correctly.

Is my understanding of your code correct?

Code:

---

Sub GetCell()
    Dim myRow As Long
    Dim val As String           
    val = ActiveCell.Value ' <-----Stores the value of the active cell
    myRow = ActiveCell.Row ' <-----Stores the row number of the active cell
    ActiveCell.Select ' <-----This line is redundant if the cell is already active
    MsgBox ActiveCell ' <-----Displays the value of the active cell
    If ActiveCell.Column <> 1 Then ' <-----Checks if the active cell is NOT in Column A
        MsgBox ActiveCell.Column  ' <------Displays the column number if not in Column A
        MsgBox " Select a Lot Number " ' <-----Prompts the user to select a Lot Number
        Exit Sub ' <-----Exits the sub-procedure
    End If
    MsgBox ActiveCell.Column ' <-----Displays the column number if it IS in Column A
End Sub

---

Essentially the code is meant to be acting like a gateway to ensure the User selects a cell in Column A right?

Quote:

---

I've had a macro running for over 3 years, but now it has suddenly started returning the incorrect column Number, even when the cursor is firmly in Column 1.

---

. Have you changed any other code in the last few days?

Would there be any reason why the active cell is in Column Y (25), prior to running the failed code?

Quote:

---

Originally Posted by simora

I've had a macro running for over 3 years, but now it has suddenly started returning the incorrect column Number, even when the cursor is firmly in Column 1.

It keeps selecting column Y. EVERY TIME !
It doesn't matter what I select as the ActiveCell.
Any ideas what could be causing this ?
If a virus is a possibility, any suggestions as to where I should start looking.

---

Passing thought

I've fixed many "It used to work before" issues by cleaning the code

http://www.appspro.com/Utilities/CodeCleaner.htm

Quote:

---

The Excel VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

---

This doesn't work with 64 bit office, but I believe you can get the same results by manually exporting the module, delete it from the workbook, and then import the exported module. Don't know if you'd have to do all

Code works for me. Column Y is not selected so is there more code you have not provided?

You could provide file for analysis.

Quote:

---

Originally Posted by simora

I did NOT include all of the code, but I have now added the selection part

---

and is that now all of the code? The line you added would only make a difference if you already had multiple cells selected - it would then select just the active one of those.

Does this work for you? This version cleans up the original code, removing redundancies and combining messages.

Code:

---

Sub EnsureSelectionInColumnA()
    ' Checks if the active cell is in Column A (column index 1).
    If ActiveCell.Column <> 1 Then
        ' If not in Column A, inform the user and exit the macro.
        MsgBox "Please select a cell in Column A (Lot Number column) to proceed.", vbExclamation, "Invalid Selection"
        Exit Sub
    End If
    ' If the code reaches here, it means the active cell IS in Column A.
    ' You can place the rest of your macro's logic here.
    MsgBox "You have successfully selected a cell in Column A. Proceeding...", vbInformation, "Selection Confirmed"
End Sub

---

Or from a slightly different perspective.

Code:

---

Sub SelectLotNumberFromActiveRow()
    Dim activeRow As Long
    ' Get the row number of the currently active cell.
    activeRow = ActiveCell.Row
    ' Select the cell in Column A (column index 1) of the active row.
    ' This assumes Lot Numbers are always in Column A.
    Cells(activeRow, 1).Select
    MsgBox "Selected Lot Number in cell: " & ActiveCell.Address & " with value: " & ActiveCell.Value, vbInformation, "Lot Number Selected"
End Sub

---

Aussiebear:


You are correct.
The code is to make sure that the user selected a cell in Column A.
When I open the Macro and step through the code by using F8, everything works as intended.
I have deleted and re-created a different button to run the code, but that still give the same error.
I've never encountered something like this before.
I have exported the same Macro to a new sheet.
It works the very first time, but after that, it's the same error.

I've been asked to provide more of the full Macro rather than just the offending portion.
Here's more or less the full bit to get an idea of what it does.


After trying all of the suggestions, I'm noticing another issue.
Now, if I've selected a cell, the next time the Macro runs, it will select the previous cell as the activecell even if I've selected a different cell.
This was NOT an issue before.


Here's the code.

Code:

---

Sub CellPost()
    Dim myRow As Long
    Dim val As String
    Dim activeRow As Long   
    val = ActiveCell.Value
    myRow = ActiveCell.Row       
    Sheets("Gunsmoke").Activate
    Sheets("Gunsmoke").Select   
    activeRow = ActiveCell.Row
    Cells(activeRow, 1).Select   
    If ActiveCell.Column <> 1 Then
        MsgBox " Select a Lot Number from Column A "
        Exit Sub
    Else
        If ActiveCell.Interior.Color = vbYellow Then
            MsgBox " This Lot was already Invoiced"
            Exit Sub
        Else
            Sheets("Parts&Labor").Unprotect
            Sheets("Parts&Labor").Range("A18:E22").Locked = False
            Sheets("Parts&Labor").Range("E3").Value = Now()
            ' MORE POSTING IS DONE HERE
            ActiveCell.Interior.Color = vbYellow
            ActiveCell.Offset(0, 8).Value = Now()
            ' MORE POSTING IS DONE HERE
        End If
    End If
End Sub

---

Not sure, but it seems that you're always making ActiveCell column A in whatever the activeRow is

Code:

---

    activeRow = ActiveCell.Row
    Cells(activeRow, 1).Select   
    If ActiveCell.Column <> 1 Then

---

I'm guessing you wanted something like this

Code:

---

Option Explicit

Sub CellPost()
    Dim r As Range
    Dim val As String
    Dim activeRow As Long   
    Sheets("Gunsmoke").Select   
    If ActiveCell.Column <> 1 Then
        MsgBox " Select a Lot Number from Column A "
        Exit Sub
    End If   
    If ActiveCell.Interior.Color = vbYellow Then
        MsgBox " This Lot was already Invoiced"
        Exit Sub
    End If   
    Set r = ActiveCell           
    Sheets("Parts&Labor").Unprotect
    Sheets("Parts&Labor").Range("A18:E22").Locked = False
    Sheets("Parts&Labor").Range("E3").Value = Now()
    ' MORE POSTING IS DONE HERE                     
    r.Interior.Color = vbYellow
    r.Offset(0, 8).Value = Now()
  ' MORE POSTING IS DONE HERE
End Sub

---

Paul_Hossler


Now I'm sure that the code itself is NOT the problem.


There's something going on with the Worksheet itself that I can't isolate.
I've come to this conclusion because if I create a blank sheet and run all of the different versions of the code that has been suggested, as well as my original code, the new sheet performs as expected.
No Errors, No Problems.
However, when I run all of the code that we've put forward, a problem arises.
Either the ActiveCell returned is NOT the one that was selected, or it randomly makes the ActiveCell the last ActiveCell that was used.

Even when I copy the sheet to a new workbook, the problems arise.
If I run the macro or step through it using F8, everything works as intended, so there's something going on with the click of the Button that's causing the problem.
M question is: How do you investigate random behavior on a worksheet, probably caused by clicking the Button.

Since we are unable to look over your shoulder as you work on your spreadsheet it is difficult to say exactly what. Are you able to create a mock up of your current workbook and post it?

12 worksheets in the workbook but I'll isolate and test with only those that are called by the macro.

Will post back as time permits.

Thanks guys.

Make sure that there are no events that are getting in the way

Make sure that the sheet is not protected, since that might prevent ActiveCell from getting updated

I added some Debug.Print statements to the attachment and it seelms to work as I think you wanted

Delete WS Gumsmoke, save WB as noew name, and recreate Gunsmoke and see if that works