Tag: Office

Categories
Microsoft Office Technology

Excel – Split one cell to multiple rows

There are times when you have an excel sheet which has multiple lines of text in a single cell, which has been split using a carriage return. NOTE: I’m not talking about text which has wrapped due to the size or formatting of the cell.

If you need to separate the contents of this single cell, into one row per line then this is the macro for you. NOTE: This macro will insert rows into your sheet so you may have to “fix” the layout afterwards. Save your sheet before you run this just in case.

The first function processes the current cell – use this if you only have one cell which you want to split.

Public Sub SplitCellToRows()
    arrValues = Split(ActiveCell.Value, vbLf)
    For i = UBound(arrValues) To LBound(arrValues) Step -1
        'MsgBox i & " " & arrValues(i)
        If i > 0 Then
            ActiveCell.Offset(1).Resize(1).EntireRow.Insert (1)
        End If
        ActiveCell.Offset(Sgn(i)).Value = arrValues(i)
    Next i
End Sub

If you have multiple cells which you want to split out then there is a wrapper macro which will call this multiple times.

Public Sub SplitCellToRows_Multiple()
    For Each cell In ActiveCell.CurrentRegion.Cells
        cell.Select
        SplitCellToRows
    Next cell
End Sub

To use, simply highlight one or more cells and then run the appropriate macro.

Categories
Microsoft Office Technology

Excel – Unfilter all sheets

There are times when you are using an Excel workbook and you simply want to search for some content but on one of the tables on one of the sheets the table which contains the data has been filtered. You can spend more time looking for and then removing the filter than you do in running the actual search.

This macro will remove all filters from your current workbook.

Continue reading “Excel – Unfilter all sheets”
Categories
Microsoft Office

QUESTION: Outlook Macro Not Running

I’m looking for some help.

I have a variety of Outlook macros (see here) that run automatically when I send an e-mail. Recently these have stopped working and I see no errors.

If I manually trigger the same macro then it works and from that point onwards it will run automatically.

Any thoughts?

Categories
Microsoft Office Productivity Technology

Useful Outlook Macros

Quite often I want to create a task immediately off the back of sending an e-mail – usually to remind me to follow up with the recipient after a period of time. I created a macro to do this rather than having to remember to go into tasks and do this manually – better to keep the flow rather than having to remember the steps.

Continue reading “Useful Outlook Macros”
Categories
Microsoft Misc Office Productivity Technology Work

Add Reminders To Outlook Appointments

A Reminder
Creative Commons License photo credit: Andrew Coulter Enright

If like me your Outlook calendar can get a bit hectic and others can add meetings to your calendar sometimes you end up simply “reacting” to your schedule and jumping from one appointment to the next on “autopilot”. It’s at times like these that Outlook’s ability to create a reminder can save your dignity.

However, sometimes the meeting organiser does not set a reminder. I have created an Outlook macro that will look for incoming meeting requests, without a reminder set and give you the option to set a reminder.

Continue reading “Add Reminders To Outlook Appointments”
Categories
Microsoft Office Windows Work

Multiple Excel Text Boxes With Common Content

I have just had to place a textbox object onto 17 worksheets within the same excel workbook, each text box containing the same commentary text. Type it once and then copy the text box and paste, job done.
Of course as soon as I have finished it I spot my typo and realise I will have to do it all again.
Only NOW do I slow down and think (OK I Google it) of the proper way to do it.
When you create a text box, rather than typing the text into the text box as I have been doing for years, with the text box selected you can type into the formula bar. In my case I put in the formula “=Metadata!B2”. This means that the textbox will display whatever I type into cell B2 on my “Metadata” worksheet.
Now I edit the textboxes I have created to reference the formula and in the future if the text needs to change, I change it in Metadata!B2 and the new text appears in all of the textboxes that refer to it!
NOTE: The text displayed in a formula driven text box is limited to 256 characters.

Categories
Microsoft Office Windows Work

Microsoft Office–VBA Editor Keeps Opening

For weeks now I have had the mild annoyance that the VBA code editor for various Excel spreadsheet’s appear to be opening at “randomâ€?. I finally tracked it down to me unlocking my PC. If I have excel running then lock my PC, when I unlock it, the VBA code editor appears.

I’m using Office 2003 SP3 on XP Pro SP3.

Turns out that there is a VERY easy fix – don’t maximise the VBA code editor.
If the editor is maximised, it appears on unlock, if it is not maximised it does not appear automatically on unlock.

Categories
Microsoft Office

Changing Excel Cell Values To Uppercase

A nice quick tip, I had lots of cell values in a mixture of cases that should have been uppercase.

Create a macro:

sub MakeUpper()

for each myCell in Selection

  myCell.Value = UCase(myCell.value)

next myCell

end sub

Add a menu item to your toolbar and then assign this macro to it.

Select the cells you want to upper case then press the button!

Categories
Microsoft Office Work

Update All Fields In MS Word

Sometimes you find a feature in a product that is just blatantly missing. One of those in Microsoft Word is the ability to update all the fields in the document with the latest value in a field.

I use both the built in fields e.g. Author, Version, Title and custom fields e.g. Project Code. I set the values once in the document properties and then insert the field codes in the document rather than the text. All is well. If you change the value of a field you can press F9 and all of the fields update to the latest value EXCEPT if you have used the fields in the document header or footer, these do not get updated for some reason. I would classify this as a bug, why would I not want all instances of the same field to be in sync throughout my document.

The solution is remarkably simple. Create the following macro:

Sub UpdateAll()
Dim oStory As Range
Dim oField As Field
For Each oStory In ActiveDocument.StoryRanges
For Each oField In oStory.Fields
oField.Update
Next oField
Next oStory
End Sub

This can be saved in your normal.dot file and will be available in all your word documents.

Categories
Microsoft Office Technology

Favourite Shortcut Keys – Outlook

Apple Keyboard (with Avid shortcuts; Letterboxed)
Creative Commons License photo credit: laffy4k

If you look at the left hand menu you will see the items:

  • Mail
  • Calendar
  • Contacts
  • Tasks
  • etc

These can be quickly accessed by using the following shortcut keys:

  • (Ctrl+1) – Mail
  • (Ctrl+2) – Calendar
  • (Ctrl+3) – Contacts
  • (Ctrl+4) – Tasks

In addition, when you use Ctrl+1 to access mail, this takes you to the folder that you were last using, if you would rather jump straight into your In-Box – use (Ctrl+Shift+I).