Issue with EntireRow.Delete

[dareman93]

I'm having an issue with deleting a row at the end of a User Form code. This is part of a much larger workbook that serves to track water and wastewater assets for DPW staff. It has multiple worksheets that serve different functions, but the overall goal is to alert the DPW staff when maintenance is due and allow them to update asset conditions when work is done on them so that they can better predict and plan for when assets need to be replaced. Among other worksheets, the workbook contains a master list of all of the assets to track when maintenance is due for each asset and individual worksheets for the various types of assets (for example, one sheet just has data about water mains on it, another has just valves, another just hydrants, etc.).

This particular part of the workbook is intended to allow the user to remove an asset from the master list of all assets in the event that it is decommissioned or demolished. That part of the code works perfectly. However, in addition to removing the asset from the overall master list, the user can select from 4 options on the User Form of what to do with the remainder of the asset data on its individual asset class worksheet. The user can select to either:

• Do no action (leave the asset alone on it's individual asset worksheet).
• Mark the asset as being decommissioned (or demolished) by changing the text format to strikethrough (which, by conditional formatting, will highlight that asset's row in red).
• Mark the asset as being demolished (or decommissioned) by hiding the asset's row.
• Remove the demolished (or decommissioned) asset from the individual asset worksheet by deleting it's entire row (and then inserting a new row below the existing list of assets, copying and pasting formulas and conditional formatting to allow new assets to be added by the user in the future).

The issue I'm running into is with the final option. The code for the first three works perfectly. However, if the 4th option is selected to delete the asset's entire row, the VBA code is run through the point where the entire row is deleted and then seems to exit the subroutine without performing the remaining steps. The asset's row is deleted, but a new row is not inserted, nothing is copied, and the User Form is not unloaded (it's still visible on the screen and you can click the button to try to run it again). When I walked through the code in debugging mode, the same thing happens - it processes the .EntireRow.Delete command and then seems to bail out of the subroutine. The pertinent portion of the code is as follows:

UpdateAssetClassWS:
'Updating the selected asset's individual asset class worksheet in the manner chosen by the
'OptionButton selected on the UserForm.
    With wsAsset
        .Activate
        .Unprotect
    End With
    If Me.OptionButton_NoAction.Value = True Then
        'No action is necessary on the individual asset class worksheet
        wsAsset.Protect
    ElseIf Me.OptionButton_Highlight.Value = True Then
        With wsAsset.Range("A" & wsVBA.Range("Row_Asset").Value & ":" & LastColUsed & wsVBA _
            .Range("Row_Asset").Value)
            .Font.Strikethrough = True
            .FormatConditions(1).StopIfTrue = False
            .FormatConditions(1).StopIfTrue = True
        End With
        wsAsset.Protect
    ElseIf Me.OptionButton_Hide.Value = True Then
        wsAsset.Range("A" & wsVBA.Range("Row_Asset").Value).EntireRow.Hidden = True
        wsAsset.Protect
    ElseIf Me.OptionButton_Delete.Value = True Then
        wsAsset.Range("A" & wsVBA.Range("Row_Asset").Value).EntireRow.Delete
        wsAsset.Range("A" & "LastAssetType" + 1).EntireRow.Insert
        wsAsset.Range("A" & "LastAssetType" + 2).EntireRow.Copy
        wsAsset.Range("A" & "LastAssetType" + 1).EntireRow.PasteSpecial xlPasteAll
        wsAsset.Protect
    Else
        MsgBox "There is an unanticipated error in the VBA code for the Remove Asset function " _
            & "as the User should not have been able to click the 'Submit - Update O&M Master " _
            & "List' button without first selecting one of the option buttons." & vbCrLf _
            & vbCrLf _
            & "Please note the wording of this error, close the workbook without saving, " _
            & "and contact Jeff Edwards for debugging. He can be reached at ###-###-#### or " _
            & "xxxxxxxxx@xxxxxxxxxxxx.com.", vbOKOnly + vbCritical, "Unanticipated Error"
        Unload Me
        Exit Sub
    End If

'Preparing to exit the subroutine by closing the user form, which will trigger the
'UserForm_QueryClose subroutine to reset all of the pertinent variables on the VBA Factors
'worksheet to prepare for the next use of the Remove Asset function.
    Unload Me

I've searched and can't find any posts about similar issues. I've tried debugging in about every way I could think of (including adding the wsAsset.Activate statement to get rid of a 1004 error, un-hiding all columns before the row is deleted, commenting out everything below the EntireRow.Delete command except Unload Me, putting the Unload Me command right after the EntireRow.Delete command, changing from the "Range" method of selecting the row to delete to the "Row" method of selection, etc.). Everything I've tried still has the subroutine exiting right after processing the EntireRow.Delete command with the User Form still showing, and I'm out of ideas. Help!?!?!?

Thanks!
Jeff

 

[MARK858]

If LastAssetType is a variable then it shouldn't be in quotes (can't tell really as you don't show where it is defined, but seeing as it should be a number I assume it is a variable).

I would have thought it would have errored.

 

[dareman93]

If LastAssetType is a variable then it shouldn't be in quotes (can't tell really as you don't show where it is defined, but seeing as it should be a number I assume it is a variable).

I would have thought it would have errored.

That's a nice catch - I can only plead that I was blind from having stared at it for so long trying to figure out what was wrong since Excel gave no errors. The LastAssetType is a variable that holds the value of the last row that has an actual asset in it (there are a few hundred "empty" rows below that for future assets that contain all the necessary formulas, but they remain blank with conditional formatting until a name for the new asset is entered in Column A). That is the purpose for wanting to insert a row and copy/paste the formulas when a row is deleted - to maintain space for future assets instead of running the risk that they will eventually delete enough rows that all of the preset rows are gone.

Alas, while that was an issue and has since been corrected, it is still doing the same thing - exiting the subroutine as soon as the .EntireRow.Delete line is processed with no error or anything else.

Any other ideas?

 

[MARK858]

Can you add the message boxes below, run your code and let us know which (if any) of the message boxes appear please (make sure any error handling code is removed first please).

    ElseIf Me.OptionButton_Delete.Value = True Then
       MsgBox "Reached me 1"
        wsAsset.Range("A" & wsVBA.Range("Row_Asset").Value).EntireRow.Delete
        wsAsset.Range("A" & LastAssetType + 1).EntireRow.Insert
        wsAsset.Range("A" & LastAssetType + 2).EntireRow.Copy
        wsAsset.Range("A" & LastAssetType + 1).EntireRow.PasteSpecial xlPasteAll
        wsAsset.Protect
       MsgBox "Reached me 2"
  Else

 

[dareman93]

Can you add the message boxes below, run your code and let us know which (if any) of the message boxes appear please (make sure any error handling code is removed first please).

    ElseIf Me.OptionButton_Delete.Value = True Then
       MsgBox "Reached me 1"
        wsAsset.Range("A" & wsVBA.Range("Row_Asset").Value).EntireRow.Delete
        wsAsset.Range("A" & LastAssetType + 1).EntireRow.Insert
        wsAsset.Range("A" & LastAssetType + 2).EntireRow.Copy
        wsAsset.Range("A" & LastAssetType + 1).EntireRow.PasteSpecial xlPasteAll
        wsAsset.Protect
       MsgBox "Reached me 2"
  Else

I went 1 step further and put a similar MsgBox message between command below the ElseIf statement. It only made it to the first message box (Reached me 1) and didn't display the second MsgBox message I put after the .EntireRow.Delete command:

Any other ideas would be greatly appreciated as I'm completely stumped. Thanks again!

Jeff

 

[RoryA]

Bit of a long shot, but do you have any conditional formatting rules that use UDFs?

 

[dareman93]

Bit of a long shot, but do you have any conditional formatting rules that use UDFs?

I do have a user defined function that allows conditional formatting (highlighting the cell in dark red) if the text format is strike-through.

Function StrikeThru(rng As Range)
'This function serves to allow cells to be conditionally formatted based on whether the font
'includes the "strikethrough" option or not. It will serve to override any other conditional
'formatting, allowing the conditional formatting in cells that include color (such as the
'overall condition rating, consequence of failure, and asset criticality) to be over-ridden by
'the desired "192 red" coloring in the cell with black text that is marked as strikethrough.
'
'The formula to access this fuction is "=StrikeThru(B1) = True" where cell B1 is the cell that is
'being tested (i.e. the beginning of the range that the condition will be applied to. Insert that
'formula in the conditional formatting as the equation to evaluate the cell based on, set the
'cell format to the desired 192 red background, black text, and strikethrough font, apply the
'conditional format to the entire range of the workbook, place it at the top of the conditional
'formatting list, and check the "Stop if true" box.

    StrikeThru = rng.Font.Strikethrough

End Function

This conditional formatting rule was required because of other conditional formatting included in some of the cells that changed colors based on the cell value. If the VBA code for decommissioning an asset applied both the strikethrough font and changed the color of the cell, those cells that contained other conditional formatting did not display the desired dark red background color. Could conditional formatting and the UDF shown above be causing the issues I'm seeing?

Jeff

 

[RoryA]

Yes it could. It's about the only thing I've seen that can cause silent termination of code. Try temporarily amending that code to just return True without testing the font, and see if the problem resolves itself.

 

[dareman93]

Yes it could. It's about the only thing I've seen that can cause silent termination of code. Try temporarily amending that code to just return True without testing the font, and see if the problem resolves itself.

Sorry for the delay - I got pulled off working on other projects and am just jumping back into this. I did as you said and amended the code to just return True without testing the font and the problem does resolve itself. It steps through the remainder of the code, giving me all 6 "Reached me" messages, unloads the form, and terminates properly. So, that is definitely the issue.

Next question - is there a way to address that issue with the UDF where it can still be used? Or should I just delete the UDF and its associated Conditional Format and resign myself to the idea that, when an asset is marked as decommissioned (i.e. font is changed to strikethrough), I will have 3 cells that have a different color and don't have strikethrough text?

Thanks!

Jeff

 

[RoryA]

You can do it with an XLM function instead. Select A1 on any sheet, then define a name as IsStruckThrough using =GET.CELL(23,!A1) as the refers to:

then in your conditional formatting formula, just use =IsStruckThrough with no brackets or cell reference.