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

 

[dareman93]

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:
View attachment 101884
then in your conditional formatting formula, just use =IsStruckThrough with no brackets or cell reference.

That works, but I have to plead ignorance in XML - this is my first foray into it, so maybe I'm doing something wrong. I put the Get.Cell function in as shown in the name definition box for IsStruckThrough and applied that as the conditional formatting. It seems to work as far as highlighting the right rows, but it throws off the rest of the formatting. Here is what a portion of one of the asset workbooks looks like before the conditional formatting is applied:

and, after the conditional formatting is applied (to override the conditional formatting resulting in the coloring in columns AN, AP, and AS), this is what I have:

Note the different fonts in columns B, C, U, V, W, AL, AN, AP, and AS and the fact that, in the highlighted row, none of those columns is struck through or in gray text anymore, either.

Again, I know very little about XML, so this may be something stupid I did, or just that this workbook is way too complicated to try to figure out what is causing issues like this, but I thought I'd see if you had any ideas before I give up and just leave columns AN, AP, and AS colored as shown in the first screenshot above.

Thanks!

Jeff

 

[RoryA]

That can't have anything to do with the XLM itself as all that does is return True/False. I suspect your CF rule format has been incorrectly applied.