Error 1004 Select method of Range class failed

[ABabeNChrist]

I have a docm that has mutable worksheets that I’m trying to clean up in a way that will prevent possible error messages from appearing from simple user error caused. Like for instance I’m using a Worksheet_Change with Target.Select. If I am typing within this selected target cell and were to select a different worksheet from visible list at bottom, but when making sheet selection I did not tab out of previous cell, I when then receive an error.

error 1004.JPG

I believe I could use
On Error GoTo Canceled
Or is there a better way

[HansV]

Why do you use Target.Select in your Worksheet_Change event procedure?

[ABabeNChrist]

I use

Code: Select all

    If Not Intersect(Target, Range("A1,C3,F5")) Is Nothing Then
        Target.Select

[HansV]

OK, but why?

[ABabeNChrist]

OK here you go, first off are you sitting down
I'm going to use some foul words
Here it is

Code: Select all

    If Not Intersect(Target, Range("A1,C3,F5")) Is Nothing Then
        Target.Select
        AutoFitMergedCellRowHeight
    End If

I know that merged cells are not very popular

[HansV]

If you're using the same code for AutoFitMergedCellRowHeight as in Post=9691, replace the lines

Target.Select
AutoFitMergedCellRowHeight

with

AFMCRH Target

(cf the code at the end of the post I mentioned)

[ABabeNChrist]

Hi Hans
I tried using this method AFMCRH Target that you have previously mentioned it just did not seem to function as smoothly or as efficiently, I'm not sure why. I did not receive an error or anything.

[HansV]

AFMRCH is called by AutoFitMergedCellRowHeight so they should operate similarly. You can try adding a line

Application.ScreenUpdating = False

at the beginning, and

Application.ScreenUpdating = True

at the end of AFMCRH.

[ABabeNChrist]

I made changes as you have suggested
I am receiving an error message

error 1004.JPG

on line
With rng.MergeArea

[HansV]

On which line?

[ABabeNChrist]

on line
With rng.MergeArea

[HansV]

Try this:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1,C3,F5")) Is Nothing Then
        For Each oCell In Intersect(Target, Range("A1,C3,F5"))
          AFMCRH oCell
        Next oCell
    End If
End Sub

[ABabeNChrist]

Nothing changed, Same error

[HansV]

The attached sample workbook doesn't cause an error for me. If it does for you, could you indicate what exactly you were doing when the error occurred?

CodeForMergedCells.xlsm

[ABabeNChrist]

Hi Hans
Sorry for returning so late, Had to work
I looked over the sample you provided. I think the code I was using seems to work more efficiently. It keeps the cell even with the amount of text that is entered, the code you provided over sizes the cells. I also noticed is that both codes will not work if more than 1 row is merged. You can merge all the columns you wish as long as they are in the same row.
I added to your sample workbook and entered the code I use on sheet2 as a comparison.

CodeForMergedCells.xlsm

[HansV]

Ah, I see I made a mistake in the code for AFMRCH. The line

For Each CurrCell In Selection

should be

For Each CurrCell In rng.MergeArea

Both your and my versions of the code are intended for horizontally merged cells only. They won't work correctly for vertically merged cells.

[ABabeNChrist]

Hi Hans
Waa la, very cool its works. Now I’m wondering why you suggested that I change my previous code for this new code?

[HansV]

The code that I suggested doesn't select cells. That's usually more efficient.

[ABabeNChrist]

Thank you Hans
For your assistance and I wanted to mention that my original request about my error message is no more.

[ABabeNChrist]

Hi Hans
I have been making changes to my workbook and then came across a sheet that uses this method that will skip rows

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    For Each rng In Target.Rows
        Select Case rng.Row
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 16, 21, 28, 37, 44, _
            51, 57, 63, 78, 84, 90, 102, 110, 132, 154, 161, 173, _
            180, 199, 206, 212, 217, 222, 228, 233, 238, 243, 249, _
            254, 260, 265, 281, 287, 294, 301, 308, 315, 320 'Rows that will be skipped
            ' Do nothing
        Case Else
            With rng
                Target.Select
                AutoFitMergedCellRowHeight
                .WrapText = True
                If .RowHeight < 19.5 Then
                    .RowHeight = 19.5
                End If
            End With
        End Select
    Next rng
End Sub

I made some adjustments, let me know if I did this correctly

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim oCell As Range
    For Each oCell In Target.Rows
        Select Case oCell.Row
        Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 16, 21, 28, 37, 44, _
             51, 57, 63, 78, 84, 90, 102, 110, 132, 154, 161, 173, _
             180, 199, 206, 212, 217, 222, 228, 233, 238, 243, 249, _
             254, 260, 265, 281, 287, 294, 301, 308, 315, 320    'Rows that will be skipped
            ' Do nothing
        Case Else
            With oCell
                AFMCRH oCell
                .WrapText = True
                If .RowHeight < 19.5 Then
                    .RowHeight = 19.5
                End If
            End With
        End Select
    Next oCell
End Sub