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