Error 1004 and 424 upon workbook open

I am creating a group of interactive excel forms
They are all in one workbook, there are many VBA objects on each sheet
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)
The results may also depend upon the values of other objects in conjunction with their own value.

I want the sheets to be able to be filled out and saved
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

In the code I am only using named ranges scoped to each worksheet to facilitate this

The only named ranges scoped to the workbook are ones which fill list boxes


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

Please Login or Register  to view this content.

The error occurs where the named ranges come in “SFRACLabel…”

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

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

Please Login or Register  to view this content.

the error highlights “If ComboBox5.Text = "3 Tier" ”


Some of the sheets have the same object names


This workbook works fine once it is open


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)

My file is hosted here
http://www.box.net/shared/161yzmqdlf

Any help would be appreciated.

Thanks,
JJohanni

Thank you very much for the quick reply

This is the code for protecting and unprotecting the sheet

Please Login or Register  to view this content.

so yes the sheet is unprotected

and your recommended command seems to work for clearing contents but i still had problems with other commands

it seems to be fixed now however
i added

Please Login or Register  to view this content.

before the main code for each ComboBox_Change

but i 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


Thanks again

Here it is