Adding/Removing Button in a toolbar and assigning a macro

[polska2180]

Hello,

I'm looking to add toolbar and then a button inside that toolbar that will be linked to a script using vba. Baiscally, on excel start a toolbar would be placed at the end of the existing ones at the top that would have a button names Run and would be linked to a macro names play. On close I want that to be deleted.

Also,

I need script that will only print cells that are populated. The number of columns will not change just the rows.


Thanks.

 

[steve1262]

Print Populated Cells

Here is your code for printing only the populated cells. As far as the tool bar I have do not know how to do that.

Dim LastRow As Long, ws As Worksheet
     ' add your worksheet name here
    Set ws = Sheets("Sheet1")
     
    Application.ScreenUpdating = False
    With ws
        .Visible = xlSheetVisible
        LastRow = .Range("A" & Rows.Count).End(xlUp).Offset(2).Row
'Set your column range here
        .PageSetup.PrintArea = "A1:S" & LastRow
    End With
     
    With ws.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
     
     
    With ws.PageSetup
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .Draft = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
     
    With Application
        .Dialogs(xlDialogPrinterSetup).Show
        ws.PrintOut
        ' ws.Visible = xlSheetVeryHidden
        .ScreenUpdating = True
    End With
End Sub

 

[polska2180]

thanks, that get me half way there.

 

[polska2180]

not what I originally wanted but this works so here it is

Option Explicit
Dim cControl As CommandBarButton

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("VeoliaES Profitability").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("VeoliaES").Delete
Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
With cControl
.Caption = "VeoliaES Profitability"
.Style = msoButtonCaption
.OnAction = "Play"
End With
Range("A1").Select
ActiveSheet.Paste
On Error GoTo 0
End Sub