Locking a Command Button to a cell:VBA

[bamaisgreat]

I am currently using the code below to create my command button.The problem im having is that columns are adjusted sometimes so the button gets in the way.

ActiveSheet.Buttons.Add(1200.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846).Select

Is there away in the code to lock it in a cell????

 

[VoG]

Maybe like this

Dim bt As Button
Set bt = ActiveSheet.Buttons.Add(1200.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
bt.Placement = xlMove

 

[bamaisgreat]

Peter, can I insert just this part

ActiveSheet.Buttons.Add(1200.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
bt.Placement = xlMove

<!-- / message --><!-- sig -->

 

[VoG]

No, you need what I posted.

 

[bamaisgreat]

Peter, I tried it and it errored out,

Sub FINALIZED_BY_QC()

Dim newFileName As String
Dim appendtext As String
With ActiveSheet
.Unprotect Password:="1288"
With .Range("L23").Interior
.Pattern = xlSolid
.PatternColorIndex = 1
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
appendtext = "-FINAL"
.Range("L23").FormulaR1C1 = appendtext



Cells.Select
Selection.Locked = True
Range("V4:V21").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V3").Select
ActiveCell.FormulaR1C1 = "Assistant Purchasing/" & Chr(10) & "Dept. Comments"
Range("V3").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
Range("V3:V21").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("V3").Select
ActiveWorkbook.Save
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

Dim bt As Button
Set bt = ActiveSheet.Buttons.Add(1250.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
bt.Placement = xlMove
'ActiveSheet.Buttons.Add(1250.15384615385, 26.5846153846154, 179.446153846154,
' _23.8153846153846).Select
Selection.OnAction = "PURCH_COMMENTS"
Selection.Characters.Text = "SAVE COMMENTS"
With Selection.Characters(Start:=1, Length:=13).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With






'.UsedRange.Locked = True
.Protect Password:="1288", DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
With ActiveWorkbook
oldFileName = .FullName
newFileName = Left(.FullName, InStrRev(.FullName, ".xls") - 1) _
& appendtext
.SaveAs Filename:=newFileName
End With
Kill oldFileName

End Sub

 

[VoG]

What error did you get? This worked for me

Sub test()
Dim bt As Button
Set bt = ActiveSheet.Buttons.Add(1200.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
bt.Placement = xlMove
End Sub

 

[bamaisgreat]

Peter, It made it down on the code above untill it got to the line under were it built the command button


Actually its the line that says

Selection.OnAction = "PURCH_COMMENTS"

Set bt = ActiveSheet.Buttons.Add(1220.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
bt.Placement = xlMove

 

[VoG]

Try

Dim bt As Button
Set bt = ActiveSheet.Buttons.Add(1250.15384615385, 26.5846153846154, 179.446153846154, 23.8153846153846)
With bt
    .Placement = xlMove
    .OnAction = "PURCH_COMMENTS"
    .Characters.Text = "SAVE COMMENTS"
    With .Characters(Start:=1, Length:=13).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
    End With
End With

 

[bamaisgreat]

Thanks Peter as always. You help me out alot..