need a macro or code to cause an action in the workbook

What I'm looking to do is create a button. When you click this button you activate (Border option) Standard line/automatic color/

My goal is to have 3 buttons:

1:  standard line/ automatic color

2:  dotted line/ red color

3; erase border

I want to be able to click any button and be ready to do this option anywhere on the sheet.

Thanks for any help.

Rollins

Hi Rollins, this code does the applied changes on the current selection. As you can see you can use the borders collection or the borderaround method, either does the trick

Private Sub CmdClearBorder_Click()
    With Selection
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
End Sub

Private Sub CmdRedBorder_Click()
    With Selection
        .BorderAround LineStyle:=xlDash, Weight:=1, ColorIndex:=3
    End With
End Sub

Private Sub CmdStandardBorder_Click()
    With Selection
        .BorderAround LineStyle:=xlContinuous, Weight:=2, ColorIndex:=1
    End With
End Sub

Adding to Warren's code (Hi Warren), this is a situation when the macro recorder is more than adequate as it operates generally on the selection you have made, so, this recorded this morning.

Sub PlainBorder()
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
    End With
End Sub

Sub RedDottedBorder()
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlDot
        .Color = -16776961
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlDot
        .Color = -16776961
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDot
        .Color = -16776961
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDot
        .Color = -16776961
    End With
End Sub

Sub NoBorder()
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
End Sub

Hey guys thanks alot and I will find a use for this; however, what I'm looking to do is activate the action to allow me to draw these line.

I'll try to explain this in code form :)  follow my actions in excel

Sub

 Click the dropdown for borders

 Click Draw border

' needs to be solid black line

End Sub

So no border is drawn I am ready to draw and I can draw anywhere for as long as I want till I stop the function or change to the Red.

Thanks again guys for the fast response

Rollins

I'm not sure you can do what you want from VBA. If I understand you, by pressing the button on the sheet you want to be able to enter 'draw borders' mode and then press the another button and you are in draw borders mode, but in red colour?

I think this is something like entering edit mode that cannot be done in excel, except perhaps through a 'SendKeys' operation.

If you are in versions before 2007 however, you could, of course, create a command bar and have that undocked near your work, if this is what you seek?

Anyone else find a method that allows Excel to be placed in that mode? A sort of CutCopyMode equivalent? DrawBordersMode?

I am in version 2007.  For what I'm doing, I need to be able to draw the boarder anywhere on the sheet. And be able to change to the different color and style. right know as you know I have to manualy change that which is very time consuming. 

If you think of any ideas or avenues I could use please let me know.

Thanks

I spoke with a friend today whom I found out has done alot with excel. He says scripting. It sounds like I'll have to do some scripting to make this work the way I want it to.

I'd like to revisit this Thread. Let me recap. I have designed A new Ribbon in my workbook. This Ribbon will have various buttons. For starters I have 3.

Button                 Action                                                                                                                            effect

Button     -   on click will act as if you clicked "DrawBoarder" then"BoarderStyle" then "BoarderColor"       -     Sheet will be in Draw Boarder mode

Button 1 - thin solid black line

Button 2 - thin dotted red line

Button 3 - thin dotted black line

I will be greatful for any help

Thank you

Rollins