Error 1004 - when open workbook and named ranges

[jjohanni]

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-comfficeffice" /><o> </o>
I get this error whenever I open the workbook and there are any values set for the objects
<o> </o>
Run-time error ‘1004’:
Selected method of Range class failed
<o> </o>
Here is a sample section of code where I get an error
<o> </o>
Private Sub ComboBox8_Change()
'Unprotect sheet
Call UnlockSheet
<o> </o>
'Lock display to speed up
Application.ScreenUpdating = False
<o> </o>
'Clear Cell Contents and Borders and Lock
'Contents
Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").Select
Selection.ClearContents
<o> </o>
<o> </o>
<o> </o>
The error occurs where the named ranges come in “SFRACLabel…”
<o> </o>
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> </o>
Solution A. is there any way to stop excel from running this code at open
<o> </o>
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> </o>
Any help would be appreciated.
<o> </o>
Thanks,
JJohanni<o></o>

 

[jasonb75]

Welcome to the board!

Why not just bypass selection?

Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").ClearContents

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

Application.Goto Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4")
Selection.ClearContents

 

[jjohanni]

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-comfficeffice" /><o></o>
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></o>
The results may also depend upon the values of other objects in conjunction with their own value.<o></o>
<o> </o>
I want the sheets to be able to be filled out and saved<o></o>
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></o>
<o> </o>
In the code I am only using named ranges scoped to each worksheet to facilitate this<o></o>
<o> </o>
The only named ranges scoped to the workbook are ones which fill list boxes<o></o>
<o> </o>
<o> </o>
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

[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> </o>
The error occurs where the named ranges come in “SFRACLabel…”

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

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></o>
Error 424 : Object Required<o></o>
<o> </o>
<o> </o>
<o> </o>

[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> </o>

the error highlights “If ComboBox5.Text = "3 Tier" ”<o></o>
<o> </o>
<o> </o>
Some of the sheets have the same object names<o></o>
<o> </o>
<o> </o>
This workbook works fine once it is open<o></o>
<o> </o>
<o> </o>
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></o>
<o> </o>
My file is hosted here<o></o>
http://www.box.net/shared/161yzmqdlf<o></o>

Any help would be appreciated.

Thanks,
JJohanni <o></o>

 

[jjohanni]

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