1

I have a folder that contains hundreds of subfolders. The first four characters of each subfolder's name consists of a 4-digit number followed by a string of alphanumeric characters (Example: 1234_Test_test_99).

I am trying to get the subfolder's full name (as indicated in the example) based on first four characters (in this case, I enter 1234 in cell C3 and expect to see 1234_Test_test_99 in cell F3).

Private Sub Worksheet_Change(ByVal Target As Range)
Folder = Dir("C:\Users\TestFolder" & Range("C3").Value & "*", vbDirectory)
If Len(Folder) > 0 Then
    Range("F3").Value = Folder
Else
    MsgBox("No such folder")
End If
End Sub

Upon entering the substring in C3, the subfolder's full name is briefly displayed in cell F3. A split second later, Excel crashes. No error message. It shuts down, closing all open files in the process. I believe the problem is at

Range("F3").Value = Folder

since after replacing it with

MsgBox(Folder)

the macro runs.

double-beep
  • 5,031
  • 17
  • 33
  • 41
kstdrew
  • 13
  • 2
  • 3
    Because you are calling this from `Worksheet_Change` and then ***changing the worksheet***, you're firing a cascade of `Worksheet_Change` events. Excel is likely crashing because of the flood of change events. Inside of this sub, if you need to change the worksheet, protect that section of code by inhibiting events with `Application.EnableEvents = False` and then after you change the value `Application.EnableEvents = True` – PeterT Nov 17 '22 at 22:55
  • In addition to Peter's comment, you should check if C3 changed before running your update code, using `If Not Application.Intersect(Target, Range("C3") Is Nothing Then` – chris neilsen Nov 17 '22 at 23:21
  • 1
    You're also missing a \ in your path – chris neilsen Nov 17 '22 at 23:21
  • @chris - I made a mistake when writing the code here. In the workbook, the path includes a \ at the end – kstdrew Nov 18 '22 at 03:16
  • @PeterT - brilliant! I knew something is going on, overwhelming the application, but I didn't think of using the Application.EnableEvents bit. I've tried error handling, but to no avail. Thanks a lot! – kstdrew Nov 18 '22 at 03:20
  • You may want to read on `Application.EnableEvents` [HERE](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Nov 18 '22 at 08:31
  • See also Error mitigation in combination with `Application.EnableEvents = false` in other answers. Like this one: https://stackoverflow.com/questions/74467640/changing-target-case/74472697#74472697 – mtholen Nov 18 '22 at 09:17

0 Answers0