Tag: vba

Automatically highlight selected Excel row

Here’s a simple script that makes it easier to visually scan lengthy Excel spreadsheets. If you spend a lot of time in Excel spreadsheets, you’ll find this useful.

What you’ll need

  • .XLSM Excel workbook
  • Macros enabled
Excel spreadsheet with color highlighting
Excel VBA script that automatically highlights current selected row
Excel VBA script that automatically highlights current selected row

Although I like this above script, you do have to hard code a few values. Here’s another Exel video that is more straight forward. This method also highlights the columns. This video, assumes you already know how to enable the developer tab and create formulas however. 

'Visual Basic for applications code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With ThisWorkbook.Names("CurrentRow")
    .Name = "CurrentRow"
    .RefersToR1C1 = "=" & ActiveCell.Row
'above "&" should be the ampersand symbol. 
    End With

End Sub

Outlook script to export /copy email data

Have you tried selecting an email in Outlook and copying it to learn that it copies the entire .msg file instead of just the raw data such as the subject line or just the sender? Perhaps you want to create a log of emails from certain senders so you can categorize the emails in Excel or just have a printed record. Here’s a handy visual basic for application(VBA) script that does this. I tested this with a folder of over 2,000 emails and it works well. It will likely take a while to execute if select a folder with a large number of emails. To use this code, you’ll have to create a macro in Outlook. Note, the output will be sent to the immediate window. This is similar to the debugging window in Outlook.

Sub PrintSubjectLineForSelectedFolder()
  
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder
Dim msg As Object
 Dim myItems As Outlook.Items

Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
Set olFolder = objNS.GetDefaultFolder(olFolderInbox)

    Dim objFolderSelected As Folder
     Set objFolderSelected = objNS.PickFolder
    If TypeName(objFolderSelected) <> "Nothing" Then
    
        Debug.Print objFolderSelected
        Set olFolder = objFolderSelected
        'Set olFolder = olFolder.Folders("Test Results")' can also be hardcoded
      
        For Each msg In olFolder.Items

'Debug.Print TypeName(msg)
    If msg Is Nothing Then
'If varValue = Nothing Then

        Exit For
   
	Else

    If Not msg Is Nothing Then
 
        Debug.Print msg.Subject
        
       
   End If
   
   End If
   
Next

    Else
    
        Debug.Print vbCr &amp; "User selected Cancel No Folder selected"
    End If

    Set objFolderSelected = Nothing
    Set objNS = Nothing

End Sub