Solutions Archive

After inserting many comments in an Excel workbook, you may want to customize the fonts of all the comments. In this situation, you can refer to this article. Here we will share you 2 easy methods.

As we can see, the comments inserted in Excel automatically use the default font. At times, you may want to alter the comments’ fonts. If there are a few comments, you can simply use the following Method 1 to manually change them. However, if there are hundreds of comments, you are better off selecting the latter means.

Method 1: Change One by One

  1. First off, select a comment in the worksheet.
  2. Then, right click on it and select “Format Comment” from the context menu.

    Select "Format Comment" Menu Item

  3. Next, in the small popup dialog box, you can select fonts and styles to your liking.

    "Format Comment" Dialog

  4. Ultimately, click “OK”.
  5. Later, you can use this way to manually change comments one by one.

Method 2: Batch Change via VBA

  1. For a start, launch Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
  2. Then, in the “Microsoft Visual Basic for Applications” window, put the VBA code below into a project or module.
Sub BatchChangeFontsOfAllComments()

    Dim objWorksheet As Excel.Worksheet

    Dim objComment As Excel.Comment

   

    'Process all worksheet one by one

    For Each objWorksheet In ThisWorkbook.Worksheets

        If objWorksheet.Comments.Count > 0 Then

           For Each objComment In objWorksheet.Comments

               With objComment.Shape.TextFrame.Characters.Font

                    'Specify font name and size

                    .Name = "Comic Sans MS"

                    .Size = 10

                    'Use RGB function to create a color value

                    .Color = RGB(255, 0, 0)

                    .Italic = True

                    .Bold = True

               End With

           Next

        End If

    Next

End Sub

 

VBA Project

  1. Later, run this macro simply by pressing “F5” key button.
  2. Eventually, after macro finishes, back to the worksheets.
  3. As you can see, the fonts of all comments have been changed, as shown in the following screenshot:

Change Comment Font Result

Comparison

Advantages Disadvantages
Method 1 Quick and easy to process a small amount of comments Very troublesome in case of too many comments
Method 2 Very efficient regardless of the number of comments Increase risks of virus infection

Restore Compromised Excel File

When your computer suddenly shut down, if some Excel files haven’t been saved, the next time when PC boots back up, you may find the Excel files are corrupted. At this point, if you would like to get back the files, you have no choice but to take aid of a powerful and reliable Excel recovery tool, like DataNumen Excel Repair, which can recover corrupted Excel data with effortless ease.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix sql and outlook repair software products. For more information visit www.datanumen.com

When flagging a contact, some users hope to get a standard Outlook task in To-Do List instead of a flagged contact. Now, in this article, we will introduce a method to let Outlook auto create a task for call the contact person later when flagging a contact.

As we all know, when flagging a contact, the contact will display in the To-Do list. But, most of time, what we really want is a standard task for calling this contact later and contains the telephone numbers of the contact only in task body. Hence, here we will introduce a method to configure Outlook to auto create such a task. Read on to get more details.

Auto Create a Task to Call the Contact Person Later when Flagging a Contact

  1. At first, launch Outlook VBA editor with reference to “How to Run VBA Code in Your Outlook“.
  2. Then, open the “ThisOutlookSession” project and put the following code into it.
Private WithEvents objContacts As Outlook.Items

'Works for contacts in the default Contact folder
Private Sub Application_Startup()
    Set objContacts = Outlook.Application.Session.GetDefaultFolder(olFolderContacts).Items
End Sub

Private Sub objContacts_ItemChange(ByVal Item As Object)
    Dim objContact As Outlook.ContactItem
    Dim objTask As Outlook.TaskItem

    If TypeOf Item Is ContactItem Then
       Set objContact = Item

       If objContact.IsMarkedAsTask = True Then
          If objContact.TaskCompletedDate = "1/1/4501" Then
             'Create a task for calling this contact
             Set objTask = Outlook.Application.CreateItem(olTaskItem)
             With objTask
                  .Subject = "Call " & objContact.FullName
                  .StartDate = objContact.TaskStartDate
                  .DueDate = objContact.TaskDueDate
                  .Attachments.Add objContact
                  .Body = "Business: " & objContact.BusinessTelephoneNumber & vbCr & "Home: " & objContact.HomeTelephoneNumber & vbCr & "Other: " & objContact.OtherTelephoneNumber & vbCr & vbCr
                  .ReminderSet = True
                  .ReminderTime = objContact.ReminderTime
                  .Save
                  .Display
             End With
             objContact.ClearTaskFlag
             objContact.Save
        End If
      End If
   End If
End Sub

VBA Code - Auto Create a Task to Call the Contact Person Later when Flagging a Contact

  1. After that, put cursor in the “Application_Startup” subroutine and press “F5” key to activate this macro.
  2. Finally, exit the VBA editor and try this macro right away.
  • Select or open a contact and flag it.Flag a Contact
  • At once, a new task will be displayed, like the following screenshot.Popup Task
  • Meanwhile, as you can see, the contact’s flag is automatically removed.

Repair Compromised PST Files

Like Word documents, Outlook PST file is also prone to corruption. Many users have been plagued by the problems related to inaccessible PST files. Fortunately, Outlook comes pre-installed with an inbuilt repair utility – Scanpst. It is indeed able to fix a majority of small PST issues. Yet, when confronting seriously corrupt PST file, it seems ineffective. Thereby, at that time, you have to take aid of a more powerful tool, like DataNumen Outlook Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and outlook repair software products. For more information visit www.datanumen.com

If each worksheet in an Excel workbook contains several charts and now you would like to batch move all charts to a new created worksheet, you can refer to this post. Here we will introduce you 2 easy ways.

After creating charts for the data in each worksheet of an Excel workbook, so as to better analyze data according to these charts, you may want to gather all these charts to a same worksheet. In other words, you may hope to move all of them to a new worksheet. If there are few charts, you can just use the following Method 1 to move one by one. Yet, if there are a myriad of charts, you are better off utilizing the latter one.

Method 1: Manually Move Chart One by One

  1. At first, create a new worksheet where you want to move all charts.
  2. Then, open a worksheet and pitch on a chart in it.
  3. Next, right click on the chart and select “Move Chart” from the context menu.Move Chart
  4. In the popup “Move Chart” dialog box, select the new sheet in “Object in” list and click “OK”.Select Location
  5. At once, the selected chart will be moved.Chart Is Moved to New Sheet
  6. Now, you can use this way to process all the charts in all worksheets one by one.

Method 2: Batch Move with Excel VBA

  1. To begin with, launch Excel VBA editor according to “How to Run VBA Code in Your Excel“.
  2. Then, put the following code into a module or project.
Sub MoveAllCharts()
    Dim strNewSheet As String
    Dim objTargetWorksheet As Worksheet
    Dim objWorksheet As Worksheet
    Dim objChart As Object

    'Create a new sheet
    strNewSheet = "Charts"
    ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1)).Name = strNewSheet
    Set objTargetWorksheet = Application.Worksheets(strNewSheet)

    For Each objWorksheet In Application.ActiveWorkbook.Worksheets
        If objWorksheet.Name <> strNewSheet Then
           'Move each chart to the new sheet one by one
           For Each objChart In objWorksheet.ChartObjects
               objChart.Chart.Location xlLocationAsObject, strNewSheet
           Next objChart
        End If
    Next

    objTargetWorksheet.Activate
End Sub

VBA Code - Batch Move All Charts from All Exiting Worksheets to a New Created One

  1. Afterwards, directly press “F5” to run this macro right now.
  2. When macro finishes, exit VBA editor.
  3. In Excel main window, you can see a new worksheet, in which you can see all the charts.Batch Moved Charts in the New Excel Worksheet

Comparison

  Advantages Disadvantages
Method 1 Easy to operate You have to move one by one
Method 2 Can process all charts in bulk Users have to beware of external macros

Repair Damaged Excel File

Excel file is prone to corruption since Excel can crash now and then. Under such a circumstance, you’d better prepare a powerful and trustworthy xlsx recovery tool, such as DataNumen Excel Repair. It will come in handy when your Excel file gets corrupted and Excel’s auto-recovery feature doesn’t work.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair sql and outlook repair software products. For more information visit www.datanumen.com