Unprotect and Protect Sheet using VBA code

Here is a sample to unprotect a sheet and write some values and then protect the sheet again

Sub Unprotect_And_ThenProtect()

ActiveSheet.Unprotect
Range("A2").Value = Now()
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

AutomaticHyphenation feature

Word does automatic hyphenation at the end of line when the AutomaticHyphenation feature is turned on


ActiveDocument.AutoHyphenation = True

You can test it by try selecting the Hyphen (which is not there physically)

The following code converts all automatic hyphens to manual ones


ActiveDocument.ConvertAutoHyphens

How to Specify the Password in SaveAs option in PowerPoint VBA

Unlike SaveAs in Word/Excel, which takes the Password as part of the argument, Powerpoint SaveAs function doesn't specify it.

Here is a way to do it through VBA


Sub Save_Presentation_With_Password()

Dim oPS As PowerPoint.Presentation
Dim sTempPath As String

Set oPS = Presentations.Add
oPS.Slides.Add 1, ppLayoutTitle

' ----------------------------
' Coded by chithu for VBACT.Blogspot.com
' ----------------------------

sTempPath = Environ("Temp") & "\"

oPS.Password = "PWD2PPT"


oPS.SaveAs FileName:=sTempPath & "PPTSample1.ppt", FileFormat:=ppSaveAsDefault
oPS.Close


End Sub

Hide / Unhide Sheet Tab using Excel VBA

If you want to hide the Sheet Tab you can do that using Excel Options

1) Uncheck the Show sheet tabs checkbox from Advanced Tab of Options Menu.
2) Go to the advanced tab and you can enable/disable through the show sheet tabs options.


You can do the same through Excel VBA

ActiveWindow.DisplayWorkbookTabs = False

HOW TO EXTRACT ALL FORMULA'S IN EXCEL SHEET USING VBA

Highlight all cells containing Formulas using Excel VBA

The following snippet highlights all cells that contain formula


Sub HighLight_Formula_Cells()

Dim oWS As Worksheet
Dim oCell As Range

Set oWS = ActiveSheet

For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
oCell.Interior.ColorIndex = 36
MsgBox oCell.Formula
Next oCell


End Sub