Monday, June 27, 2005

Code to copy selected Excel Cells to Powerpoint Presentation via a Macro / Button

Sub RangeToPresentation()
'Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, "No Range Selected"

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")

'Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

' Reference active slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy the range as a picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture

' Paste the range

' Align the pasted range
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub

Code to hide and unhide worksheets in Excel

To Hide a worksheet: Goto VB Editor Window and type:

thisworkbook.Worksheets("name of workbook").visible=xlsheetveryhidden

To Unhide an Excel Worksheet: Goto VB Editor Window and type:

thisworkbook.Worksheets("name of workbook").visible=xlsheetvisible

Locking Scroll Bars in Excel

Enter the following code in the “Workbook� area of the VB Editor to lock down the scroll bars

Private Sub Workbook_Open()
ThisWorkbook.Worksheets(name of workbook).ScrollArea = "$A$1:$b$10"End Sub

Automatic Highlighting of Active Row

This code must be placed in the Private Module of the Worksheet. To get there right click on the sheet name tab and select "View Code". Here is a handy little bit of code that will highlight the current row as you select it. But only if the row is NOT empty.

Private Sub Worksheet_Selection
Change(ByVal Target As Range)
Dim strRow As String
With Target.EntireRow
strRow = .Address
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=COUNTA(" & strRow & ")>0"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Prevent a user saving a Workbook as another name. That is, stop the Save as dialog box from showing.

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub

Excel code to run your code when the Workbook opens, Activates, Deactivates, Closes and Saves

The code below shows you how you can use Excels Workbook Events to run your code when the Workbook opens, Activates, Deactivates, Closes and Saves. The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". Then choose an event from the "Procedure" drop down list box.

For Excel 2000 you will need to select "Workbook" from the "Object" drop down list box first. All examples must be placed within the Private Module of the Workbook Object "ThisWorkbook" as described above. Unless stated otherwise! Hide all of Excels standard Menus and Toolbars and show only your Custom Toolbar.This code will decide if the user has closed your Workbook or simply Activated another. This code (unless changed) assumes you have a Custom Toolbar called "MyToolBar" which is attached to the Workbook.

Whenever the user closes or deactivates the Workbook, all Toolbars and Menubars will be restored as before. To attach your a Custom Toolbar go to View>Toolbars>Customize-Attach then Copy your Custom Toolbar to the Workbook.

'Module level declaration
Dim IsClosed As Boolean, IsOpen As Boolean
Private Sub Workbook_Activate()
'Show the Custom toolbar
IsClosed = False
If IsOpen = False Then
Application.ScreenUpdating = False
Run "HideMenus"
Application.ScreenUpdating = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
IsClosed = True 'Closing so set to True
If Cancel = True Then IsClosed = False
'Changed their mindEnd Sub
Private Sub Workbook_Deactivate()Application.ScreenUpdating = False
IsOpen = False
On Error Resume Next 'In case it's already gone.
If IsClosed = True Then 'Workbook is closing.
With Application.CommandBars("MyToolBar")
.Protection = msoBarNoProtection
End With
Run "ShowMenus"
Else 'They have only activated another Workbook
Run "ShowMenus"
End IfApplication.ScreenUpdating = True
End Sub