Error '1004': Application defined or Object defined error?

[websmythe]

Could anybody shed any light on why I'm getting a
Runtime Error '1004': Application defined or Object defined error?
I've been searching the net and found a note about using full naming,
eg: ActiveWorkbook.Sheets("Estimate").Range(BlankRngStr) ,
but it didn't fix it. The red line is the point that it starts to error out.
If I comment out the line it just moves to the next. Thanx

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
    
    Dim Msg, Style, Title, Response
    Dim ThisSum, ThisRngStr, BlankRngStr
    Dim SubTotalRng, TxtRng As Range
    
    ThisSum = WorksheetFunction.Sum(Target)
    
    Msg = "Current SubTotal = " & Format(ThisSum, "$#,###,##0.00") _
    & vbCrLf & vbCrLf & "Insert SubTotal into Sheet?"
    
        Style = vbYesNo + vbQuestion + vbDefaultButton2   ' Define buttons.
        Title = "SubTotal"    ' Define title.
        
        Response = MsgBox(Msg, Style, Title)
        Cancel = True
        
        If Response = vbYes Then    ' User chose Yes.
       
            ' get Address rng from selection
            ThisRngStr = ActiveWindow.RangeSelection.Address
            BlankRngStr = Mid(ThisRngStr, InStr(ThisRngStr, ":") + 1)
            
            Set SubTotalRng = ActiveWorkbook.Sheets("Estimate").Range(BlankRngStr)
            
            If SubTotalRng.Value <> "" Then
                Msg = "No Blank cell found at end of selection" & vbCrLf _
                & "Please include a blank cell at the end" & vbCrLf & "of your selection"
                Style = vbOKOnly + vbExclamation ' Define buttons.
                Title = "No Blank Cell Found"    ' Define title.
        
                Response = MsgBox(Msg, Style, Title)
        
            Else
                
                ' get the cell beside the Subtotal
                Set TxtRng = ActiveWorkbook.Sheets("Estimate").Cells(SubTotalRng.Row, SubTotalRng.Column - 1)
                
                ' place the word SubTotal in the cell to the left of the last cell
                [COLOR="Red"]TxtRng.Formula = ""[/COLOR]
                TxtRng.Font.Bold = True
                TxtRng.Value = "SubTotal"
                
                ' place SubTotal formula in last cell
                SubTotalRng.Formula = ""
                SubTotalRng.Font.Bold = True
                SubTotalRng.Value = "=SUM(" & ThisRngStr & ")"

            End If
        
        End If
    
End Sub

 

[Richard Schollar]

That line and the following are completely superfluous (you don't need them as you assign a value to the cell later on). Re-write to:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
    
    Dim Msg, Style, Title, Response
    Dim ThisSum, ThisRngStr, BlankRngStr
    Dim SubTotalRng, TxtRng As Range
    
    ThisSum = WorksheetFunction.Sum(Target)
    
    Msg = "Current SubTotal = " & Format(ThisSum, "$#,###,##0.00") _
    & vbCrLf & vbCrLf & "Insert SubTotal into Sheet?"
    
        Style = vbYesNo + vbQuestion + vbDefaultButton2   ' Define buttons.
        Title = "SubTotal"    ' Define title.
        
        Response = MsgBox(Msg, Style, Title)
        Cancel = True
        
        If Response = vbYes Then    ' User chose Yes.
       
            ' get Address rng from selection
            ThisRngStr = ActiveWindow.RangeSelection.Address
            BlankRngStr = Mid(ThisRngStr, InStr(ThisRngStr, ":") + 1)
            
            Set SubTotalRng = ActiveWorkbook.Sheets("Estimate").Range(BlankRngStr)
            
            If SubTotalRng.Value <> "" Then
                Msg = "No Blank cell found at end of selection" & vbCrLf _
                & "Please include a blank cell at the end" & vbCrLf & "of your selection"
                Style = vbOKOnly + vbExclamation ' Define buttons.
                Title = "No Blank Cell Found"    ' Define title.
        
                Response = MsgBox(Msg, Style, Title)
        
            Else
                
                ' get the cell beside the Subtotal
                Set TxtRng = ActiveWorkbook.Sheets("Estimate").Cells(SubTotalRng.Row, SubTotalRng.Column - 1)
                
                ' place the word SubTotal in the cell to the left of the last cell
                TxtRng.Font.Bold = True
                TxtRng.Value = "SubTotal"
                
                ' place SubTotal formula in last cell

                SubTotalRng.Font.Bold = True
                SubTotalRng.Formula = "=SUM(" & ThisRngStr & ")"

            End If
        
        End If
    
End Sub

 

[websmythe]

That line and the following are completely superfluous (you don't need them as you assign a value to the cell later on).

Didn't think of that. Thanx.
But the error then shifts to the following line.

I guess I shoud be writing the SUM statement to the formulla anyways, instead of the value.

' get the cell beside the Subtotal
Set TxtRng = ActiveWorkbook.Sheets("Estimate").Cells(SubTotalRng.Row, SubTotalRng.Column - 1)
 
' place the word SubTotal in the cell to the left of the last cell
TxtRng.Font.Bold = True
TxtRng.Value = "SubTotal"
 
' place SubTotal formula in last cell
SubTotalRng.Font.Bold = True
SubTotalRng.Formula = "=SUM(" & ThisRngStr & ")"

 

[Colin Legg]

Is that worksheet protected by any chance (with format cells ticked if TxtRng.Font.Bold doesn't throw an error)?

 

[websmythe]

Is that worksheet protected by any chance (with format cells ticked if TxtRng.Font.Bold doesn't throw an error)?

Bingo.
Got as circular reference as well, but I can deal with that.
Thanx