Adding/Removing Button in a toolbar and assigning a macro

polska2180

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

steve1262

Board Regular
Joined
Aug 19, 2006
Messages
126
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.


Code:
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

Active Member
Joined
Oct 1, 2004
Messages
357
Office Version
  1. 365
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
 

Forum statistics

Threads
1,136,370
Messages
5,675,365
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top