Visual Basic Script to Lock Computer

Lock Machine (Computer) using VB Script

My good friend Venugopala Reddy Ragi had a good VBScript snippet to lock computer. Probably useful for someone to enhance security

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "rundll32 user32.dll,LockWorkStation"

Play Audio File using Excel VBA

Play Audio File using Excel VBA / Excel VBA Play Wav File

Here is a small snippet used by Pradeep Meesala to play a wav file. He has used this for an elegant splash screen, which was a nice combination of music and display

The code uses PlaySound API Function

Private Declare Function PlaySound Lib "winmm.dll" _

Alias "PlaySoundA" (ByVal lpszName As String, _

ByVal hModule As Long, ByVal dwFlags As Long) As Long

Sub Play_Wav_File()

Dim sWAVFile As String

On Error GoTo Err_Play

sWAVFile = "C:\Temp\Windows XP Logon Sound.wav"

PlaySound(sWAVFile, &O0, 0)

Err_Play:

If Err <> 0 Then

Err.Clear()

End If

End Sub

Permanently hide worksheet from user using VBA

Hide / Unhide Worksheets using Excel VBA (Excel 2007)

Here is the simple code to Hide and unhide Worksheet:

Sub Hide_Unhide_Worksheets()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = Worksheets(1)

' hide the sheet - user cannot unhide

oWS.Visible = xlSheetVeryHidden

' To make the sheet visible

oWS.Visible = xlSheetVisible

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub

Here are the values for XLSheetVisibility. Hides the Worksheet so that the only way for you to make it visible again is by setting this property to True (the user cannot make the Worksheet visible).

Name		Description
xlSheetHidden		Hides the worksheet which the user can unhide via menu.
xlSheetVeryHidden		Hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible).
xlSheetVisible		Displays the sheet.

Get Name of the WeekDay (Excel VBA)

Get Name of the WeekDay (Excel VBA)

Sub WeekDay_Name()

Debug.Print(WeekdayName(1))

Debug.Print(WeekdayName(1, True))

Debug.Print(WeekdayName(1, False, vbSunday))

Debug.Print(WeekdayName(1, True, vbSunday))

End Sub

The above function would give result as :

'Monday

'Mon

'Sunday

'Sun

Retrieve / Get First Row of Excel AutoFilter using VBA

Extract First Row of the Filtered Range using Excel VBA

We can create filters programmatically using Excel VBA (AutoFilter using Excel VBA) and also add multiple criteria to it (Create AutoFilter with Multiple Criteria using Excel VBA). Once we get the filtered data, either we extract the same or iterate each row in it and do some operations. Here is one such simple program to extract the rows of filtered range using VBA

Sub Get_Filtered_Range()

Dim oWS As Worksheet

Dim oRng As Range

Dim oColRng As Range

Dim oInRng As Range

On Error GoTo Err_Filter

oWS = ActiveSheet

oWS.UsedRange.AutoFilter(Field:=2, Criteria1:="Banana")

oRng = oWS.Cells.SpecialCells(xlCellTypeVisible)

oColRng = oWS.Range("A2:A5000")

oInRng = Intersect(oRng, oColRng)

MsgBox("Filtered Range is " & oInRng.Address)

MsgBox("First Row Filtered Range is " & oInRng.Rows(1).Row)

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub

See also:

Create AutoFilter with Multiple Criteria using Excel VBA

AutoFilter using Excel VBA

Check for existence of Filter using Excel VBA

Excel Filter Show All using VBA

Retrieve / Get First Row of Excel AutoFilter using VBA

Create AutoFilter with Multiple Criteria using Excel VBA

Excel VBA Applying Multiple Criteria in AutoFilter

Here is a simple way of using multiple criteria in the Excel AutoFilter option

Sub AutoFilter_WithMultiple_Criteria()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = ActiveSheet

oWS.UsedRange.AutoFilter(Field:=2, Criteria1:="Apple", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="Orange")

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear()

GoTo Finally

End If

End Sub

See also:

Create AutoFilter with Multiple Criteria using Excel VBA

AutoFilter using Excel VBA

Check for existence of Filter using Excel VBA

Excel Filter Show All using VBA

Retrieve / Get First Row of Excel AutoFilter using VBA

AutoFilter using Excel VBA

AutoFilter using Excel VBA

Filters are one of the most used Excel utilities. Here is a simple way to create a filter through code

Sub Simple_AutoFilter()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = ActiveSheet

oWS.UsedRange.AutoFilter Field:=2, Criteria1:="Apple"

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear

GoTo Finally

End If

End Sub

Range Before Filtering
Range after Filter




See also:

Create AutoFilter with Multiple Criteria using Excel VBA

AutoFilter using Excel VBA

Check for existence of Filter using Excel VBA

Excel Filter Show All using VBA

Retrieve / Get First Row of Excel AutoFilter using VBA

VBA Send File to Recycle Bin

Delete Files using VBA

There are many methods to delete the file:- simple VBA Kill method, using FileSystemObject etc. Here is one snippet my friend Devakottai PaneerSelvam used for deleting files (if I can say it, temporarily). The advantage here is that the deleted file is available in Recycle Bin for the user to restore if needed.

Private Type SHFILEOPSTRUCT

hwnd As Long

wFunc As Long

sFrom As String

sTo As String

fFlags As Integer

fAnyOperationsAborted As Boolean

hNameMappings As Long

lpszProgressTitle As String

End Type

Private Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _

(ByRef lpFileOp As SHFILEOPSTRUCT) As Long

Private Const FO_DELETE = &H3

Private Const FOF_SILENT = &H4

Private Const FOF_NOCONFIRMATION = &H10

Private Const FOF_ALLOWUNDO = &H40

Sub DeleteFileUsingAPI()

Dim oFilAPI As SHFILEOPSTRUCT

Dim lReturn As Long

Dim sFile As String ' File that needs to be deleted

sFile = "C:\ Temp\VBADUD\Hints and Tips.htm"

With oFilAPI

.wFunc = FO_DELETE

.sFrom = sFile

.sTo = vbNullChar

.fFlags = FOF_SILENT + FOF_NOCONFIRMATION + FOF_ALLOWUNDO

End With

' Use WinAPI User Defined Function

lReturn = SHFileOperation(oFilAPI)

Err_Delete:

MsgBox(Err.Number & " - " & Err.Description)

Err.Clear()

Resume Next

End Sub

Delete Files using VBA, Temporary Deletion using VBA, VBA Code to send files to Recycle Bin

See also:

Move and Overwrite Files in C# (.NET)

Delete Files using C# / Delete Files using .NET

Clean Temp (.tmp) and Word Backup files (.wbk) using Word VBA

Primitive File Handling Functions

Identify Objects that are linked to Source file using Excel VBA

Extract all Linked Objects in Excel Spreadsheet using VBA

At times when you send out some document, you should be careful of the external links that the document contains. The following code might help you:

Sub Extract_Linked_Objects()

Dim oWS As Worksheet ' Worksheet Object

Dim oOLE As OLEObject ' OLE Object

On Error GoTo Err_OLE

Set oWS = ActiveSheet

If oWS.OLEObjects.Count = 0 Then Exit Sub

For Each oOLE In oWS.OLEObjects

If oOLE.OLEType = xlOLELink Then

MsgBox oOLE.SourceName

End If

Next

Finally:

If Not oOLE Is Nothing Then Set oOLE = Nothing

If Not oWS Is Nothing Then Set oWS = Nothing

Err_OLE:

If Err <> 0 Then

Err.Clear

GoTo Finally

End If

End Sub

Embed Existing Word File to Spreadsheet using Excel VBA

Insert Existing File (Word Document) to Spreadsheet using VBA

Sub Insert_File_To_sheet()

Dim oWS As Worksheet ' Worksheet Object

Dim oOLEWd As OLEObject ' OLE Word Object

Dim oWD As Document ' Word Document Object (Use Microsoft Word Reference)

Set oWS = ActiveSheet

' embed Word Document

Set oOLEWd = oWS.OLEObjects.Add(Filename:="C:\VBADUD\Chapter 1.doc")

oOLEWd.Name = "EmbeddedWordDoc"

oOLEWd.Width = 400

oOLEWd.Height = 400

oOLEWd.Top = 30

' Assign the OLE Object to Word Object

Set oWD = oOLEWd.Object

oWD.Paragraphs.Add

oWD.Paragraphs(oWD.Paragraphs.Count).Range.InsertAfter "This is a sample embedded word document"

oOLEWd.Activate

End Sub

If you want to embed other document like PDF etc, you can do the same by

ActiveSheet.OLEObjects.Add Filename:= "C:\VBADUD\Sample_CH03.pdf", Link:=False, DisplayAsIcon:= False

Display embedded document as Icon

If you want to display the embedded document as an Icon set DisplayAsIcon property to True

See also:

Manipulate ActiveX TextBoxes in a Word Document using VBA

Embed Word Document to Excel Sheet using Excel VBA

Linking Text Box to Excel Range using VBA

Creating a Command Button on Sheet using Excel VBA

Get the Height & Width of Shapes / Figures in Word Document (Word VBA)

Embed Word Document to Excel Sheet using Excel VBA

Programming OLEOBjects in VBA to embed Word Document

Sub Embed_WordDocument_To_sheet()

Dim oWS As Worksheet ' Worksheet Object

Dim oOLEWd As OLEObject ' OLE Word Object

Dim oWD As Document ' Word Document Object (Use Microsoft Word Reference)

Set oWS = ActiveSheet

' embed Word Document

Set oOLEWd = oWS.OLEObjects.Add("Word.Document")

oOLEWd.Name = "EmbeddedWordDoc"

oOLEWd.Width = 400

oOLEWd.Height = 400

oOLEWd.Top = 30

' Assign the OLE Object to Word Object

Set oWD = oOLEWd.Object

oWD.Paragraphs.Add

oWD.Paragraphs(oWD.Paragraphs.Count).Range.InsertAfter "This is a sample embedded word document"

oOLEWd.Activate

End Sub

Add Words to AutoCorrect Entries

AutoCorrect feature is used to correct typos and misspelled words (dont to don’t), as well as to insert symbols ((c) as ©) and other pieces of text. AutoCorrect is set up by default with a list of typical misspellings and symbols. If you want to add more entries, you can do that by following

Office Button - -> Word Options - -> Proofing Tab - - >Autocorrect Options

Add the mistyped word and the replacement in Replace Box -- >Add

Barak Obama is the democratic presidential candidate will become Barack Obama is the democratic presidential candidate once you type.

The same can be achieved through Word VBA / Excel VBA

Excel VBA Code:

Sub Add_AutoCorrect_Entry_XL()

On Error GoTo Err_Label

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

Application.AutoCorrect.AddReplacement "VBADUD Plc", "VBADUD Inc"

Application.AutoCorrect.AddReplacement "Barak Obama", "Barack Obama"

Err_Label:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear

End If

End Sub

Word VBA Code:

Sub Add_AutoCorrect_Entry()

On Error GoTo Err_Label

' ---------------------------------------------

' Coded by Shasur for www.vbadud.blogspot.com

' ---------------------------------------------

Application.AutoCorrect.Entries.Add "VBADUD Plc", "VBADUD Inc"

Application.AutoCorrect.Entries.Add "Barak Obama", "Barack Obama"

Err_Label:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear

End If

End Sub