Error 1004 - when open workbook and named ranges

J

jjohanni

New Member
Joined
Jun 3, 2011
Messages
3
I am creating a group of interactive excel forms
They are all in one workbook, but the coding is extensive with many VBA objects on each sheet
some of the sheets have over 25 pages of code
I am also using named ranges for any cell references scoped to individual worksheets to enable copying of the sheets and insertion of rows
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
I get this error whenever I open the workbook and there are any values set for the objects
<o:p> </o:p>
Run-time error ‘1004’:
Selected method of Range class failed
<o:p> </o:p>
Here is a sample section of code where I get an error
<o:p> </o:p>
Private Sub ComboBox8_Change()
'Unprotect sheet
Call UnlockSheet
<o:p> </o:p>
'Lock display to speed up
Application.ScreenUpdating = False
<o:p> </o:p>
'Clear Cell Contents and Borders and Lock
'Contents
Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").Select
Selection.ClearContents
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
The error occurs where the named ranges come in “SFRACLabel…”
<o:p> </o:p>
When each sheet is selected the code runs fine
But it appears that when the workbook is opened it runs all the code from all the sheets and thus only one sheet will be selected
<o:p> </o:p>
Solution A. is there any way to stop excel from running this code at open
<o:p> </o:p>
Solution B. is there any way to activate each sheet and allow the code to run
(I tried using a FOR loop already in the workbook open section to sequentially activate each sheet and it didn’t work)
<o:p> </o:p>
Any help would be appreciated.
<o:p> </o:p>
Thanks,
JJohanni<o:p></o:p>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Welcome to the board!

Why not just bypass selection?

Rich (BB code): 
Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").ClearContents

If you have need to select the range for other uses, try

Rich (BB code): 
Application.Goto Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4")
Selection.ClearContents
 
Upvote 0
I rewrote the question adding in some more information and a link to the file
the previous suggestion seemed to work for clearing data but I am also using other commands such as borders - also i am worried that it may be referencing the named range for the active sheet and not for the sheet which the code is associated with is this possible?


I am creating a group of interactive excel forms
They are all in one workbook, there are many VBA objects on each sheet<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
These objects change which other objects are displayed, which rows are visible / hidden (via named ranges scoped to worksheet), values of cells (via named ranges scoped to worksheet)<o:p></o:p>
The results may also depend upon the values of other objects in conjunction with their own value.<o:p></o:p>
<o:p> </o:p>
I want the sheets to be able to be filled out and saved<o:p></o:p>
I need to be able to copy the sheets to duplicate the form within the same workbook as necessary
I also need to be able to insert rows as necessary without causing error<o:p></o:p>
<o:p> </o:p>
In the code I am only using named ranges scoped to each worksheet to facilitate this<o:p></o:p>
<o:p> </o:p>
The only named ranges scoped to the workbook are ones which fill list boxes<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
I get this error whenever I open the workbook and there are any values set for the objects

Run-time error ‘1004’:
Selected method of Range class failed

Here is a sample section of code where I get an error
Code: 
[COLOR=black][FONT=Calibri]Private Sub ComboBox8_Change()[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]'Unprotect sheet[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]Call UnlockSheet[/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Calibri]'Lock display to speed up[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Calibri]'Clear Cell Contents and Borders and Lock[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]'Contents[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").Select[/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Calibri]Selection.ClearContents[/FONT][/COLOR][COLOR=black][FONT=Verdana]
…<o:p></o:p>[/FONT][/COLOR]
<o:p> </o:p>
The error occurs where the named ranges come in “SFRACLabel…”

<o:p></o:p>
I also have the same type of code for many other commands such as changing borders, protection, hidden property, and value<o:p></o:p>

When the sheet which the code is assigned to is selected and the workbook is saved, that part of the code runs fine

Then I get an error <o:p></o:p>
Error 424 : Object Required<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Code: 
[COLOR=black][FONT=Verdana]Private Sub ComboBox4_Change()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]'Error Check for other box with more options<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    If ComboBox5.Text = "3 Tier" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Range("FIRRow1,FIRRow2,FIRRow3").Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Selection.EntireRow.Hidden = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    Range("FIRRow4,FIRRow5").Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]        Selection.EntireRow.Hidden = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]…<o:p></o:p>[/FONT][/COLOR]
<o:p> </o:p>

the error highlights “If ComboBox5.Text = "3 Tier" ”<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Some of the sheets have the same object names<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
This workbook works fine once it is open<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Solution A. is there any way to stop excel from running this code at open and only run at an action (e.g. my code is under ComboBox#_Change shouldn’t it only be run/read when the combobox is changed?)

Solution B. is there any way to activate each sheet and allow the code to run for that sheet
(I tried using a FOR loop already in the workbook open section to sequentially activate each sheet and it didn’t change anything)<o:p></o:p>
<o:p> </o:p>
My file is hosted here<o:p></o:p>
http://www.box.net/shared/161yzmqdlf<o:p></o:p>

Any help would be appreciated.

Thanks,
JJohanni <o:p></o:p>
 
Upvote 0
the first problem seems to be fixed now
i added

<!-- BEGIN TEMPLATE: bbcode_code -->
Code:
<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE>
Me.Activate</PRE>
<!-- END TEMPLATE: bbcode_code -->before the main code for each ComboBox_Change

buti still have the problem with the 424 error

the numbering pattern of the comboboxes is the same where the error occurs
the number of the combobox with the code is one less that the one which is referenced throught the if statement

does excel load them in order one at a time
and run the code before loading the next one?

By objects i mean VBA objects or controls e.g. combobox
by value i mean the value property of the object

so I have a combobox set up as a list box with a user specified value

before anything is selected it has value of ""
after something is selected the value changes

I don't have anything in the workbook open event

I dont have any sheet events either

the only event that is triggering is the ComboBox_Change event
and that is when i get the error

This only occurs when something was selected from the combobox list and the workbook saved
the error occurs upon re-opening
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Error on method intersect
Replies
2
Views
122
Beneindias
B
sharky12345
  • Question Question
Error 1004 - Copy method of Worksheet class failed
Replies
1
Views
131
yoseeeph
Y
D
  • Solved
copy paste using Range(Cells,Cells) - error 1004
Replies
5
Views
246
RoryA
RoryA
S
  • Question Question
Open Specific Sheet while file open
Replies
9
Views
203
Logit
Logit
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
154
howard
H

Forum statistics

Threads
1,224,613
Messages
6,179,904
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top