Error 1004 and 424 upon workbook open

[jjohanni]

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

Private Sub ComboBox8_Change()
'Unprotect sheet
Call UnlockSheet

'Lock display to speed up
Application.ScreenUpdating = False

'Clear Cell Contents and Borders and Lock
'Contents
Range("SFRACLabel1,SFRACLabel2,SFRACLabel3,SFRACLabel4").Select
Selection.ClearContents
…

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

Private Sub ComboBox4_Change()

'Error Check for other box with more options
    If ComboBox5.Text = "3 Tier" Then
    Range("FIRRow1,FIRRow2,FIRRow3").Select
        Selection.EntireRow.Hidden = False
    Range("FIRRow4,FIRRow5").Select
        Selection.EntireRow.Hidden = True
    End If
…

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

[Richard Buttrey]

OK, one at a time...Select Method of Range class failed.

Are you sure the sheet is unprotected and all the four SFRAC range names are on the same sheet? What does 'UnlockSheet' actually do?

Do you really need to Select anyway. Have you tried just

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

Regards

[jjohanni]

Thank you very much for the quick reply

This is the code for protecting and unprotecting the sheet

Dim passtoggle As Integer

Sub UnlockSheet()
   If ActiveSheet.ProtectContents = True Then
   ActiveSheet.Unprotect Password:="*****"
   passtoggle = 1
   End If
End Sub
'
'
Sub LockSheet()
    If passtoggle = 1 Then
    ActiveSheet.Protect Password:="*****"
    End If
    passtoggle = 0
End Sub

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

Me.Activate

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?

[Richard Buttrey]

Hi,

Can you explain " I get this error whenever I open the workbook and there are any values set for the objects". What do you mean by values and objects?

Do you have any code in the Workbook Open event? Unless you do I can't see why any macro would automatically run when you open the workbook.

There are of course Sheet Activate, Sheet Change and Sheet Selection Change events, which can all trigger macros. It all depends when you want them to be triggered.

Regards

[jjohanni]

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

[Richard Buttrey]

Hi,

Are you able to upload the workbook, anonymised if necessary.

Regards

[jjohanni]

Here it is