I have a form that requires user input to a number of boxes.

As each is updated I would like to update the array element to which it
relates.

I have stated my array as public in the code that calls the userform, but as
soon as I'm in form code I get a 'Subscript out of range' error.

Any ideas where I'm going wrong ?

Paul,

As usual, post your code.

Mike

"Paul" wrote:

I have a form that requires user input to a number of boxes.

As each is updated I would like to update the array element to which it
relates.

I have stated my array as public in the code that calls the userform, but as
soon as I'm in form code I get a 'Subscript out of range' error.

Any ideas where I'm going wrong ?

OK - Here goes
I've tried to make the code as short as possible without losing the thread.

In the master code

Public myFilters()
' This holds details of the columns, comparison types and comparison text
the user wants when consolidating a number of files

I have a form with
- 4 comboboxes, populated with column letters#
- 4 comboboxes, populated with the comparison types (Equal to, etc.)
- 4 textboxes, where the user can place the comparison text

I have populated three arrays with details of the controls in the form
myComboBox, myTypeBox and myTextBox
These are used to determine whether the boxes are visible or not during form
processing, and work perfectly

As part of the exit from the form I want to place the contents of the 12
boxes into the array for further processing - as follows :
For nCount = 1 To 4
myFilters(0, nCount) = myComboBox(nCount).Value
myFilters(1, nCount) = myTypeBox(nCount).Value
myFilters(2, nCount) = myTextBox(nCount).Value
Next

I am given an 'Automation Error - catastrophic failure' message
The Watch on myComboBox, myTypeBox and myTextBox then shows no variables
I have tried this piece of code within the form code, and outside the form
code - with exactly the same result.

I hope that's enough to get the gist of the problem



"Mike H" wrote:

Paul,

As usual, post your code.

Mike

"Paul" wrote:

I have a form that requires user input to a number of boxes.

As each is updated I would like to update the array element to which it
relates.

I have stated my array as public in the code that calls the userform, but as
soon as I'm in form code I get a 'Subscript out of range' error.

Any ideas where I'm going wrong ?

Mike

I've found it !!

I had a Dim statement within my master code which seemed to be overriding
the public statement.

Remove the DIM and all is well.

Thanks anyway for taking the time

"Mike H" wrote:

Paul,

As usual, post your code.

Mike

"Paul" wrote:

I have a form that requires user input to a number of boxes.

As each is updated I would like to update the array element to which it
relates.

I have stated my array as public in the code that calls the userform, but as
soon as I'm in form code I get a 'Subscript out of range' error.

Any ideas where I'm going wrong ?

I had a Dim statement within my master code which seemed to be overriding
the public statement.

What you have encountered is an illustration of a programming concept
known as "scope". See http://www.cpearson.com/Excel/Scope.aspx for a
full discussion as it applies to variables, classes, and procedures.

Cordially,
Chip Pearson
Microsoft MVP
Excel 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Fri, 13 Nov 2009 07:17:02 -0800, Paul
wrote:

Mike

I've found it !!

I had a Dim statement within my master code which seemed to be overriding
the public statement.

Remove the DIM and all is well.

Thanks anyway for taking the time

"Mike H" wrote:

Paul,

As usual, post your code.

Mike

"Paul" wrote:

I have a form that requires user input to a number of boxes.

As each is updated I would like to update the array element to which it
relates.

I have stated my array as public in the code that calls the userform, but as
soon as I'm in form code I get a 'Subscript out of range' error.

Any ideas where I'm going wrong ?