Runtime Error "1004" Select Method of Range Class Failed
 

Hi -

Currently I have formatting macros attached to various Comboboxes and
Toggle Buttons. I copied one of the macros below. The problem I am
having now is when I try to insert cells on a different tab, I get the
Runtime Error 1004. When I click on debug, it highlights the selected
range of the macro (line 2 below). I can only make changes if I
disable the macros. Can this be fixed?

Application.ScreenUpdating = False
Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25").Select
With Selection
If Worksheets("Master List").Range("CA26") = "% of Sales" Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End If
If Worksheets("Master List").Range("CA26") = "Total $" Then
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""_);_(@_)"
End If
End With

Thanks in advance for your help.

Stephen

Runtime Error "1004" Select Method of Range Class Failed
 

You can only select cells on the active sheet. OTOH, you don't need to
select at all:

With Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25")
Select Case Worksheets("Master List").Range("CA26").Value
Case "% of Sales"
.NumberFormat = "0.00%"
Case "Total $"
.NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* - _);_(@_)"
Case Else
.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End Select
End With


In article ,
(Stephen) wrote:

Hi -

Currently I have formatting macros attached to various Comboboxes and
Toggle Buttons. I copied one of the macros below. The problem I am
having now is when I try to insert cells on a different tab, I get the
Runtime Error 1004. When I click on debug, it highlights the selected
range of the macro (line 2 below). I can only make changes if I
disable the macros. Can this be fixed?

Application.ScreenUpdating = False
Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25").Select
With Selection
If Worksheets("Master List").Range("CA26") = "% of Sales" Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End If
If Worksheets("Master List").Range("CA26") = "Total $" Then
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""_);_(@_)"
End If
End With

Thanks in advance for your help.

Stephen

Runtime Error "1004" Select Method of Range Class Failed
 

I tried the script below and it works. However, now when I try to
insert cells, Excel locks up. When I right click and select close on
the program, it asks if I want to save my changes. I've tried it on
different computers with the same result.

JE McGimpsey wrote in message ...
You can only select cells on the active sheet. OTOH, you don't need to
select at all:

With Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25")
Select Case Worksheets("Master List").Range("CA26").Value
Case "% of Sales"
.NumberFormat = "0.00%"
Case "Total $"
.NumberFormat = _
"_($* #,##0_);_($* (#,##0);_($* - _);_(@_)"
Case Else
.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End Select
End With


In article ,
(Stephen) wrote:

Hi -

Currently I have formatting macros attached to various Comboboxes and
Toggle Buttons. I copied one of the macros below. The problem I am
having now is when I try to insert cells on a different tab, I get the
Runtime Error 1004. When I click on debug, it highlights the selected
range of the macro (line 2 below). I can only make changes if I
disable the macros. Can this be fixed?

Application.ScreenUpdating = False
Worksheets("Roll-up by RSM").Range("D13:G17,I13:L17,D21:G25").Select
With Selection
If Worksheets("Master List").Range("CA26") = "% of Sales" Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"
End If
If Worksheets("Master List").Range("CA26") = "Total $" Then
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($*
""-""_);_(@_)"
End If
End With

Thanks in advance for your help.

Stephen

Runtime Error "1004" Select Method of Range Class Failed
 

There's nothing inherent in the code itself that would cause XL to 'lock
up'.

Where is this code located? Can you set breakpoint(s) and step through
the code to see where the 'lockup' occurs?




In article ,
(Stephen) wrote:

I tried the script below and it works. However, now when I try to
insert cells, Excel locks up. When I right click and select close on
the program, it asks if I want to save my changes. I've tried it on
different computers with the same result.

Runtime Error "1004" Select Method of Range Class Failed
 

I removed "Application.ScreenUpdating = False" from the code and it
works fine now. The code was located on the sheet with the toggle
button and combo box. I'm not sure why it had an effect on the other
sheets.

THANKS FOR ALL YOUR HELP!!!


JE McGimpsey wrote in message ...
There's nothing inherent in the code itself that would cause XL to 'lock
up'.

Where is this code located? Can you set breakpoint(s) and step through
the code to see where the 'lockup' occurs?




In article ,
(Stephen) wrote:

I tried the script below and it works. However, now when I try to
insert cells, Excel locks up. When I right click and select close on
the program, it asks if I want to save my changes. I've tried it on
different computers with the same result.