Error 1004 Select method of Range class failed
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Error 1004 Select method of Range class failed
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.
On Error GoTo Canceled
Or is there a better way
I believe I could use On Error GoTo Canceled
Or is there a better way
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
Why do you use Target.Select in your Worksheet_Change event procedure?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
I use
Code: Select all
If Not Intersect(Target, Range("A1,C3,F5")) Is Nothing Then
Target.Select
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
OK here you go, first off are you sitting down
I'm going to use some foul words
Here it is
I know that merged cells are not very popular
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
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
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)
Target.Select
AutoFitMergedCellRowHeight
with
AFMCRH Target
(cf the code at the end of the post I mentioned)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
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.
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.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
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.
Application.ScreenUpdating = False
at the beginning, and
Application.ScreenUpdating = True
at the end of AFMCRH.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
I made changes as you have suggested
I am receiving an error message on line
With rng.MergeArea
I am receiving an error message on line
With rng.MergeArea
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
ABabeNChrist wrote: on line
With rng.MergeArea
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
Nothing changed, Same error
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
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?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
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?
Waa la, very cool its works. Now I’m wondering why you suggested that I change my previous code for this new code?
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 1004 Select method of Range class failed
The code that I suggested doesn't select cells. That's usually more efficient.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
Thank you Hans
For your assistance and I wanted to mention that my original request about my error message is no more.
For your assistance and I wanted to mention that my original request about my error message is no more.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Error 1004 Select method of Range class failed
Hi Hans
I have been making changes to my workbook and then came across a sheet that uses this method that will skip rows
I made some adjustments, let me know if I did this correctly
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
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