Excel Macro suddenly starts giving incorrect ActiveCell Data

[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.

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

[Aflatoon]

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.

[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.

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.

[simora]

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

[Aussiebear]

Is my understanding of your code correct?

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?

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?

[Paul_Hossler]

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

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

[June7]

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

You could provide file for analysis.

[Aflatoon]

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.

[Aussiebear]

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

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

[Aussiebear]

Or from a slightly different perspective.

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

[simora]

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.

[simora]

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.

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

[Paul_Hossler]

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

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

I'm guessing you wanted something like this

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

[simora]

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.