Button Problems

[Ritt]

I have this VBA Macro assigned to a button but it does not work. The code does work in the immediate window or if I hit the play button but will now work by assigning it to a button. Anyone have any idea? Thanks for your help.

Sub DrawBorder()
Application.CommandBars.FindControl(ID:=6056).Execute
End Sub

 

[ChrisM]

The Commandbars object refers to the buttons on the toolbars. Is that where your button is? Normally a macro button is placed right on the worksheet itself.

How did you create your button?

 

[Ritt]

Sorry, The button is on the worksheet it self. Just used the insert shapes to make the button. Then assigned it to the macro with the VBA Code.

 

[ChrisM]

Oh, so you've already assigned the macro to the button, and all the macro does is click a command toolbar button for you? And that toolbar button is the border settings?

If this is the case, that is not the best way to do it. What if the toolbar button is current set to a border type that you don't want?

Why not just use VBA to set the border directly? This is right out of the macro recorder:

With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

that will set whatever you have highlighted to have a left border.

 

[Ritt]

Thanks but the app that I wrote is for mapping land for gas and oil. It picks the cells to change the color in according to the user commands. In most cases it is more than one cell and in a irregular shape that is why I need to draw the borders. I just though that it would be nice for the user to have two buttons on the screen that had Draw borders and Erase borders so that they did not have to go to the top of the menu each time they did this. I don't understand why when I run the command from the play button in VBA that it works but it does not when I click the assigned macro button. I do appreciate all your help.

 

[ChrisM]

I can't answer why the macro doesn't work, I guess I was assuming that based on earlier user inputs that you would know the range (irregular or not) and could just use VBA to make the border automatically with the click of a button.