# personal.xls - whats in your box?



## scrupul0us (Mar 2, 2005)

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 (Mar 2, 2005)

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 (Mar 2, 2005)

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 (Mar 2, 2005)

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 (Mar 2, 2005)

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 (Mar 3, 2005)

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 (Mar 3, 2005)

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 (Mar 3, 2005)

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 (Mar 3, 2005)

Cbrine said:
			
		

> 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 (Mar 3, 2005)

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.


----------



## dk (Mar 3, 2005)

dk said:
			
		

> Cbrine said:
> 
> 
> 
> ...



Sorry mate - I didn't read your post properly.  I guess you already know how to disable it.

Dan


----------



## litrelord (Mar 4, 2005)

That's pretty much what I've got but it seems to come back now and then when I use it so I've just put


```
Private Sub Workbook_Open()
Application.CommandBars("Reviewing").Enabled = False
End Sub
```

into the workbook module.

I'm sure it's a brilliant and well designed part of office all the reviewing tools but i've never found them any use personally.

Nick


----------



## Zack Barresse (Mar 4, 2005)

litrelord said:
			
		

> ..but i've never found them any use personally.


Great for group reviewing/editting.  The Word version is better, imho; easier to navigate/control.


----------



## JamesKM (Mar 7, 2005)

I have a few modules:
1. Formatting (dates, currency, numbers, percentages, wrapping, unwrapping etc that are then put in one toolbar menus)
2. Print options (toggle gridlines, select printer and paper and paper size, add footers - various, add headers - various, landscape/portrait, margin manipulation, repeat top line, repeat left column, freeze panes at specific points, etc)
3. Worksheet specific (import data, run reports etc)
4. Spellchecker
5. Auto-open (disables F1 as help key)
6. Misc - add watermark, remove hyperlinks, timing test for macros.
7. Functions - mainly text manipulation ie get date from "Import##-20050307-A"

Most of the code was either recorded (formats) or copied from this site (disable F1, spellcheck, extract nth element)

James


----------



## steve case (Mar 8, 2005)

Hmmmm

Some one already mentioned the "Paste Values" short cut key, I think that one gets the most use.  I've got it set up as [Ctrl]+[Shft]+[V] in the personal file. 

Then there's the "Where is this file" which pastes the pathway to the file in a cell.  

Most of my "Toys" are rigged with an "Open Read Only" workbook macro which necessitates a "Defeat Read Only" routine for me to perform maintenance on them.  It's great to watch that annoying (Read Only) notice simply wink off when it runs.  

The Application Activate and Send Keys commands are great for getting raw data from outside of Excel.  

Application Screen Updating = False/True must be in every job I've done. 
Also End(xlUp) gets a lot of use. 

All my knowledge is "On the Job Trial and Error" or ask Mr. Excel.  I could really use a formal class.  Some of the stuff I've gotten from this board is best termed as esoteric.  I have no idea how or why it works.  I'm just thankful that it does.  Those dimension as string routines plus a few others are magic as far as I'm concerned.


----------

