personal.xls - whats in your box?

[scrupul0us]

So as I posted a few weeks ago, I'm starting to build up my personal.xls by going thro all my old macros and sorting out snippets and routines... I was just wondering if theres any "must haves" for someones personal.xls. I guess this would depend on what you macro mainly, for me, I do a TON of non-delimited text importing / cleaning / sorting / reporting etc...

Just wondering whats in your arsenal

 

[Von Pookie]

I don't have much in mine.

One from the KB to force the task pane to display on open, and then little ones to let me use certain shortcuts to fill left, up, merge selected cells and use the normal ctrl+e shortcut to center text (I was *constantly* trying to use that and it wouldn't do anything. So annoying).

 

[scrupul0us]

I've got so many that I've had to break out into modules for each category. Right now I have 8 categories rnaging from string manipulation to moving data, to various ways of deleting rows.

 

[Cbrine]

I've got about 8 or 9 macros with mine. The biggest one being a formatting macro that I put together to format my MSAccess query dumps to excel. I just copy the query data, paste it to excel, and run my macro. Same format everytime, and it does all the extras that I always forget. Adds titles, footers, formats, adds grid lines, and setups the printing range.
Also a few general purpose macro's, a trim tool to trim a list to unique entries, and couple of password macro's for the passwords I use on most workbooks(Easier then typing them in all the time). A GetColor UDF for use on sorting by color. A paste Values macro that I have assigned to a shortcut key, since I'm always do that.
Hope this gives you some ideas.

Cal

 

[Glaswegian]

Well I've got a real mixed bag. Some small macros, mostly for ActiveX controls, some general formatting stuff that I use regularly, a couple of functions (finding files in sub-directory etc) that I don't really use that much and a couple of Ivan's API routines. I've also got some menu bar controls but they are commented out - at least I know where the basic code is and don't have to start from scratch!

Heaven only knows what that tells you!

Regards

 

[litrelord]

Quite a few small macros, formatting mostly and a couple to open some workbooks which I'm always using (opened from a menu on the toolbar).

Anything which starts to grow too much in size I either make an add-in or quite often just have a workbook with the code unopened but have a toolbar button/menu item for it and have the last line of the code and thisworkbook.close so it loads and unloads itself.

Oh, and a workbook event to disable to reviewing commandbar as I never use it so it just serves to drive me crazy.

Nick

 

[Cbrine]

litrelord,
Can you pass along the code you use to disable it. It drives me nuts, but not quite nuts enough to dig into disabling it. If you already have, then I don't need to do the work.

Cal

 

[Von Pookie]

Oh! I forgot about it, but I have that as well.

I got tired of it popping open on certain files, so I added this to personal.xls. It doesn't disable it entirely, but it closes the reviewing toolbar if it is open on file open:

Private Sub Workbook_Open()
On Error Resume Next

If Application.CommandBars("Reviewing").Visible = True Then
    Application.CommandBars("Reviewing").Visible = False
End If

End Sub

 

[dk]

litrelord,
Can you pass along the code you use to disable it. It drives me nuts, but not quite nuts enough to dig into disabling it. If you already have, then I don't need to do the work.

Cal

You can disable it permanently (without having to run code every time Excel or a workbook opens) using Application.CommandBars("Reviewing").Enabled = False. Works well in Excel but not in Word unfortunately

Dan

 

[Zack Barresse]

Well, I've got some miscellaneous macros as well. The most frequently used are, as mentioned earlier, the formatting routines. Some routines to show multiple precedents/dependents. One to tell me the length of the cell in a message box (you wouldn't believe how much I actually use that little bugger! set to a keyboard shortcut). Another one like it to tell me the interior color of a cell.

Then on top of those, I carry most of my UDF's in there. The non-application specific one's anyway, that I like to have around all the time 'just in case'. Hmm, too many to list, but if you'd like to see them..

Everything else I compartmentalize into add-ins. Each add-in being about 5-7 times the size of my personal.xls. I actually try not to keep too much in the personal.xls file.