Tag: macro

MS Word: Paste as Text

To set up a shortcut key to paste as text, do the following:
  1. From the View tab, create a new macro named PasteSpecialAsText.
  2. Replace the contents of the code editor with the following text:

    Sub PasteSpecialAsText()
         Selection.PasteSpecial DataType:=wdPasteText
    End Sub

  3. From the File menu, choose Word Options, then Customize Ribbon, and then click the Customize button next to Keyboard shortcuts at the bottom to bring up the Customize Keyboard dialog.
  4. Choose Macros from the categories list on the left, and then your macro in the Commands box on the right-hand side. Now you can enter in a shortcut key (Ctrl+Shift+V or your own), and click the Assign button.
This procedure was taken from LifeHacker and updated for Word 2010.

[Source LifeHacker]

Excel: Removing Blank Rows

Excel has a powerful filtering capability that lets you view, among other options, the data without blank rows. But how do you remove the blank rows from your data? The following VBA code does the following:
  1. Select a cell in the maximum row
  2. Check if the selection is blank
  3. Delete if blank
  4. Move up 1 cell

      Dim x As Integer
      ‘ Set numrows = number of rows of data.
      NumRows = 1400 -2
      ‘ Select max cell
      Range(“B1400”).Select
      ‘ Establish “For” loop to loop “numrows” number of times.
      For x = 1 To NumRows
         ‘ Insert your code here.
         If Selection.Text = “” Then
            Selection.EntireRow.Delete
         End If
         ‘ Selects cell down 1 row from active cell.
         ActiveCell.Offset(-1, 0).Select

There is probably a smarter way to select the maximum number of rows, but for my data it was fixed.

Macro: Remove Spacing Between Table Cells

I received a document that had hundreds of tables, all with a nice 3D border. Then somebody said that the border looked ugly so I started writing a macro to change all the borders to something more plain. Changing the borders was simple, but the cells had space between them which resulted in a double border. Getting rid of this space was not so simple.
Finally, I found this little macro at the Egghead Cafe:
With Dialogs(wdDialogTableTableOptions)
   .AllowSpacing = 0
   .Execute
End With
 
A second method (that I did not try) is to use this:


Selection.Tables(1).Spacing = -1


 


The full macro follows:




Sub remove3dBorder()
'
' remove3dBorder Macro
'
'
Dim myT As Table

For Each myT In ActiveDocument.Tables

    myT.Select
    
    'Selection.Rows.HeadingFormat = True
    With Selection.Tables(1)
        With .Borders(wdBorderLeft)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderRight)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderTop)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderBottom)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderHorizontal)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        With .Borders(wdBorderVertical)
            .LineStyle = wdLineStyleSingle
            .LineWidth = wdLineWidth050pt
            .Color = wdColorAutomatic
        End With
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
 
        .TopPadding = CentimetersToPoints(0)
        .BottomPadding = CentimetersToPoints(0)
        .LeftPadding = CentimetersToPoints(0)
        .RightPadding = CentimetersToPoints(0)
        With Dialogs(wdDialogTableTableOptions)
            .AllowSpacing = 0
            .Execute
        End With
        
        .AllowPageBreaks = True
        .AllowAutoFit = True
    End With
    
    ' turn on header row
    myT.Cell(1, 1).Select
    Selection.Rows.HeadingFormat = True
    
    Next myT
End Sub 




[Source Egghead Cafe and Google Answers] 

	


	



			
				


			

			
Macro: Repetitive Replacing of Table Cells
		
	
	

		
Here is today’s time saver:




I had some tables (actually 1000 pages of tables!) where I needed to replace cells containing various text (denoted by comments from the SME) with a standard word (which was Reserved in this case). Normally, I would triple-click in the cell and then control-v to paste in the text (5 clicks/keys), but there were too many places that I needed to make this change.




Using the following macro this task was reduced to two clicks, once in the cell and then once on the button for the macro. There was one other requirement for this macro, which was that if the cell was in bold, it received slightly different text.




Sub makeCellReserved()
   Selection.SelectCell


   If Selection.Range.Bold = True Then
       Selection.Text = “RESERVED”
   Else
       Selection.Text = “Reserved”
   End If
End Sub

	


	



			
				


			

			
Excel Macro for Background Color
		
	
	

		


I was writing an Excel Macro and I needed to set the background color of a cell based on the text value. To do this I wanted to use Conditional Formatting, and I found out that you cannot (AFAIK) setup Conditional Formatting from inside of a macro. But, you can create a macro that loops through a range and sets the formatting.




Here is how:


    

  1. Open the VB editor (Alt + F11)
    2. 

  2. Open ThisWorkbook under Microsoft Excel Objects
    3. 

  3. Add the following code:
    4. 



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Set myRange = Range(“C4:C104”)


    For Each Cell In myRange


        If Cell.Value = “n” Then


            Cell.Interior.ColorIndex = 3


        End If


    Next


End Sub




Anytime  a change is made to the workbook, the macro is called. It checks a range of cells, and then if the text is ‘n’ it sets the background color to 3. But what is ‘3’?




Excel uses an object called ColorIndex which takes an integer as its argument. The integer is one of 56 colors, given by the following table (a link to the source is below):








[Color 0]
[Color 0]

[Color 15]
[Color 15]

[Color 30]
[Color 30]

[Color 45]
[Color 45]




[Color 1]
[Color 1]

[Color 16]
[Color 16]

[Color 31]
[Color 31]

[Color 46]
[Color 46]




[Color 2]
[Color 2]

[Color 17]
[Color 17]

[Color 32]
[Color 32]

[Color 47]
[Color 47]




[Color 3]
[Color 3]

[Color 18]
[Color 18]

[Color 33]
[Color 33]

[Color 48]
[Color 48]




[Color 4]
[Color 4]

[Color 19]
[Color 19]

[Color 34]
[Color 34]

[Color 49]
[Color 49]




[Color 5]
[Color 5]

[Color 20]
[Color 20]

[Color 35]
[Color 35]

[Color 50]
[Color 50]




[Color 6]
[Color 6]

[Color 21]
[Color 21]

[Color 36]
[Color 36]

[Color 51]
[Color 51]




[Color 7]
[Color 7]

[Color 22]
[Color 22]

[Color 37]
[Color 37]

[Color 52]
[Color 52]




[Color 8]
[Color 8]

[Color 23]
[Color 23]

[Color 38]
[Color 38]

[Color 53]
[Color 53]




[Color 9]
[Color 9]

[Color 24]
[Color 24]

[Color 39]
[Color 39]

[Color 54]
[Color 54]




[Color 10]
[Color 10]

[Color 25]
[Color 25]

[Color 40]
[Color 40]

[Color 55]
[Color 55]




[Color 11]
[Color 11]

[Color 26]
[Color 26]

[Color 41]
[Color 41]

[Color 56]
[Color 56]




[Color 12]
[Color 12]

[Color 27]
[Color 27]

[Color 42]
[Color 42]







[Color 13]
[Color 13]

[Color 28]
[Color 28]

[Color 43]
[Color 43]







[Color 14]
[Color 14]

[Color 29]
[Color 29]

[Color 44]
[Color 44]











A more complex example is shown below. This is used in a ToDo sheet. It checks the “Done” column (C) to see if  the cell is marked ‘y’ or ‘n’ and sets the corresponding color. If the task is done (C=’y’) then it also sets the cell adjacent tothe color gray with strikethrough text.




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Set myRange = Range(“C4:C104”)


    For Each Cell In myRange


    


        If Cell.Value = “n” Then


            Cell.Interior.ColorIndex = 3


        End If


        If Cell.Value = “y” Then


            Cell.Interior.ColorIndex = 10


        End If




        If Cell.Value  “n” And Cell.Value  “y” Then


        Cell.Interior.ColorIndex = xlNone


        End If


        


        ‘ add strikethrough


        Dim myRow As Integer


        Dim myCol As Integer


        myRow = Cell.Row


        myCol = Cell.Column – 1


        


        If Cell.Value = “y” Then


            Cells(myRow, myCol).Font.Strikethrough = True


            Cells(myRow, myCol).Interior.ColorIndex = 15


        End If


        


        If Cell.Value = “n” Then


            Cells(myRow, myCol).Font.Strikethrough = False


        End If


    Next


End Sub




Happy coding 🙂


[Source MVPS site] 

	


	



			
				


			

			
Word: Show Me My Property
		
	
	

		
Word documents have many user-accessible properties, such as Author, Title, etc.  One can find them by clicking on the Word Button -> Prepare -> Properties.  These properties can also be accessed by a macro using the BuiltInDocumentProperties method.




This post will show you which properties are available and how to access them using macros.






To access individual properties, use this:




Sub showProperty() 


    Set myProp = ActiveDocument.BuiltInDocumentProperties(wdPropertyAuthor)
    ‘MsgBox (“Value of this property ->” + myProp + “<-")


End Sub




(I like to enclose my displayed values with some character, like an angle bracket, to make it easy to see any preceding or trailing spaces)




In place of wdPropertyAuthor, you can use any of the following properties:


    

    • 

    

  • wdPropertyAppName
    • 

  • wdPropertyAuthor
    • 

  • wdPropertyBytes
    • 

  • wdPropertyCategory
    • 

  • wdPropertyCharacters
    • 

  • wdPropertyCharsWSpaces
    • 

  • wdPropertyComments
    • 

  • wdPropertyCompany
    • 

  • wdPropertyFormat
    • 

  • wdPropertyHiddenSlides
    • 

  • wdPropertyHyperlinkBase
    • 

  • wdPropertyKeywords
    • 

  • wdPropertyLastAuthor
    • 

  • wdPropertyLines
    • 

  • wdPropertyManager
    • 

  • wdPropertyMMClips
    • 

  • wdPropertyNotes
    • 

  • wdPropertyPages
    • 

  • wdPropertyParas
    • 

  • wdPropertyRevision
    • 

  • wdPropertySecurity
    • 

  • wdPropertySlides
    • 

  • wdPropertySubject
    • 

  • wdPropertyTemplate
    • 

  • wdPropertyTimeCreated
    • 

  • wdPropertyTimeLastPrinted
    • 

  • wdPropertyTimeLastSaved
    • 

  • wdPropertyTitle
    • 

  • wdPropertyVBATotalEdit
    • 

  • wdPropertyWords
    • 



To list all properties in the document, run this macro.  It will put them at the end of the document.




Sub ListProperties()
    Dim rngDoc As Range
    Dim proDoc As DocumentProperty


    Set rngDoc = ActiveDocument.Content


    rngDoc.Collapse Direction:=wdCollapseEnd


    For Each proDoc In ActiveDocument.BuiltInDocumentProperties
        With rngDoc
            .InsertParagraphAfter
            .InsertAfter proDoc.Name & “= “
            On Error Resume Next
            .InsertAfter proDoc.Value
        End With
    Next
End Sub







	


	



			
				


			

			
Preventing Emails with Blank Subject Lines in Outlook
		
	
	

		
Here is a nice fix to prevent Outlook from sending emails without anything in the subject:




Save yourself the annoyance by following the next steps.


    

  1. Open the Visual Basic Editor (in Outlook, press      ALT+F11).
    2. 

  2. On the left-hand side of the screen, expand the      ‘Project1′ (go into      ‘Microsoft Office Outlook Objects’ -> ‘ThisOutlookSession’).
    3. 

  3. Double click on ‘ThisOutlookSession’
    4. 

  4. Paste the following lines of code in the      right-hand pane of the screen:
    5. 





Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)


    If Item.Subject = “” Then


    Cancel = MsgBox(“This message does not have a subject.” & vbNewLine & “Do you wish to continue sending anyway?”, vbYesNo + vbExclamation, “No Subject”) = vbNo


    End If


End Sub




Save the project (‘File’ -> ‘Save’) and return to Outlook. From now on, the code will be called every time you press the send-button after you composed an email. Outlook will pop up a warning when you try sending an email with an empty subject line.