Please Help (Arrays)

[Sforster]

I know I asked the same question about two days ago but I could really do with some help on this.

Without changing names to the same or making the index values the same (or copy and paste) is there any way in code to make a number of command buttons into an array that could be used to return an index value.

i.e. something like

sub myarray

dim mybuttonarray(3) as control/object

mybuttonarray = (cmdone,cmdtwo,cmdthree)

end sub

sub mybuttonarray_click (index as integer)

then code dependent on which button is clicked.....

end sub

Please help...

cheers
Steve

[coox]

So what have you got against control arrays? Is there some reason you can't use one? Because that is exactly what you need to do I reckon...

[Sforster]

Because as far as I know you can't create control arrays in Excel !! i.e. change the names to the same or change the index values to the same

Coox in another post you stated that you could aid people with excel questions, please help me then......

Steve

[coox]

Ok. Now, so far as getting all buttons to call one sub, and then that sub doing different things depending on who called it (just put three buttons on a form):

Private Sub CommandButton1_Click()
 Master ActiveControl.Name
End Sub

Private Sub CommandButton2_Click()
 Master ActiveControl.Name
End Sub

Private Sub CommandButton3_Click()
 Master ActiveControl.Name
End Sub


Sub Master(myName As String)
 MsgBox myName & " was pressed"
End Sub

Does that do what you want?

[Edited by coox on 08-23-2000 at 05:45 AM]

[Yonatan]

You can take this even further, and pass the entire CommandButton control to the Master sub, then you'd be able to change its properties.

Sub Master(MyControl As CommandButton)
    MyControl.Caption = "You clicked me! OUCH! Don't do that!" :rolleyes:
End Sub

Private Sub CommandButton1_Click()
    Call Master(ActiveControl)
End Sub

Private Sub CommandButton2_Click()
    Call Master(ActiveControl)
End Sub

Private Sub CommandButton3_Click()
    Call Master(ActiveControl)
End Sub

[Sforster]

I can see what you are saying but I was trying to avoid using

command1_click
command2_click etc..

As I have 26 buttons. They are not actually on a form but are embedded into the spreadsheet and when the user presses the buttons one of the 26 spreadsheets are shown. I was just trying to think of a way that it could be done without having to type comm2_click comm3_click etc...

What Yonatan states about passing the commandbutton itself is fine except it doesn't solve the problem of

"Again repeating myself"

Command1_click
Command2_click etc...

Is there not a way that I can just call

MYBUTTONARRAY_click (INDEX as Integer)

Where MYBUTTONARRAY is a collection of commandbuttons

Or am I just completely losing it !!!

Steve

[Yonatan]

As you said: Excel isn't sophisticated enough to allow you to use control arrays.
So use some sub which will handle all of them.
However, you will have to add (at least) 78 lines to call it from each CommandButton.

Life's tough, but Microsoft is tougher.

[coox]

Listen, all you got to do is this -

1. Go to your first sheet, view the Control Toolbox, go into Design mode.

2. Locate each command button and, still in design mode, double-click on it. This will generate the basic click event (which is all you want) for each button, eg:

Private Sub CommandButton1_Click()

End Sub

3. In the VB editor, turn Full Module View (at the bottom their, looks a bit like the Align... buttons in Excel and Word) and you'll see all the click events for all the buttons all at once.

4. Key (or copy and paste from this posting) Yonatan's code (the call Master(ActiveControl) bit - nice one Yonatan!) into one click event, then paste it into all the others - take you about 30 seconds. And you won't have to edit a thing...

That's it, couldn't really be simpler. I don't think there's a way of doing what you're after, ie having a single bit of code that somehow recognizes and deals with all the click events in the whole workbook. Anyway, that should work fine...

[Sforster]

Oh well !! thanks for the responses anyway.

Steve

[coox]

Blimey, what's my spelling like - just realised I put "their" instead of "there"...

Steve, you sound like a right lazy arse... oh well, I suppose computer's are supposed to do all the work for us eh?

[Sforster]

Sorry COOX, I must have been writing my response at the same time....

Thanks but what you have said is pretty much what I had already done.

Cheers Anyway
Steve

[Sforster]

Not just a lazy arse !!

Pretty much lazy everything, elbows, arms, hands etc....

Especially brain !

Steve