VBA editor opens when I select a file in Windows Explorer

Question

I've written code to change the contents of a cell in multiple protected workbooks looking up the value to be inserted from a table in my worksheet.

The issue I have is that when I select a file in the \Files\ folder, it opens up the VBA editor showing the code I'm using. The window opens even if Excel is closed and after a fresh restart.

It's not really a bug. It's just quite annoying as I'm using the preview mode in file explorer to double check the code has worked.

Files are stored in c:\Folder\.

A list of the file names is in column A starting in row 2 with corresponding values to be updated in column B.

Paste location is cell F2 of sheet 'References'

Sub UpdateFiles()
    Dim rownum As Long
    rownum = 2
    
    Dim last_row As Long
    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim filename As String
    Dim newvalue As Long
    
    For nextRow = 2 To last_row
        filename = Range("A" & rownum).Value     
        newvalue = ActiveWorkbook.Sheets("Sheet1").Range("B" & rownum).Value
    
        Workbooks.Open "C:\Folder\" & filename        
        ActiveWorkbook.Unprotect ("password")        
        ActiveWorkbook.Sheets("References").Range("F2") = newvalue        
        ActiveWorkbook.Protect ("password")        
        ActiveWorkbook.Save        
        ActiveWorkbook.Close
    
        rownum = rownum + 1
    Next nextRow
End Sub

You are saying that after running this code and you go into File Explorer and click on a file, it opens VBA showing THIS code? That sounds crazy bananas. — JNevill, Jun 17 '21 at 21:31
That's correct, could be a setting i have elsewhere but what you describe is what is happening — Matt Drake, Jun 17 '21 at 21:39
If I understand - when you open an Excel file in your `C:\Folder\` folder, the VBA window pops up showing the code. Does your Excel file also load, so it's two windows (the spreadsheet, and VBA window)? What if you open a similar file in a different folder? — BruceWayne, Jun 18 '21 at 03:15

score 0 · Answer 1 · answered Jan 04 '23 at 19:38

0

I had a similar strange thing recently and it turned out i'd actually accidentally dropped some files into the excel startup folder. I'd say it's worth checking in there for the VBA file:

enter image description here

answered Jan 04 '23 at 19:38

bobfossil

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 04 '23 at 22:43
This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/33560930) – Cameron Critchlow Jan 04 '23 at 23:37

VBA editor opens when I select a file in Windows Explorer

1 Answers1