How to have userform on when working with Word / Excel

How to use Excel and Word Application when userform is displayed


Many times you need to show the userform and work with the application (Excel / Word etc). By default you cannot switch to the spreadsheet or document if the userform is shown.
























Setting the form's ShowModal property to false enables user to use the Application when the userform is displayed

How to Reply to Mail using Outlook VBA

Copy formatted word document to Outlook mail using VBA


Here is a simple snippet that replies to the mail using VBA. The contents of the document has earlier been saved as HTML and the HTML is copied to the mail Item

Public Sub ReplyWithHTML()
Dim oMail As Outlook.MailItem
Dim oFSO
Dim oFS
If Application.ActiveExplorer.Selection.Count Then
If TypeOf Application.ActiveExplorer.Selection(1) Is Outlook.MailItem Then
Set oMail = Application.ActiveExplorer.Selection(1).Reply

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFS = oFSO.OpenTextFile("C:\ForBlogger\formedSample.html")

stext = oFS.readall
oMail.BodyFormat = olFormatHTML
oMail.HTMLBody = stext & vbCr & oMail.HTMLBody
oMail.Display
End If
End If
End Sub

Save Powerpoint Slides as Images using VBA

VBA Code to Convert Ppwerpoint Slide to Image (JPEG)

Here is a simple code that will Export all slides in a powerpoint presentation to Jpeg files

Sub Save_PowerPoint_Slide_as_Images()

Dim sImagePath As String
Dim sImageName As String
Dim oSlide As Slide '* Slide Object
Dim lScaleWidth As Long '* Scale Width
Dim lScaleHeight As Long '* Scale Height
On Error GoTo Err_ImageSave

sImagePath = "C:\ForBlogger\"
For Each oSlide In ActivePresentation.Slides
sImageName = oSlide.Name & ".jpg"
oSlide.Export sImagePath & sImageName, "JPG"
Next oSlide

Err_ImageSave:
If Err <> 0 Then
MsgBox Err.Description
End If
End Sub

Disable Close button using Excel VBA

How to disable close button using VBA

If you want to prevent the user to close the Application, you block the menu items, keypress etc. This snippet will help you in disabling the close button of the application.


Sub DisableExcelMenu()
' Remove Exel Menu Items
Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)
Call DeleteMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)
End Sub

The snippet uses WinAPI functions. Include the following functions to your module:



Public Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long

Public Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long


Declare Function EnableMenuItem Lib "user32" ( _
ByVal hMenu As Long, _
ByVal uIDEnableItem As Long, _
ByVal uEnable As Long) As Long
'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow _
Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Public Const MF_BYCOMMAND = &H0&
Public Const MF_BYPOSITION = &H400&
Public Const SC_ARRANGE = &HF110
Public Const SC_CLOSE = &HF060
Public Const SC_HOTKEY = &HF150
Public Const SC_HSCROLL = &HF080
Public Const SC_KEYMENU = &HF100
Public Const SC_MAXIMIZE = &HF030
Public Const SC_MINIMIZE = &HF020
Public Const SC_MOVE = &HF010
Public Const SC_NEXTWINDOW = &HF040
Public Const SC_PREVWINDOW = &HF050
Public Const SC_RESTORE = &HF120
Public Const SC_SIZE = &HF000
Public Const SC_VSCROLL = &HF070
Public Const SC_TASKLIST = &HF130
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Public Const HWND_TOP = 0
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const GWL_STYLE = (-16)


To enable the close button use

Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)
Call EnableMenuItem(hMenu, SC_CLOSE, MF_BYCOMMAND)


Disabled Close Button Excel

Kill Residual Excel Process using VBA

Here is a simple VBA code to "Kill" the Excel process using VBA


Sub Kill_Excel()

Dim sKillExcel As String

sKillExcel = "TASKKILL /F /IM Excel.exe"
Shell sKillExcel, vbHide

End Sub

How to Kill the Word Process using VBA

Developers who are working on Word VBA would have experienced the Word Crash problem more often.

Here is a crude way to kill the Word process.
Sub Kill_Word()

Dim sKillWord As String

sKillWord = "TASKKILL /F /IM Winword.exe"

Shell sKillWord, vbHide

End Sub


Process Window

Use this option if you are sure that the process is unused one

Reset Auto Filter and unhide Rows and Columns using Excel VBA

Autofilters and hidden cells are a nemesis when you perform some operations. The following simple macro will unhide all rows/columns and turn-off the autofilter

Sub RemoveFiltersAndHiddenRows()

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets

oWS.AutoFilterMode = False
oWS.UsedRange.Rows.Hidden = False
oWS.UsedRange.Columns.Hidden = False Next

End Sub

How to Save a Chart as Image using Excel VBA

Here is the way to save the active chart in Excel 2007 to a JPG file. It is better to size the chart appropriately before exporting it as an image.

Sub Save_ChartAsImage()

Dim oCht As Chart

Set oCht = ActiveChart

On erRROR GoTo Err_Chart

oCht.Export Filename:="C:\PopularICON.jpg", Filtername:="JPG"

Err_Chart:

If Err <> 0 Then

Debug.Print Err.Description

Err.Clear

End If

End Sub

The code uses Export method to save the chart in graphics format

How to Install Analysis ToolPak in Excel 2007

How to Install Analysis ToolPak in Excel 2007

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.

In the Manage list, select Excel Add-ins, and then click Go.

In the Add-ins available list, select the Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the Setup program.

The analysis pack will be loaded and displayed on the menu