Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro button on toolbar | Charts and Charting in Excel | |||
Button with macro in toolbar | Charts and Charting in Excel | |||
Command Button for PasteSpecial - Values - Transpose | Excel Discussion (Misc queries) | |||
Toolbar button To Run a Macro? | Excel Programming | |||
How do I make a button to PasteSpecial Values only. | Excel Programming |