Word: Modify Table of Contents tab settings

This summary is not available. Please click here to view the post.

Getting Rid of Mail Merge Section Breaks

This summary is not available. Please click here to view the post.

How to Clean Server Properties in MS Word 2007

This summary is not available. Please click here to view the post.

Hide everything but the working area in an Excel worksheet

Takeaway: Hiding unused cells makes it easier for users to focus on their worksheet data. 

Here’s an easy way to clear the decks and simplify the display.

You usually hide a column or row to conceal or protect data and formulas. But you can also hide unused regions of a sheet to keep users from exploiting unused areas or to help keep them on task by not allowing them to wander. By hiding unused rows and columns, you present a sheet that focuses on just the work area. To demonstrate, we’ll use the sample worksheet shown in Figure A, which has a small working area and a whole lot of unnecessary rows and columns.

Figure A

To hide unused rows in Excel 2003, select the row beneath the sheet’s last used row. (Select the row header to select the entire row.) Next, press Ctrl + Shift + Down Arrow to select every row between the selected row and the bottom of the sheet. Then, choose Row from the Format menu and select Hide. Repeat this process to hide unused columns, only select the column header in the first empty column. Press Ctrl + Shift + Right Arrow and then choose Column from the Format menu instead of Row.
In Excel 2007, the selection process is the same. However, you’ll need to click Format in the Cells group on the Home tab, choose Hide & Unhide, and select Hide Rows (Figure B) and Hide Columns.

Figure B

Note: Before you hide anything, make sure you don’t inadvertently hide an obscure area. To find the last cell in the sheet’s used range, press Ctrl + End.
Figure C shows our worksheet with the extraneous rows and columns hidden — a cleaner, less busy interface for your Excel users.

Figure C

Restoring the display

If you need to unhide the rows and columns, first select the entire sheet. Then, in Excel 2003, choose Row or Column from the Format menu and select Unhide. In Excel 2007, click Format in the Cells group on the Home tab, choose Hide & Unhide, and select Unhide Rows (or Unhide Columns).

Source

very similar procedure listed here: 

http://www.onsitetrainingcourses.com.au/main/page_blog_hiding_most_excel_rows_and_columns.html

Change the formatting of text to match the your style in Excel while doing copy-paste

 If you want to copy-paste text and want it should copy-paste automatically matching your style , then  copy the below code in "thisworkbook" of you excel


Private Sub WorkBook_Open()

  'MsgBox "this happens when workbook is opened"
    Application.OnKey "^v", "my_paste"
    '--------------call macro Paste option set to my style
End Sub
Option Explicit

Sub my_paste()
    MsgBox "you have pressed ctrl+v"
'Enter the password if your sheet is protected .
'worksheet.Protect Password:="urPassword", UserInterFaceOnly:=True, AllowFormattingColumns:=True, AllowFiltering:=True

'enter here your sheet style which is to be matched.
        Selection.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False, NoHTMLFormatting:=True, SkipBlanks:=False, Transpose:=False
   
End Sub

Coverts Display Hyperlink to Address of Link Hyperlink

If you want to convert  the hyeprlink display address to Actual address of Hyperlink  , We can do as below

Sub x()
    
     ' Select a range or column first! Enter the range you want to look in for
     Columns("A:A").Select

         Dim lngTemp As Long
    
    For lngTemp = 1 To Selection.Hyperlinks.Count
        
        Selection.Hyperlinks(lngTemp).TextToDisplay = Selection.Hyperlinks(lngTemp).Address
        
    Next
    
End Sub

Show All Comments using Excel VBA / Hide All Comments using Excel VBA

This summary is not available. Please click here to view the post.

sgds

This summary is not available. Please click here to view the post.

Excel 2007-2010 - High memory Usage

This summary is not available. Please click here to view the post.

Using Message Box (MsgBox) in Excel VBA

This summary is not available. Please click here to view the post.

Custom Message Box Buttons using Hooking in Excel VBA

This summary is not available. Please click here to view the post.

IE (Internet Explorer) Automation using Excel VBA

This summary is not available. Please click here to view the post.

Define a Position of MessageBox using VBA in Excel

Typical MsgBox pops-up in the center of the screen. You can change MsgBox's position using a Hooking API in your VBA code:

• You must create a CBT hook
• Run a Message Box with CBT hook
• Catch a HCBT_ACTIVATE message in the Hook procedure
• Set new position using the SetWindowPos function
• Release the CBT hook

Example: Hooking MessageBox using VBA in Excel:

Option Explicit
 
' Import
Private Declare Function UnhookWindowsHookEx Lib "user32" _
    (ByVal hHook As Long) As Long
 
Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
 
Private Declare Function SetWindowsHookEx Lib "user32" _
    Alias "SetWindowsHookExA" _
    (ByVal idHook As Long, _
     ByVal lpfn As Long, _
     ByVal hmod As Long, _
     ByVal dwThreadId As Long) As Long
 
Private Declare Function SetWindowPos Lib "user32" _
    (ByVal hwnd As Long, _
     ByVal hWndInsertAfter As Long, _
     ByVal x As Long, _
     ByVal y As Long, _
     ByVal cx As Long, _
     ByVal cy As Long, _
     ByVal wFlags As Long) As Long
 
' Handle to the Hook procedure
Private hHook As Long
 
' Position
Private msgbox_x As Long
Private msgbox_y As Long
 
' Hook type
Private Const WH_CBT = 5
Private Const HCBT_ACTIVATE = 5
 
' SetWindowPos Flags
Private Const SWP_NOSIZE = &H1      ' Retains the current size
Private Const SWP_NOZORDER = &H4    ' Retains the current Z order

Sub TestMsgBox()
    MsgBoxPos "Set non-Center Position", _
              vbOKOnly, _
              "Message Box Hooking", _
              400, 300
End Sub
 
Public Sub MsgBoxPos(strPromt As String, _
              vbButtons As VbMsgBoxStyle, _
              strTitle As String, _
              xPos As Long, _
              yPos As Long)
 
    ' Store position
    msgbox_x = xPos
    msgbox_y = yPos
 
    ' Set Hook
    hHook = SetWindowsHookEx(WH_CBT, _
                              AddressOf MsgBoxHookProc, _
                              0, _
                              GetCurrentThreadId)
 
    ' Run MessageBox
    MsgBox strPromt, vbButtons, strTitle
End Sub
 
Private Function MsgBoxHookProc(ByVal lMsg As Long, _
                                ByVal wParam As Long, _
                                ByVal lParam As Long) As Long
    If lMsg = HCBT_ACTIVATE Then
        ' Change position
        SetWindowPos wParam, 0, msgbox_x, msgbox_y, _
                     0, 0, SWP_NOSIZE + SWP_NOZORDER
 
        ' Release the Hook
        UnhookWindowsHookEx hHook
    End If
 
    MsgBoxHookProc = False
End Function

Using InputBox Method in Excel VBA

This summary is not available. Please click here to view the post.

Disable Alert (Warning) Messages in Excel

This summary is not available. Please click here to view the post.

How To Store Current Range Selection using VBA in Excel?

Q. How To Store Current Selection using VBA in Excel?

A. Use the foloowing VBA script:

Option Explicit
Private Sub Example()
    Dim ActSheet As Worksheet
    Dim SelRange As Range
 
    Set ActSheet = ActiveSheet
    Set SelRange = Selection
 
    '' Any code here
    'Dim NewSheet As Worksheet
    '
    'ActiveSheet.Range("A1").Select
    '
    'Set NewSheet = ThisWorkbook.Sheets().Add()
    'NewSheet.Move After:=Sheets(ThisWorkbook.Sheets().Count)

    ActSheet.Select
    SelRange.Select
End Sub

Let's discuss how it works. First, force explicit declaration of all variables:

Option Explicit

To store selection we need two variables (Worksheet and Range):

Dim ActSheet As Worksheet
Dim SelRange As Range

Then we store active Worksheet and current range selection:

Set ActSheet = ActiveSheet
Set SelRange = Selection

Now we can use any VBA code (add new sheets, select or hide cells etc) and then restore origin selection:

ActSheet.Select
SelRange.Select

Save Workbook as New File using VBA in Excel

Q. How Save Workbook as New File? A. Use the following VBA code:

Private Sub SaveWorkbookAsNewFile(NewFileName As String)
    Dim ActSheet As Worksheet
    Dim ActBook As Workbook
    Dim CurrentFile As String
    Dim NewFileType As String
    Dim NewFile As String
 
    Application.ScreenUpdating = False    ' Prevents screen refreshing.

    CurrentFile = ThisWorkbook.FullName
 
    NewFileType = "Excel Files 1997-2003 (*.xls), *.xls," & _
               "Excel Files 2007 (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    NewFile = Application.GetSaveAsFilename( _
        InitialFileName:=NewFileName, _
        fileFilter:=NewFileType)
 
    If NewFile <> "" And NewFile <> "False" Then
        ActiveWorkbook.SaveAs Filename:= NewFile, _
            FileFormat:=xlNormal, _
            Password:="", _
            WriteResPassword:="", _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False
 
        Set ActBook = ActiveWorkbook
        Workbooks.Open CurrentFile
        ActBook.Close
    End If
 
    Application.ScreenUpdating = True
End Sub

How does it work? Let's look inside.

• First, turn off screen updating:

  Application.ScreenUpdating = False

• Store the opened file full path:

  CurrentFile = ThisWorkbook.FullName

• Open window to choose new filename and folder:

• NewFile = Application.GetSaveAsFilename( _
      InitialFileName:=NewFileName, _
      fileFilter:=NewFileType)

• And now save file as new Workbook:

• ActiveWorkbook.SaveAs Filename:= NewFile, _
      FileFormat:=xlNormal, _
      Password:="", _
      WriteResPassword:="", _
      ReadOnlyRecommended:=False, _
      CreateBackup:=False

• We have to close new file and open the origin workbook:

• Set ActBook = ActiveWorkbook
  Workbooks.Open CurrentFile
  ActBook.Close

• and turn on screen updating:

  Application.ScreenUpdating = True

Using Intersection to Create a Range in Excel VBA

Intersect Method returns a Range object that represents the Intersection of Ranges.

Example selects the Intersection of 2 Ranges (A1:D5 and C3:C10). If the Intersection is blank, the example displays a message box:

Private Sub UseIntersection()
    IntersectRanges Range("A1:D5"), Range("C3:C10")
End Sub
 
Private Sub IntersectRanges(range1 As Range, range2 As Range)
    Dim intRange As Range
 
    ' Application.Intersect Method
    Set intRange = Application.Intersect(range1, range2)
 
    If intRange Is Nothing Then
        ' No Intersection
        MsgBox "Ranges Do Not Intersect!"
    Else
        ' Show new Range's address
        MsgBox (intRange.Address)
 
        ' Select new Range
        intRange.Select
    End If
End Sub

Excel VBA: SmartDel Macro

This summary is not available. Please click here to view the post.

Screen Updating/Flash using VBA in Excel

This summary is not available. Please click here to view the post.

Who Knew Excel Could Do That? Ten Top Tips

This summary is not available. Please click here to view the post.