Help with sheet code

[ASBeard]

Good morning. I have an excel file and I have written code to display a default value in approximately 400 cells. The code works correctly, however whenever I several of these cells simultaneously it causes a critical error and the excel file closes. I have placed an image of just some of the code as the mini-sheet I don't think will copy inside visual basic (if this is incorrect, please let me know). If you need to see all of the code, please don't hesitate to let me know. And just so you know, the code is entered in the 'sheet' for which the default values are to be placed and not in a module. I am hoping that there is something I can do to simplify my code to prevent the critical error. Thank you in advance for any help you can provide!

 

[Jeffrey Mahoney]

Welcome to the forum ASBeard. It would be very helpful if you could paste your VBA code as text and wrap it with VBA like:

Hi

 

[ASBeard]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
    Dim cell As Range
   
    Application.ScreenUpdating = False
   
    Set rng = Range("B27:B32")
    For Each cell In rng
        If cell = "" Then cell = "(Enter Custom Label)"
    Next cell
   
    Set rng = Range("F10")
    For Each cell In rng
        If cell = "" Then cell = "MRN"
    Next cell
   
    Set rng = Range("F11")
    For Each cell In rng
        If cell = "" Then cell = "Visit Number"
    Next cell
       
    Set rng = Range("D7,D10:D11,D13:D16,I6,I9:I11")
    For Each cell In rng
        If cell = "" Then cell = "Optional"
    Next cell
   
    Set rng = Range("D8:D9,D12,I7:I8,I12")
    For Each cell In rng
        If cell = "" Then cell = "Hidden"
    Next cell
   
    Set rng = Range("D6")
    For Each cell In rng
        If cell = "" Then cell = "Required"
    Next cell
   
    Set rng = Range("I14")
    For Each cell In rng
        If cell = "" Then cell = "Lifetime ID"
    Next cell
   
     Set rng = Range("I15")
    For Each cell In rng
        If cell = "" Then cell = "Last Name and First Name"
    Next cell
   
     Set rng = Range("E19")
    For Each cell In rng
        If cell = "" Then cell = "Red"
    Next cell
   
    Set rng = Range("E20")
    For Each cell In rng
        If cell = "" Then cell = "Orange"
    Next cell
   
    Set rng = Range("E21")
    For Each cell In rng
        If cell = "" Then cell = "Yellow"
    Next cell
   
    Set rng = Range("E22")
    For Each cell In rng
        If cell = "" Then cell = "Blue"
    Next cell
   
    Set rng = Range("E23")
    For Each cell In rng
        If cell = "" Then cell = "Purple"
    Next cell
   
    Set rng = Range("E24")
    For Each cell In rng
        If cell = "" Then cell = "Salmon"
    Next cell
   
    Set rng = Range("E25")
    For Each cell In rng
        If cell = "" Then cell = "Gray"
    Next cell
   
    Set rng = Range("E26")
    For Each cell In rng
        If cell = "" Then cell = "Green"
    Next cell
   
    Set rng = Range("G34")
    For Each cell In rng
        If cell = "" Then cell = "CM"
    Next cell
   
    Set rng = Range("G35")
    For Each cell In rng
        If cell = "" Then cell = "LBS"
    Next cell
   
    Set rng = Range("G36")
    For Each cell In rng
        If cell = "" Then cell = "UNCONFIRMED"
    Next cell
   
    Set rng = Range("G40,G41,G43,G44,G46,G48:G53,G55,G60,G62,G64,G67,G69,G71,G72,G75,G77:G78,G80,G82:G90,G148,G157:G159,G161,G205,G209,G214,G218,G223,G227,G231,G235,G239,G243,G247,G250,G254,G258,G347")
    For Each cell In rng
        If cell = "" Then cell = "OFF"
    Next cell
   
    Set rng = Range("G39,G47,G56:G59,G63,G65,G76,G79,G81,G138,G139,G146,G149,G150,G153,G319,G320,G351,F195")
    For Each cell In rng
        If cell = "" Then cell = "ON"
    Next cell
   
    Set rng = Range("G91")
    For Each cell In rng
        If cell = "" Then cell = "Manage Patient"
    Next cell
    
    Set rng = Range("G92")
    For Each cell In rng
        If cell = "" Then cell = "Review"
    Next cell
   
    Set rng = Range("G93")
    For Each cell In rng
        If cell = "" Then cell = "Measurements"
    Next cell
   
    Set rng = Range("G94")
    For Each cell In rng
        If cell = "" Then cell = "120"
    Next cell
   
    Set rng = Range("G95")
    For Each cell In rng
        If cell = "" Then cell = "15"
    Next cell
   
    Set rng = Range("G96")
    For Each cell In rng
        If cell = "" Then cell = "25.0 MM/SEC"
    Next cell
   
    Set rng = Range("G97")
    For Each cell In rng
        If cell = "" Then cell = "Record All"
    Next cell
   
    Set rng = Range("G98")
    For Each cell In rng
        If cell = "" Then cell = "Record"
    Next cell
       
    Set rng = Range("G99")
    For Each cell In rng
        If cell = "" Then cell = "All Alarms"
    Next cell
   
    Set rng = Range("G101")
    For Each cell In rng
        If cell = "" Then cell = "Traditional"
    Next cell
   
    Set rng = Range("G102")
    For Each cell In rng
        If cell = "" Then cell = "20"
    Next cell
   
    Set rng = Range("G103,G210,G224,G244,G259,G291,G295,G300")
    For Each cell In rng
        If cell = "" Then cell = "10"
    Next cell
   
    Set rng = Range("G104")
    For Each cell In rng
        If cell = "" Then cell = "7"
    Next cell
   
    Set rng = Range("G105")
    For Each cell In rng
        If cell = "" Then cell = "4"
    Next cell
   
    Set rng = Range("G106:G108")
    For Each cell In rng
        If cell = "" Then cell = "0"
    Next cell
   
    Set rng = Range("G109")
    For Each cell In rng
        If cell = "" Then cell = "19:00"
    Next cell
   
    Set rng = Range("G110")
    For Each cell In rng
        If cell = "" Then cell = "20:00"
    Next cell
   
    Set rng = Range("H109")
    For Each cell In rng
        If cell = "" Then cell = "7"
    Next cell
   
    Set rng = Range("H110,G356")
    For Each cell In rng
        If cell = "" Then cell = "4"
    Next cell
   
    Set rng = Range("G112")
    For Each cell In rng
        If cell = "" Then cell = "1280x1024"
    Next cell
   
    Set rng = Range("G113")
    For Each cell In rng
        If cell = "" Then cell = "8"
    Next cell
  
    Set rng = Range("G114")
    For Each cell In rng
        If cell = "" Then cell = "2"
    Next cell
        
    Set rng = Range("G115")
    For Each cell In rng
        If cell = "" Then cell = "Primary Lead"
    Next cell
   
    Set rng = Range("G116")
    For Each cell In rng
        If cell = "" Then cell = "Any Pleth"
    Next cell
   
    Set rng = Range("G117")
    For Each cell In rng
        If cell = "" Then cell = "Any Press"
    Next cell
   
    Set rng = Range("G118:G122")
    For Each cell In rng
        If cell = "" Then cell = "Any RT Waves"
    Next cell
   
    Set rng = Range("G124")
    For Each cell In rng
        If cell = "" Then cell = "1280x1024"
    Next cell
   
    Set rng = Range("G125,G166")
    For Each cell In rng
        If cell = "" Then cell = "Primary Lead"
    Next cell
   
    Set rng = Range("G126,G178")
    For Each cell In rng
        If cell = "" Then cell = "Any Pleth"
    Next cell
   
    Set rng = Range("G127,G179")
    For Each cell In rng
        If cell = "" Then cell = "Any Press"
    Next cell
   
    Set rng = Range("G128:G132")
    For Each cell In rng
        If cell = "" Then cell = "Any RT Waves"
    Next cell
   
    Set rng = Range("G135")
    For Each cell In rng
        If cell = "" Then cell = "Yellow Only"
    Next cell
   
    Set rng = Range("G136")
    For Each cell In rng
        If cell = "" Then cell = "2 min"
    Next cell
   
    Set rng = Range("G137")
    For Each cell In rng
        If cell = "" Then cell = "Red&Yellow"
    Next cell
   
    Set rng = Range("G140")
    For Each cell In rng
        If cell = "" Then cell = "3 min"
    Next cell
   
    Set rng = Range("G141")
    For Each cell In rng
        If cell = "" Then cell = "Hard"
    Next cell
   
    Set rng = Range("G142,G145")
    For Each cell In rng
        If cell = "" Then cell = "Yellow"
    Next cell
   
    Set rng = Range("G143,G144")
    For Each cell In rng
        If cell = "" Then cell = "Cyan"
    Next cell
   
    Set rng = Range("G147")
    For Each cell In rng
        If cell = "" Then cell = "Enhanced"
    Next cell
   
    Set rng = Range("G151")
    For Each cell In rng
        If cell = "" Then cell = "Short Yellow"
    Next cell
   
    Set rng = Range("G154")
    For Each cell In rng
        If cell = "" Then cell = "3"
    Next cell
   
    Set rng = Range("G155")
    For Each cell In rng
        If cell = "" Then cell = "NURSE CALL ALARM"
    Next cell
   
    Set rng = Range("G156")
    For Each cell In rng
        If cell = "" Then cell = "INFINITE"
    Next cell
   
    Set rng = Range("G160")
    For Each cell In rng
        If cell = "" Then cell = "Look for Monitor"
    Next cell
   
    Set rng = Range("G162")
    For Each cell In rng
        If cell = "" Then cell = "All"
    Next cell
   
    Set rng = Range("G164")
    For Each cell In rng
        If cell = "" Then cell = "1 min"
    Next cell
   
    Set rng = Range("G165")
    For Each cell In rng
        If cell = "" Then cell = "2 Waves P"
    Next cell
   
    Set rng = Range("G167")
    For Each cell In rng
        If cell = "" Then cell = "Secondary Lead"
    Next cell
   
    Set rng = Range("G168,G169")
    For Each cell In rng
        If cell = "" Then cell = "ECG"
    Next cell
   
    Set rng = Range("G171,G172")
    For Each cell In rng
        If cell = "" Then cell = "Enabled"
    Next cell
   
    Set rng = Range("G177,G180")
    For Each cell In rng
        If cell = "" Then cell = "Any ECG"
    Next cell
   
    Set rng = Range("G186,G188")
    For Each cell In rng
        If cell = "" Then cell = "25"
    Next cell
   
    Set rng = Range("G187,G189")
    For Each cell In rng
        If cell = "" Then cell = "0"
    Next cell
   
    Set rng = Range("G191")
    For Each cell In rng
        If cell = "" Then cell = "Patient Name"
    Next cell
   
    Set rng = Range("G192,G194:G196,G198,G267,G273,G276,G280,G283,G287")
    For Each cell In rng
        If cell = "" Then cell = "None"
    Next cell
   
    Set rng = Range("G193")
    For Each cell In rng
        If cell = "" Then cell = "Lifetime ID"
    Next cell
   
    Set rng = Range("G199")
    For Each cell In rng
        If cell = "" Then cell = "Unit Name"
    Next cell
   
    Set rng = Range("G200")
    For Each cell In rng
        If cell = "" Then cell = "Institution Name"
    Next cell
   
    Set rng = Range("G204")
    For Each cell In rng
        If cell = "" Then cell = "Paper"
    Next cell
   
    Set rng = Range("G184,G207,G212,G221,G229,G233,G237,G241,G252,G256,G261,G263")
    For Each cell In rng
        If cell = "" Then cell = "Portrait"
    Next cell
   
    Set rng = Range("G208,G213,G222,G226,G230,G234,G238,G242,G246,G249,G253,G257")
    For Each cell In rng
        If cell = "" Then cell = "Electronic Document"
    Next cell
   
    Set rng = Range("G216")
    For Each cell In rng
        If cell = "" Then cell = "Landscape"
    Next cell
   
    Set rng = Range("G217")
    For Each cell In rng
        If cell = "" Then cell = "Do Not Print"
    Next cell
   
    Set rng = Range("G266,G272,G275,G279,G282,G286")
    For Each cell In rng
        If cell = "" Then cell = "7"
    Next cell
   
    Set rng = Range("H266,H272,H275,H279,H282,H286")
    For Each cell In rng
        If cell = "" Then cell = "30"
    Next cell
   
    Set rng = Range("G268,G292,G357")
    For Each cell In rng
        If cell = "" Then cell = "Red Alarms"
    Next cell
   
    Set rng = Range("H268,H292,H357")
    For Each cell In rng
        If cell = "" Then cell = "Yellow Alarms"
    Next cell
   
    Set rng = Range("G269,G293,G358")
    For Each cell In rng
        If cell = "" Then cell = "ECG Alarms"
    Next cell
   
    Set rng = Range("H269,H293,H358")
    For Each cell In rng
        If cell = "" Then cell = "Saved Strips"
    Next cell
   
    Set rng = Range("G270,G294,G359")
    For Each cell In rng
        If cell = "" Then cell = "Non-ECG Alarms"
    Next cell
   
    Set rng = Range("G284")
    For Each cell In rng
        If cell = "" Then cell = "1 Hour"
    Next cell
   
    Set rng = Range("G277")
    For Each cell In rng
        If cell = "" Then cell = "Algorithm Interval"
    Next cell
   
    Set rng = Range("G290")
    For Each cell In rng
        If cell = "" Then cell = "Alarm"
    Next cell
   
    Set rng = Range("G297")
    For Each cell In rng
        If cell = "" Then cell = "Patient Summary"
    Next cell
   
    Set rng = Range("G299")
    For Each cell In rng
        If cell = "" Then cell = "Tabular Trend"
    Next cell
   
    Set rng = Range("G301")
    For Each cell In rng
        If cell = "" Then cell = "30 Minutes"
    Next cell
   
    Set rng = Range("G303")
    For Each cell In rng
        If cell = "" Then cell = "PRN1"
    Next cell
   
    Set rng = Range("G304")
    For Each cell In rng
        If cell = "" Then cell = "PRN2"
    Next cell
   
    Set rng = Range("G305")
    For Each cell In rng
        If cell = "" Then cell = "PRN3"
    Next cell
   
    Set rng = Range("G307,G342")
    For Each cell In rng
        If cell = "" Then cell = "<None>"
    Next cell
   
    Set rng = Range("G309,G316,G353")
    For Each cell In rng
        If cell = "" Then cell = "4 seconds"
    Next cell
   
    Set rng = Range("G310,G317")
    For Each cell In rng
        If cell = "" Then cell = "6 seconds"
    Next cell
   
    Set rng = Range("G311,G322,G354")
    For Each cell In rng
        If cell = "" Then cell = "10 seconds"
    Next cell
    
     Set rng = Range("G312,G323")
    For Each cell In rng
        If cell = "" Then cell = "30 seconds"
    Next cell
   
    Set rng = Range("G318,G330,G355")
    For Each cell In rng
        If cell = "" Then cell = "25 mm/s"
    Next cell
   
    Set rng = Range("G326,G327")
    For Each cell In rng
        If cell = "" Then cell = "0.15 Hz"
    Next cell
   
    Set rng = Range("H326")
    For Each cell In rng
        If cell = "" Then cell = "100 Hz"
    Next cell
   
    Set rng = Range("H327")
    For Each cell In rng
        If cell = "" Then cell = "150 Hz"
    Next cell
   
    Set rng = Range("G328")
    For Each cell In rng
        If cell = "" Then cell = "10 mm/mV"
    Next cell
   
    Set rng = Range("G329")
    For Each cell In rng
        If cell = "" Then cell = "Full"
    Next cell
   
    Set rng = Range("G331")
    For Each cell In rng
        If cell = "" Then cell = "Sequential"
    Next cell
   
    Set rng = Range("G332")
    For Each cell In rng
        If cell = "" Then cell = "3X4 1R"
    Next cell
   
    Set rng = Range("G333")
    For Each cell In rng
        If cell = "" Then cell = "II"
    Next cell
   
    Set rng = Range("G334")
    For Each cell In rng
        If cell = "" Then cell = "aVF"
    Next cell
   
    Set rng = Range("G335")
    For Each cell In rng
        If cell = "" Then cell = "V5"
    Next cell
   
    Set rng = Range("G336")
    For Each cell In rng
        If cell = "" Then cell = "Standard"
    Next cell
   
    Set rng = Range("G338")
    For Each cell In rng
        If cell = "" Then cell = "PH100B"
    Next cell
   
    Set rng = Range("G339")
    For Each cell In rng
        If cell = "" Then cell = "Show Interpretations and Reasons"
    Next cell
   
    Set rng = Range("G340")
    For Each cell In rng
        If cell = "" Then cell = "Bazett"
    Next cell
   
    Set rng = Range("G341")
    For Each cell In rng
        If cell = "" Then cell = "Include All"
    Next cell
   
    Set rng = Range("G343")
    For Each cell In rng
        If cell = "" Then cell = "50 BPM"
    Next cell
   
    Set rng = Range("G344")
    For Each cell In rng
        If cell = "" Then cell = "Standard"
   
    Next cell
    Set rng = Range("G345")
    For Each cell In rng
        If cell = "" Then cell = "ECG Meas"
    Next cell
   
    Set rng = Range("G346")
    For Each cell In rng
        If cell = "" Then cell = "STEMI-CA"
    Next cell
   
    Set rng = Range("H345")
    For Each cell In rng
        If cell = "" Then cell = "Critical Values"
    Next cell
   
    Set rng = Range("H346")
    For Each cell In rng
        If cell = "" Then cell = "No Est. MI Size"
    Next cell
   
    Set rng = Range("G352")
    For Each cell In rng
        If cell = "" Then cell = "Wave Strip Export"
    Next cell

Application.ScreenUpdating = True
End Sub

 

[Jeffrey Mahoney]

Well, you almost made it, you just didn't highlight the code before clicking the VBA icon.

First of all, you want to make all those changes EVERY time a user makes any change to the worksheet? If so, we better prevent the macro from reiterating this process every time the macro runs. You need to add the code below at the top of the macro and the second line at the bottom.

Application.EnableEvents = False

Application.EnableEvents = False

Now I'm going to make some suggestions to make this code more efficient

 

[Jeffrey Mahoney]

I assumed that you only wanted to make changes to cells if the user deleted the cell contents. Let me know if I'm on the right track. The code below is a sample of what you had and it tests to see if the changed range or Target, falls into each expected range, and then adds the text for each target.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Dim cell As Range
  
  Application.EnableEvents = False
  
  If Not Intersect(Range("B27:B32"), Target) Is Nothing Then
    For Each cell In Target
      If cell.Value = "" Then cell.Value = "(Enter Custom Label)"
    Next cell
  End If
  
  If Not Intersect(Range("F10"), Target) Is Nothing Then
    For Each cell In Target
      If cell.Value = "" Then cell.Value = "MRN"
    Next cell
  End If
  
  If Not Intersect(Range("F11"), Target) Is Nothing Then
    For Each cell In Target
      If cell.Value = "" Then cell.Value = "Visit Number"
    Next cell
  End If
  
  Application.EnableEvents = True
  
' . . . . . .

End Sub

 

[ASBeard]

Thank you for correcting my mistake when I posted my code, obviously my first time here lol. Yes, I wanted the cell to revert to the default value when the cell is deleted but more importantly, I was going to add a macro button that cleared the cells all at once to reset my entire form, hence my problem when the code would fail and close the program. So in the long run, I would rather be able to clear all cells simultaneously and have them revert to their default values. How we get there doesn't matter to me. Thank you so much for your help and suggestions!

 

[Jeffrey Mahoney]

I would create two macros. One with the suggestions I made that you need to complete, and one where it resets all of those cells.

 

[ASBeard]

100%.... I have copied your code into the sheet and cannot seem to get it to work. I am not sure what I am doing wrong at this point.

 

[MARK858]

@ASBeard run the code below separately then see if the code @Jeffrey Mahoney posted runs

Sub Testrun()
  Application.EnableEvents = True
End Sub

 

[ASBeard]

Well Mark interestingly enough that did work.... for a minute and then it stopped working. I am having to constantly run Mark's code to get Jeff's code to run. The next problem is that Jeff's code does work provided I clear or delete on cell at a time. If I clear multiple cells, it fills them all with the same text instead of the settings outlined in the code. I have had issues before, but this one has me truly confused lol. Thank you both again for your time and help!