Control arrays

[steven_b46]

Hi,

I've got a sheet with about 94 activex controls (combo box, list box, checkbox, option button, etc.)

I tried creating a control array with my controls:

Dim cArr(1-94) as Control

Set cArr(1) = txtOption1
set cArr(2) = cboFile
...

This I put into the sheet's activate event. When I run it I get a runtime error 13 Type mismatch and it takes me to the 1st set line. What am I doing wrong?

Thanks!

[hafizimran]

Use Object instead of Control

[stnkynts]

The syntax 1-94 is incorrect. It would be "1 to 94", which technically is still flawed as arrays usually start at 0. So 0 to 93. Defining it as variant is not neccesary if the type is the same amongst all the variables but I don't know what you are trying to do. If you are trying to put the names of your controls, as a string, into an array then I guess you can do it that way (Note: you would not use "Set" and you would need quotations around the name. You could remove the variant and use as String). There is a different way to loop through all the controls on a sheet if that is what you are looking for.

For an exact answer please clarify your intentions.

[StephenR]

An array can start at 1 so 1 to 94 will do, but as stnkynts says depends on what you are doing. If you are storing the names, the array would be of String type.

[steven_b46]

sorry the 1-94 was typo. I changed it to variant as suggested, which isn't working. What I'm trying to do is create a tab structure, so users can tab from field to field.

So I'm creating a keydown event for all my controls for tabbing forward (or backward).

a=3
If KeyCode=9 and Shift=1 then
cRan(a-1).select 'defined as a series of ranges (take to previous range that control is in)
cArr(a-1).setFocus 'take the user to the previous control
End if
If KeyCode=9 and Shift=0 then
cRan(a+1).select 'Select next range
cArr(a+1).setFocus 'Select next control (setfocus isn't available in intellisense with type variant, plus I now get a Run time error 91 (Object Variable or With block variable not set and it highlights the line that cArr is on.)
End If

which I could create a function passing the value of "a" to. Something like tabMe(3) (I'm at the 3rd control)

It be great if we could come up with something that would handle all 94 without having to to create 94 separate KeyDown events, but I'd be happy with this. I hope I explained this so you can understand.

Thanks!

[steven_b46]

bump...a...de...bump

[Norie]

Do you already have code for tabbing between fields/controls?

[steven_b46]

I do, but that is to manually go to the control:

Please Login or Register  to view this content.

But I manually have to do this for every control and its very tedious. Any ideas?

[Norie]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[steven_b46]

Sorry for not marking code, I edited the previous message.

[steven_b46]

anyone have any help to offer? Thanks in advance. Its not just this one workbook. I've got 8 that are similar with a similar amount of controls.

Just a note, I've started doing this manually. This is what I've come up with

Please Login or Register  to view this content.

The reason for the Got focus is if the user clicks on a control, the number needs to be set to the number of that control. I'm setting a gotfocus and keydown for each control (94) of them. I'm having to look up the previous and next control name and manually type them in.

[Kyle123]

What does selCell do?

You can automate this, but only if you can come up with a strategy for which controls should come next in the tab order. If the only order is in your head, then you'll be stuck doing it as you are now. If you can rename the controls or use the tag property to store their tab order, code can read this to understand what should come next