Change properties of multiple activex buttons in 3 columns with a loop and an array

[boardy]

I have a "Switchboard" sheet as the landing sheet on opening the workbook. It has 3 columns of buttons to take the user to whatever sheet or action they wish to access. All buttons are activex so I can be creative with them. They all work fine (I think), but are not related to cells, therefore alignment is subject to my shaky hands when working with them . I want a single button to align enact a macro which set width, heigth, left and top for each button and those numbers wi depend upon the colomn they are in. The attached code works fine for a single button, but I cannot use an array of button names to insert the name in the command to alter as I process each button through the loop. If i use a line of code for every button name, it would work fine and dandy, but I want a small routine that get repeated on each passthrough the loop with only the button name and some other minor parameters being changed(Top). The command to alter the Left, etc will not allow me to use a variable to specify the button name to be subjected to the change. The workbook will show the sheet and the reference table that provides the column to button names. The code is in button number 20.

[AlphaFrog]

Pro Tip: when you are manually aligning a button, use the keyboard arrow keys to move it precisely.

I didn't look at your file, but here is an example to loop through an array of names of Command buttons and align them to column C

Please Login or Register  to view this content.

[boardy]

Thank you for the code. I code (Program) for myself only and have no formal training. I use things I learned from yesteryear when OOP did not exist, therefore I never learned it and am struggling to understand all of the unique variations of it. I guess you could say I learn just what I need at the time. This example helped me a lot but is still missing one important part. My array could have up to 16 different buttons specified for one of the columns, and only 3 or 4 for another column. I want to be able to put in different button names according to the column selected. Specifically, I want to be able to assign a button name to a variable. I was loading the button names into an array of a size determined by the number of buttons, but can not use, for example, array(6) to provide the button name of whatever is in number 6 at the time of a run. I just realized that one problem might be with the array starting at (0) and my brain starting at (1), but I can fix that. I just need to know how to use the variable array() to supply the "CommandButton??" name to be acted on. You have supplied all the rest.
Thanks again for getting me this far.

[AlphaFrog]

I'm not sure what the question is?

This will reference the 3rd element in the array ("CommandButton3") by index number. You are correct, the array is zero-based by default.

Please Login or Register  to view this content.

You can put this at the very top of your code module (not in a procedure) and your array will be one-based by default.

Please Login or Register  to view this content.

[boardy]

I'm sorry. I assumed you had seen the workbook I attached. It shows what I have been talking about, but in lieu that I will attempt to explain the layout for you. I'm sure my question above will make more sense then.
On worksheet "Switchboard" ( carry over from MS Access), there are three verticle columns ( not necessarily aligned with cells). Column 1, column 2, and column3. Each has ActiveX buttons to assist the user in various things. Column 1 has buttons to do things like save the workbook to several places for backup purposes. That has CommandButtons 17, 18, 3 and 2. Column 2 has buttons to take the user to various forms for inputting or updating. This column has CommndButtons 4, 7, 5, 9 and 8. And column 3 has buttons for jumping to other (about 9) sheets. These Command buttons 1, 11, 12, 13, 14, 15, 16, 6 and 19. I use an input box to query which column needs to have its buttons aligned. I then access a table that shows the CommandButton Names and the column they appear in. Using for-next to loop through all entries I use if-then to select the button names for those in the selected column and increment a counter that tells me how many (correction- tells the program/code) how many buttons are in the column. At the same time, I store the name of each button that matches the column number. The names are stored in an array using the counter as the element of the array. Once this is done I am ready to make the changes, but I cannot specify the CommandButton name to be modified because I do not have something right to allow the use of an array element to provide the button name.

I hope that clarifies my problem. And yes your method does work but would require 3 different copies ( one for each column) and some would require a whole lot, currently, 9 or 10 entries. Each time I add something I would have to update the code. With my method, all I have to do is add a new entry to my table and the rest is self-adjusting, EXCEPT! It doesn't work. I'm sure OOP has some class to resolve this, I just don't know them or where to find them. Below, find the code I am using:


Private Sub CommandButton20_Click()

' Aligns all buttons in the System Button column.

Dim colSel As Integer
Dim I As Integer
Dim c As Integer
Dim Bname(15) As String
Dim x As Integer

c = 0

colSel = InputBox("Which column of buttons do you wish to align?" & vNewLine & "1 consists of System Operations Buttone " & vNewLine & "2 consists of Jumps to input forms" & vNewLine & "3 consists of Quick access to Worksheets")

If colSel <= 0 Or colSel >= 4 Then
MsgBox "Only the digit 1, 2 or 3 is acceptable. This entry did not meet that parameter. Aborting!"
Exit Sub
Else
Sheets("References").Activate
For I = 6 To 23 'Reset this to 6 to 23 when done testing

If colSel = Sheets("References").Cells(I, "S").Value Then
c = c + 1
Bname(c) = Sheets("References").Cells(I, "T").Value
GoTo Line1
Else
End If
Line1:
Next I


End If
For x = 1 To c
Debug.Print Bname(x)
Next x

Sheets("Switchboard").Activate




Dim vBtn As Variant, objBtn As Object
For Each vBtn In Array("CommandButton17", "CommandButton18", "CommandButton3", "CommandButton2")
Set objBtn = Me.OLEObjects(vBtn)
objBtn.Left = 70 'Columns("C").Left
Next

End Sub


I know the code is not neat but this is the development version.
Thanks again for you interest and help.

[boardy]

I just relized that Ibeing unexperienced, I forgot when I posted my original message that I left a macro in it for anyone to see my code. However, opening the workbook will see the xlxm extension and the the macro warning and like the rest of you, I would be hesitant to open that also. Stupid move on my part. Anyway the code is attached as document now. I will try to remove the code and change the workbook to a standard workbook extension so all will know no code embedded. Please forgive my stupidity!

[boardy]

Here is a clean version of the workbook

[AlphaFrog]

Please Login or Register  to view this content.

[boardy]

Thank you so much, Alpha, if I can call you by your first name! But seriously, that code works just fine. I got it yesterday and spent today analyzing it to the point that I now understand it (well, about 80%) and have added to it the code to allow me to set the Width, Height, Left, and Top. Yep, I said top. I set a variable for the Top value of the first button then position it and increment that variable by the height of the fist button and the spacing I want then go on to the next button. Workks fine and I have a much better understanding of Objects!

Again, Thank you for the help!

B T W , I forgot how to mark this as solved and can't find the way to do so. If anyone can help, please let me know.

[AlphaFrog]

You're welcome.

See the Thread Tools just above post #1.