One of the main reasons that I use Microsoft Excel is to keep lists to share with multiple people. Microsoft Word has great versioning and colabarative editing capabilities, Excel not so much. The main problem I have is knowing when a particular row has been changed and who changed it.
The following macro implements this.
I have just finished reviewing an excel spreadsheet where a number of the rows were formatted with strikethough : like this.
The formatting was valid and the rows have to stay there – but I have no need to review those rows. In excel you can filter rows, by contents or even by colour but not by format.
Enter a VBA user function:
I commonly have large excel spreadsheets with many tabs or worksheets within the same workbook.
I use the following Microsoft excel macro to generate a contents page.
NOTE: I’m assuming the “Contents” worksheet will be the first and the list will be generated from A2 downwards.
Private Sub CommandButton1_Click()
For i = 2 To Sheets.Count
Range("a" & i) = Sheets(i).Name
ActiveSheet.Hyperlinks.Add Anchor:=Range("a" & i), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
Next i
Columns("A:A").EntireColumn.AutoFit
End Sub
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.
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.
One of my main productivity “sinks” is continually monitoring Outlook to see if any mails have arrived.
I have a rule set up that notifies me if an “important” email lands but for this to run, outlook needs to be running (minimised of course). I can’t resist the temptation to just “have a peek” outside my first thing in the morning, last thing at night email window.
To get around this I want to be “discouraged” from opening Outlook.
To implement this I have used Antonio Franca’s fantastic WinTrigger AutoHotkey script.
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.
Just found an interesting feature in SQL Developer 3
I created a dataset that contained two columns of numbers that I wanted to quickly get into excel. I choose, export in CSV format to clipboard so that I could quickly paste it into an existing spreadsheet.
My dataset contained:
1234, 5678
2345, 6789
3456, 7890
The data that was exported was:
12,345,678
23,456,789
34,567,890
Have to export to an xls file, open it then copy and paste – YAWN !
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.
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!