How to disable user from using short-cut keys using VBA

You can disable the keybinding using the following code

Sub VBA_DisableKey()

'This code will disables Ctrl + O FileOpen command.
' Disable Ctrl + O

FindKey(BuildKeyCode(wdKeyControl, wdKeyO)).Disable

' Rebind Ctrl + O to FileOpen

FindKey(BuildKeyCode(wdKeyControl, wdKeyO)).Rebind wdKeyCategoryCommand, "FileOpen"

End Sub

Use the rebind method to restore the bind.

How to get the free space available using VBA

Sub FreeDiskSpaceCurrent_Drive()

Dim sFreeSpace As String

sFreeSpace = System.FreeDiskSpace

sFreeSpace = Format(sFreeSpace, "0,000")

MsgBox "Free Space Available is : " & sFreeSpace

End Sub

Convert Decimal to Percentage using VBA Format Function

Here is a simple example to convert a decimal to percentage using VBA function

Sub Con_Decimal2Percentage()


Dim dblSuccess As Double
Dim sSuccess As String

dblSuccess = 0.54

sSuccess = FormatPercent(dblSuccess, 2)

sSuccess = Format(Expression:=dblSuccess, Format:="Percent")


End Sub

How to Show File Print Setup Dialog Box using Word VBA

The following code displays the Print Setup Dialog

Sub Show_PntSetup()

With Dialogs(wdDialogFilePrintSetup)
.Show
End With

End Sub.

Convert Decimal to Percentage using VBA FormatPercent Function

Sub Convert_Decimal2Percentage()

Dim dblSuccess As Double
Dim sSuccess As String

dblSuccess = 0.456345

sSuccess = FormatPercent(dblSuccess, 2)

sSuccess = Format(Expression:=dblSuccess, Format:="Percent")

End Sub

VBA check program modes.

How to check if the program is in debug mode using VBA. Below code is used to check :-

Sub Check_VBA_VBE()

Dim oVBe As VBProject

Set oVBe = ThisWorkbook.VBProject

If oVBe.Mode = vbext_vm_Run Then

MsgBox "Executing.."

MsgBox oVBe.VBE.MainWindow.Visible

End If

End Sub

The code uses VBProject. Ensure that the Microsoft Visual Basic for Applications reference is loaded.

How to Add Macro to Workbook using Excel VBA

Here is a simple procedure to add a macro to the current workbook using VBComponents

Sub Add_Macro_To_ThisWorkbook()

Dim VBP As VBProject
Dim VBM As VBComponent
Dim VBModule As CodeModule
Dim VBProc As VBComponent

Set VBP = ThisWorkbook.VBProject

Set VBModule = VBP.VBComponents.Item("ThisWorkbook").CodeModule

VBModule.AddFromString ("Sub Sample_Macro" & vbCrLf & "ret = msgbox (""Hello VBADUD"") " & vbCrLf & "End Sub")

End Sub

ADO Connection

If you are try to connect to ADO. Here is the connection string for Excel

sXL = "c:\CtArtExample.xls"

Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & sXL & ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40

Cn.Open

The rest is the usual ADO recordset retrieving technique.