Excel - Command Button Alignment

Question

Excel - Command Button Alignment

Anonymous

Hi,

I have been trying to two days to make this worksheet of command (active x) buttons align and look nice. Ideally I would like them to be perfectly aligned and have equal amount of space between each button. Thanks for any help:

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author

5 additional answers

Answer 1

Let's say that you have a worksheet named Clients, with client names in A2:A100 and the corresponding email addresses in B2:B100.

On the sheet with the single command button, place an ActiveX list box with ListFillRange set to Clients!A2:A100 and LinkedCell set to A1.

In B1 on that sheet, enter the formula =IFERROR(VLOOKUP(A1,Clients!A2:B100,2,FALSE),"")

The code behind the single command button can be as shown above, but change the .To = "...." line to

.To = Range("B1").Value

This will set the to address to the email address corresponding to the selected client.

Answer 2

On the Developer tab of the ribbon, click to turn on Design Mode.

Use click and Shift+click to select all buttons in a row.

Activate the Format tab of the ribbon (under Drawing Tools).

Select Align > Align Top.

Do the same for the other rows of buttons.

Next, select a column of buttons.

Select Align > Left.

Do the same for the other columns of buttons.

Now select a row of buttons again, and select Align > Distribute Horizontally.

Repeat.

Select a column of buttons and select Align > Distribute Vertically.

Repeat.

Answer 3

Thanks Hans,

I wondered if you had any other ideas on this process. We have a client list of 190 that I am in the process of creating a button for to generate an email in excel. As you can imagine it is taking a very long time. Do you have any faster suggestions? I'm not sure if its possible to create some type of drop down list to chose a client then click 1 button to send an email out to that person?

Thanks again,

Josh

Answer 4

Another option is to hold down Alt while dragging the buttons - they will snap to the cell grid.

But you could also use a list box (or combo box) with the client list as ListFillRange, and another cell as LinkedCell. The LinkedCell will contain the name of the selected client. You can use this in the code behind the single command button.

Answer 5

That sounds better than what I am currently doing but I have never dealt with a list box or combo box in the past. I am currently just using the below macro and creating over a hundred buttons and changing the email address:

Private Sub CommandButton7_Click()

On Error GoTo ErrHandler

' SET Outlook APPLICATION OBJECT. 

Dim objOutlook As Object 

Set objOutlook = CreateObject("Outlook.Application") 

' CREATE EMAIL OBJECT. 

Dim objEmail As Object 

Set objEmail = objOutlook.CreateItem(olMailItem) 

With objEmail 

    .To = "\*\*\*\*\*\*@gmail.com" 

    .Subject = "Direct ledger allocations thru " & Format(Date - Day(Date), "mmmm yyyy") & " " & "attached" 

    .HTMLBody = "&lt;BODY style=font-size:12pt;font-family:Calibri&gt;" & "&lt;b&gt;Good Afternoon -" & "&lt;br&gt;" & "&lt;br&gt;" & "Attached you will find your direct ledger allocations thru " & Format(Date - Day(Date), "mmmm yyyy") & "." & "&lt;br&gt;" & "&lt;br&gt;" & "Please let me know if you have any questions or require additional research." 

    .Display        ' Display the message in Outlook. 

End With 

' CLEAR. 

Set objEmail = Nothing:    Set objOutlook = Nothing

ErrHandler:

End Sub

Share via

Excel - Command Button Alignment

5 additional answers