Having macro add PasteSpecial Values button to toolbar
 

My application requires the users to use PasteSpecial Values to paste in
data. Due to there being eight Citrix servers, and a lot of users, I'd like
my workbook to add this button if it isn't already on the toolbar. This
line of code:

Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=370, Befo=11

puts it before the 11th button. But I'd like to put it right after the copy
button. Plus I don't want to add one if it already exists someplace on the
Standard toolbar. And if the Standard toolbar doesn't exist then don't do
anything.

I gather I have to loop through the toolbar items to see whether
PasteSpecial Values is already present, and to find the position of the
Copy button. But this is where I got lost.

Don <www.donwiss.com (e-mail link at home page bottom).

Having macro add PasteSpecial Values button to toolbar
 

Try this:

Sub add_PasteValues_button()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim Count As Integer

If Application.CommandBars("Standard").Visible = False Then
Exit Sub
End If

'count the number of buttons until error
'could be a better way?
On Error GoTo btnCount '(Run time error 9 - Subscript out of Range)
For i = 1 To 1000
If Application.CommandBars("Standard").Controls(i).ID = 0 Then
End If
Next i

btnCount:
Count = i - 1
Resume afterCount

afterCount:
'check for existing Paste Values button
For j = 1 To Count
If Application.CommandBars("Standard").Controls(j).ID = 370 Then
Exit Sub
End If
Next j

'check for Copy button
For k = 1 To Count
If Application.CommandBars("Standard").Controls(k).ID = 19 Then

Application.CommandBars("Standard").Controls.Add _
Type:=msoControlButton, ID:=370, befo=k + 1

Exit Sub

End If

Next k

'no Copy Button on the toolbar
'Place Paste Values somewhere
Application.CommandBars("Standard").Controls.Add _
Type:=msoControlButton, ID:=370, befo=9

End Sub

"Don Wiss" wrote:

My application requires the users to use PasteSpecial Values to paste in
data. Due to there being eight Citrix servers, and a lot of users, I'd like
my workbook to add this button if it isn't already on the toolbar. This
line of code:

Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=370, Befo=11

puts it before the 11th button. But I'd like to put it right after the copy
button. Plus I don't want to add one if it already exists someplace on the
Standard toolbar. And if the Standard toolbar doesn't exist then don't do
anything.

I gather I have to loop through the toolbar items to see whether
PasteSpecial Values is already present, and to find the position of the
Copy button. But this is where I got lost.

Don <www.donwiss.com (e-mail link at home page bottom).

Having macro add PasteSpecial Values button to toolbar
 

Thanks! I had figured out the Count property, but what was holding me back
was not knowing about the ID property. This is what I simplified it to:

Sub AddPasteValuesButton()
' adds PasteSpecial Values button to Standard toolbar

Dim i As Integer

' if no Standard toolbar we do nothing
If Not Application.CommandBars("Standard").Visible Then Exit Sub

' check for existing PasteSpecial Values button
For i = 1 To Application.CommandBars("Standard").Controls.Count
If Application.CommandBars("Standard").Controls(i).ID = 370 Then Exit Sub
Next i

' check for Copy button (so we can paste to its right)
For i = 1 To Application.CommandBars("Standard").Controls.Count
If Application.CommandBars("Standard").Controls(i).ID = 19 Then
Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=370, Befo=i + 1
Exit Sub
End If
Next i

' no Copy Button on the toolbar, so place PasteSpecial Values somewhere
Application.CommandBars("Standard").Controls.Add Type:=msoControlButton, ID:=370, Befo=10

End Sub