|
You can use the feeds in this mix as the basis for a new mix - create a new mix from this mix.
Concentration [New Window]
There is nothing like making a new game for a change of pace. This one will be added to JMT Excel Utilities soon but you can try an advance version if you like. Here is the download link. I'll also post how to get random numbers to shuffle the car... Using APIs with UserForms [New Window]
Here are a few things you can do with APIs to make your userforms more functional. All of the code shown will work with Excel 2000 or higher. Note that I am using modeless forms which enable you select ranges and the user interface (menus, other useforms ... Cell Watch Form 2 [New Window]
No pictures this time but here is what I have changed with the new version of the Cell Watch Form.If the workbook of the cell you want to watch is not open or has been deleted or renamed etc, the watchbook referred to will be the active workbook. The ... Document Properties [New Window]
Document properties allow us to get information about a workbook. Default properties are called BuiltinDocumentProperties in VBA. Straight from the Help files, here is some slightly modified code to view these properties and their values.Sub ShowBuilt... Custom Lists [New Window]
Custom ListsCustom Lists are great but I've always felt they could be used more easily. I hate dragging down with the plus cursor if I can help it, so I made a new toy to make life more bearable.Here is what I wrote in the read me file.Custo... Unique Random Numbers in VBA [New Window]
As mentioned in my last post, this is how I got random numbers for my new Concentration game. It uses the fact that only unique items (as far as I know) can be added to a collection. After attempting to add a new item (random integer), the code then count... Drawing Tools [New Window]
Recent changes to JMT Excel Utilities -Make Transparent ShapeThis is something I did long ago and thought it might be a handy tool to use. Don't forget that in addition to being a great spreadsheet prgram, Excel is also very convenient to make simp... FindControl [New Window]
This is an example of FindControl. You can use it to refer to a command bar control by using it's ID. The following code will add a comment by "executing" the Insert Comment control.Application.CommandBars.FindControl(ID:=1589).ExecuteOf course, you... JMT Excel Utilities Download [New Window]
Some people might have be having problems accessing the JMT Excel Utilities download site.So I have made a temporary download site where you can download them here.Do you the most recent version? Im always adding new stuff!While I am at it,... Determine Your Excel Version [New Window]
You may have code that requires a particular Excel version. You can check by using this line of code.MsgBox Val(Application.Version)Let's say you have code that requires Excel 2003 or higher, use the following at the beginning of your macro to inf... Chart Tools [New Window]
I am a chart newbie. At least, compared to some of the chart gurus I see, I am still taking my first steps. That has not stopped me from making some simple chart tools - I figure it's a good way to learn and we all have to start somewhere, right?Okay,... Goodbye 2008 [New Window]
How time flies when you are having fun. I'm in the middle of my first real vacation since I came back to Australia and it was certainly worth the wait.Anyway, this year has been a good year for me but it can always be improved. I've made my New Year's... New Tools [New Window]
JMT Excel Utilities has 3 new tools -Highlight Row Differences (as previously promised)Compare SheetsCopy VBE ModulesThe following tools were re-written to work properly with Excel 2007.Delete Active WorkbookRename Active WorkbookRou... Cell Watch Form [New Window]
At one of my reader's request, I have improved my Cell Watch Toolbar Addin. This time it's not a toolbar though, it's a userform.As you can see, there have been some changes. Add Cell Watch allows you to watch a cell in any worksheet, in any workb... Document Properties 2 [New Window]
CustomDocumentProperties are as the name custom properties that you can add to a workboook, just as BuiltinDocumentProperties exist by default.You can add CustomDocumentProperties using a variation of this code.ThisWorkbook.CustomDocumentProperties.... SnagIt 8 redefines "best" in screen capture [New Window]
I've been a huge SnagIt fan for a long time. It's a staple ingredient in my must-havelist of tools. and it's one of the first things I install on my system when I either rebuild or get a new toy.TechSmith has just announced SnagIt 8 and the new features and shiny interface are a great example of an upgrade thatdelivers solid value, improved usability, and a whole new set of "this is so cool!" ways to think about usingcaptured content. Here are a few of my favorites:Copy Web Pages with Links: You can capturea Web page with SnagIt and all of the links remain clickable.You can save the capture in a number of formats thatsupport embedded links: Web page with image (.mht), Adobe PDF, (.pdf) or Macromedia Flash (.swf). The links areembedded in the capture and clickable.Add Interactivity: SnagIt's new hotspotfeature makes adding interactivity easy. Use SnagIt's hotspot feature to specify parts of the image that candisplay text or images when moused over or open a Web page when clicked. These interface features allow you to addhelpful hints, explanatory illustrations, or even create a functioning prototype of a Web page or applicationinterface.Save as PDF: Self-explanatory. Save any screen capture as an Acrobat file - agreat addition to the wide range of file types SnagIt supports.A comparison chart showing all of thefeatures in SnagIt 8 and earlier versions is here. A newSnagIt 8 license can be yours for a very reasonable $39.95. If you are a current user, you can check your upgradeavailability from SnagIt itself on on the web - discounted upgrades are available for current customers. If you've yetto try this indispensable tool, a 30-day trial version can be downloaded here.Permalink | Email this | Linking Blogs | Comments egoSurfing - like web surfing only it's all about you [New Window]
The latest game tomeasure your online cred is called egoSurf and it searches across a number ofdomains including Google, Yahoo!, and Technorati to determine your score. The score is curious at best and suspiciousat worst but the toy is fun to play with and the resulting multi-dial display would make a fighter pilot smile. You cansubcribe to an RSS feed for your egoSurf too (not sure why you'd want to do that but you can). I was going to pretend tobe shy and retiring but since a number of other bloggers, including my podcast partner James Kendrick, have been posting theirscores, I figured I'd play along.Permalink | Email this | Linking Blogs | Comments The winds of change are blowing [New Window]
I've had this phrase set on my various instant messaging clients for the past few days and it's prompted more than a fewcurious inquiries about just what is up.This will be my last post here at The Office Weblog. I've beeninvited to contribute at ZDNet where I will be writing under the title "Office Evolution". Don't click just yet - I'll be back online sometimein the next few days, once the wires get twisted together and the lights start blinking.I've had anincredible time writing here at Weblogs, Inc. I jumped when Jason Calacanis invited me to join a handful of otherbloggers. Back then, the promise of this network was just that - tons of potential and and a lot of big dreams. Thesepast two years I've had the great fortune to work with some fantastically talented people, make more than a few goodfriends, and have had a chance to speak with, interview, and pick the brains of a number of scary smart people.After almost two years, nearly 2500 posts, and more than 2 million words, I am really excited about this newopportunity to explore the changing way we approach work: Office: Not just the obvious office software suite coverage but also Software as a Service, Web 2.0online applications, and the changing nature of office and knowledge work (think time-shifting, virtual teams andcompanies, telecommuters, and free agents). Productivity: I lovetalking about techniques and tools to get more done. Whether it's Getting Things Done, a great new add-in for Outlook,a compelling life hack, or even a Moleskine Journal, if it relates to reducing stress and creating more time to focuson what's truly important, I'll be all over it. Mobile Computing:From the Tablet PC to the Treo or Smartphone, we're getting cooler mobile toys every day. Not only do the latest mobileplatforms offer more computing power, better battery life, and greater usability than ever before, the variety of formfactors is increasing at an incredible pace. The recent introduction of the Nokia Internet Tablet and the refresh ofthe OQO palmtop are good examples of net-gen mobility tools that continue to push the envelope on portability andpower. USB thumb drives with a complete suite of software are another trend I'm simply fascinated by and plan tocover.As you can probably tell, I'm just a little bit excited. ;^)Check me out at the newaddress and let me know what you think. See you on the other side...Permalink | Email this | Linking Blogs | Comments MindManager add-ins for SharePoint [New Window]
Mindjet continues to find interesting waysto integrate MindManager into Microsoft's products. The latest addition is a set of add-ins that integrate mind maps into SharePoint, Microsoft'scollaboration server. Maps can be posted to a SharePoint space and checked out for local editing using MindManager 6.Very nice.Permalink | Email this | Linking Blogs | Comments Hyperwords extension out of beta [New Window]
Hyperwords is a new Firefox extension that provides an incredible rangeof actions you can perform on selected text on any page you can open in your browser. Options include search, referencelook up, mapping, tagging, blogging, and e-mailing. I love the power!What I didn't love in the early betareleases was Hyperwords popping its menu every time I selected some text. Simply stated, it got in my way. I mentiondthis to the developers and they assured me hat the official release would provide control over how the menu wasinvoked. I'm happy to report that Hyperwords now provides a number of options to manage the menu's appearance (seeimage below).Every option in Hyperwords has a single letter shortcut key associated with it so you canquickly invoke a particular operation by a series of letters to execute the command you wish to select. For example, ifI want to tag a page in del.icio.us, I simply tap g, p, d.Hyperwords is free and currently works in Firefox 1.5 on Windows, Mac OS, and Linux. An Internet Explorer version isbeing considered.Permalink | Email this | Linking Blogs | Comments Claritude releases SpeedFiler for Outlook [New Window]
Claritude Software has officially released SpeedFiler. I've covered SpeedFiler during its beta phase (here and here) and have found it to be awelcome addition to my Outlook environment. The two things this add-in does that have saved me enormous amounts of timeare: Filing new e-mails in a subject folder rather than dumping them into the Sent Items folder. Idon't always want to do this but when I do, it's a great time saver. You have the option of selecting any folder as thedefault repository for new e-mails you send or, like me, can choose on an individual message basis when you want toredirect where the message gets filed. Keeping my hands on the keyboard. I can file any message byinvoking the SpeedFiler hotkey (CTRL Shift V) and typing the first few letters of the destination folder's name. Muchfaster than using the mouse. I tend to use the SpeedFiler toolbar wen I'm in pen mode on my Tablet PC which is also agreat time saver since SpeedFiler caches the most recently accessed folders in a handy drop-down menu.Asingle license costs $19.95 and a trial version is available.Permalink | Email this | Linking Blogs | Comments Win a free GTD seminar and signed book [New Window]
The good folks at InBubbleWrap have hit the gold mine with today's offer. Enter for your chanceto win a free pass to a Getting Things Done seminar and autographed copy of the book signed by David Allen. This ispotentially life-changing stuff we're talking about here folks. Head right over and enter now.Permalink | Email this | Linking Blogs | Comments Firefly Season 2 - help make it happen [New Window]
Shiny. The Firefly saga may not be over yet. A group of folks are trying to bringback one of my all-time favorite sci-fi series are rallying the browncoats to demonstrate there is enough fan supportto warrant a new series. Alternative distribution is the key - they're thinking about pay-per-view, monthly DVDmailings, and streaming episodes to your PC or Mac. You can create a profile to add to the demographic data they'reassembling to convince the production and distribution companies this can fly. Check it out, add your profile, and keepyour fingers crossed.UPDATE: From the Season 2 page, it's clear this is an independenteffort:"We are an independent production company and core group of Browncoats. We are not affiliatedwith 20th Century Fox or Universal Pictures.We are currently pursuing the rights to continue the seriesfrom Fox and make it available to the fans, either directly or via another broadcaster.The optionalinformation collected at this site is simply used to determine where and how the series should be marketed toconsumers. (that's you!)This site is NOT a solicitation for funds. If anyone claiming to represent thissite, or any other site for that matter, asks you for money or sensitive personal information, you should report itimmediately."Permalink | Email this | Linking Blogs | Comments Sniping from the sidelines at Google Pack [New Window]
I didn't make it to CES. I'm bummed about thatfor a number of reasons (although I can't say I feel too deprived at missing the $18 hot dogs, endless taxi lines, andsardine can crush). Sitting on the sidelines, I've been able to take it all in courtesy of the crew from Engadget and my Tablet PC buddies JKand Rob.The big buzz all week was about what Google would orwould not be announcing. True to their word, they did not mention a Google PC (except to repeatedly deny any interestin building such a beast) and flatly denied interest in a Google OS when asked directly by Weblogs, Inc.s JasonCalacanis after the keynote. Instead, they fired the expected shot across the bow at iTunes and other purveyors ofdownloadable video, announcing some interesting partnerships with CBS and the NBA.The other announcementwas Google Pack - a collection of Google's software applications augmented by acurious selection of third-party products that Google wants to download, install, and monitor en masse on your PC. Ihave to agree with Joe Wikert ofWiley who points out that in the book publishing world this is called shovelware. Some folks in the Tablet PC worldare even less charitable and call it crapware.Google Pack includes: Adobe Reader 7 Ad-Aware SE Personal GalleryPlayer HDImages Google Desktop Google Earth Google Pack Screensaver GooglePicasa Photo Organizer/Editor Google Talk Google Toolbar for Internet Explorer Google Video player Mozilla Firefox with Google Toolbar Norton AntiVirus 2005 SpecialEdition RealPlayer TrillianAccording to Danny Sullivan at SearchEngineWatch, the impetus behindthis was the pain Larry and Sergey themselves experienced when setting up new PCs, having to click through countlessscreens of wizards, EULAs, and confirmations. Yes, it can be time-consuming to set up a new PC but I'm not buying intothis assortment nor am I interested in having Google scan, probe, and poke around my system. There's a fine linebetween being helpful and being intrusive and Google Pack steps over it in my opinion. The Google Updater, the gluethat binds this collection, is essentially a back door into my PC for Google to inventory and manage my software. In theory, you get to choose what'sinstalled and what's not. But apparently in practice, this doesn't quite play out. Norton, the worst selection in thispackage, apparently is installed by default, forcing you to then remove it from your system. Anyone who's wrestled withthe mess this product leaves behind will probably have the same reaction I did - ummm... no thanks.Not onlythat, it's a trial subscription, similar to what comes pre-installed on many systems today. After the trial, you haveto pay and pray - pray that you won't get hosed when the next version of Norton is released. The tales of Nortonupgrade and subscription woes you can find easily enough by Googling the product name are enough to convince mostpeople to stay far away from this product.Firefox? Got it thanks. Ad-Aware? Ditto. Acrobat Reader? I useAcrobat Pro and absolutely do not want or need the Reader program installed. I suppose a novice user witha shiny new PC might find this useful. Personally, I'd rather configure my family and friend's machines myself. WillGoogle grab some desktop real estate with Google Pack. Oh yes, I think they will. Is this, as a few wags have opined, aTrojan Horse? In many ways I believe it is.Permalink | Email this | Linking Blogs | Comments Great, in-depth review of Office 12 [New Window]
Filed under: Business ApplicationsActiveWin.com has a very well-written andhevaily illustrated review of Office 12 written by Andrede Costa. This is, to date, one of the two best resources I know of for people who either could not or chose not toparticipate in the beta of the next generation suite from Microsoft (Paul Thurrott's preview is the other). As I(and other beta testers like Ed Bott) have stated in the past, therules for writing about the beta are kind of murky. I'm glad Andre got permission to write such an in-depth andextensively illustrated review. If you're at all curious about what's coming in the next version, you will be too.Hat tip to Bink.nuPermalink | Email this | Linking Blogs | Comments Happy 250th Wolfgang [New Window]
Today is the 250th birthday of WolfgangAmadeus Mozart. Google marks the occasion with a nice logo and searchresults page. NPR has a slew of articlesand links.I remember as a kid going with may parents to the Young People's Concerts at Lincoln Center in NewYork. These concerts, performed by the New York Philharmonic Orchestra were first led by Leonard Bernstein and later byMichael Tilson Thomas. I have fond memories of trips to the city to listen to some fabulous music followed by lunch(usually crepes at The Magic Pan) and maybe a trip to F.A.O. Schwartz (if we didn't fidget too much during theconcert).Mozart was one of my favorites because of the stories the great conductors told about him and theawe they obviously held him in. My other favorite was Stravinsky. It wasn't until years later that I learned howscandalous this was ;^)Permalink | Email this | Linking Blogs | Comments Que jumps into eBooks - offers free Office 2003 guide [New Window]
Que Publishing has jumped into the eBookbusiness and is offering a free eBook guide to Office 2003 to introduce their new electronic versions of the 10-MinuteGuide series. Que is offering a free download ofthe 10-Minute Guide to Microsoft Office 2003 (PDF e-Book) for free to quickly learn how to use Office task panes,customize your Office applications, use the Office web integration features, and migrate from your older version ofMicrosoft Office to the 2003 edition. This PDF e-Book can be printed, saved, and e-mailed without limitations.Afull line of 10 Minute Guides on all of the Microsoft Office 2003 applications are available in Secure Acrobat PDFformat (DRM restrictions apply on saving the file and printing) for $6.95 each. The current promotion knocks 10% offthat price. Registered members of the site get a 30% discount which brings the price per title to under five bucks.Permalink | Email this | Linking Blogs | Comments Gmail delete button [New Window]
Sometimes, it's the little things that make all thedifference. One of the few real annoyances I've complained about in Gmail is the purposeful (on Google's part) lack of aDelete button. A while back, I tried GreaseMonkey and a script that provided this capability and promptly had my Gmailaccount suspended for "unusual activity".One burned, twice shy. I rely on Gmail for too manyinteractions to take a chance on losing access over a button.I'm happy to report that a new version of theGmail delete button is now available inboth GreaseMonkey script and compiled form. I've been using the compiled version (you do not need the GreaseMonkeyengine to use this) for a week now with no problems. Thank you Arantius!Permalink | Email this | Linking Blogs | Comments Call Log makes the Treo even more useful [New Window]
Chapura has released a great tool thatmakes the Treo 650 an even more powerful mobile tool. Call Log allowsyou to take notes related to any call you make (or receive) while you are on the call or after the fact. These notesare then synchronized to your Outlook Journal as a new record associated with the contact you were speaking with.I use a Bluetooth headset when I'm on a call on my Treo to keep my hands free for other essential tasks (likeholding the steering wheel when I'm driving - but that's another sermon). As I've been using Call Log for the pastcouple of weeks, I've been typing notes right into the call log record as I talk. Which means I no longer have to besitting in front of my PC or rely on pen and paper to capture notes from the call. It's a great mobility solution.These notes are easily accessed in Outlook once I've synced where they can then be transferred to a mind map,e-mail message, or other digital file. And, as an added bonus, Call Log lets me use the Treo's 5-way Navigator in away the standard Call Log application built into the Treo doesn't.Call Log costs $29.95. A trial isavailable.Permalink | Email this | Linking Blogs | Comments Google honors MLK with today's logo [New Window]
"I have a dream... " I remember hearing Dr.King's most famous speech (I was a lad of 9 at the time) and the uproar, furor, passion, and celebration it usheredinto our lives. Over the years, I've listened to this speech many times and it has never failed to touch me deeply andgive me hope. These closing lines give me chills... always have... always will."And when this happens,when we let freedom ring, when we let it ring from every tenement and every hamlet, from every state and every city, wewill be able to speed up that day when all of God's children, black men and white men, Jews and Gentiles, Protestantsand Catholics, will be able to join hands and sing in the words of the old spiritual, "Free at last, free at last.Thank God Almighty, we are free at last.""Permalink | Email this | Linking Blogs | Comments Time Bandits: a great GTD article in American Way [New Window]
I just received a note from a business acquaintance that he'd just finished reading "Time Bandits", an article inAmerican Airlines' in-flight magazine about David Allen's Getting Things Doneproductivity system. He told me it made the article even more enjoyable because he knew a certain blogger who wasquoted throughout the piece. Chris Tucker, the author, interviewed me months ago. Frankly, I had all but forgottenabout our chat until I got the e-mail this evening.It's a fine article (no, not just because I'm in it).Tucker does a terrific job documenting how GTD has impacted individuals and companies large and small. My bloggingbuddy Michael Hyatt, CEO of Thomas Nelson Publishers, is alsoquoted in the article.It's been quite a month (and it's barely half over).Permalink | Email this | Linking Blogs | Comments No more personalized menus - hooray! [New Window]
Jensen Harris reports some very welcome news on his Officeblog - Personalized Menus will no longer be switched on by default when Office is installed. Since the introduction ofthis feature, the default preference in Microsoft Office has been to have the Personalized Menus feature - one ofMicrosoft's most ill-advised attempts to be helpful IMO - turned on by default. Personalized Menus truncateevery menu in Office applications in an attempt to make navigating easier. The theory was that, over time, Office wouldlearn which menu items you used most often and would present those options in the shortened menus. The very first thingI do when I do a fresh install of Office is turn this feature off.Now you might be thinking this isn't sucha big deal - what with the new Ribbon UI in Office 12. Yes and no. Many of the Office applications (the main OutlookUI, Visio, Publisher, Project) are not getting the new interface in Office 12. There are a number of reasons why andJensen has written some great posts explaining the reasoning behind these decisions. The long and short of it is this -beginning with the next build, Personalized Menus will only show up if you want them to.Permalink | Email this | Linking Blogs | Comments Engadget picks the best of CES [New Window]
Engadget was all ove the Consumer Electronics Show this past week and they've justput together a list of the best gadgets they saw. From personal video to big screens, they have assembled a selectionof objects guaranteed to inspire some lust. Go check it out.Permalink | Email this | Linking Blogs | Comments Gmail update: a Delete button [New Window]
At first I thought I was seeing double.Long frustrated by Google's refusal to add a delete button to Gmail, I had installed the compiled Greasemonkey script Iposted about a while back and it's been working fine. A short while ago, I clicked on the button and... nothinghappened. I looked to see what might have gone amiss and saw not one but two Delete buttons. I disabled the add-onbutton in the extensions dialog, restarted Firefox and...SHAZAM!There was a shiny new Deletebutton. This hasn't been officially announced yet (no red text at the top of the page) but the Digg kids are all overit. If you use Gmail, expect to see it show up sometime soon.Permalink | Email this | Linking Blogs | Comments SpeedFiler 1.0 release candidate [New Window]
Claritude Software has just announced the firstRelease Candidate of their forthcoming SpeedFiler add-in for Microsoft Outlook. Installation instructions and downloadinformation is available here. I first discussed SpeedFilerin a December post. It is a lightweight*add-in for Outlook that makes the process of filing incoming and outbound e-mail messages a lot easier and is quiteadaptable to a number of productivity workflows including Getting Things Done andMichael Linenberger's Total Workday Control.SpeedFilerwill cost $19.95 for a single-user license when it is released. A discount is offered if you subscribe to the Claritudee-mail newsletter.* Lightweight, in this context, is a good thing. As many Outlook users know, some add-ins canconsume significant RAM and CPU resources and make Outlook... well, cranky. I've found SpeedFiler to be very wellbehaved in Outlook 2003.Permalink | Email this | Linking Blogs | Comments June Stats [New Window]
The Spreadsheet PagePointy Haired Dilbert
Wed, 1 Jul 2009 13:24:57 -0500 Interrupting Calculations [New Window]
Normally, Excel calculates your workbooks so fast you don’t notice. Sometimes, however, your workbook is so big that it takes Excel some time to calculate it. I’m guessing readers of this blog run into that situation more than the average Joe. While Excel is calculating, the status bar displays its progress. [...]
Wed, 1 Jul 2009 11:03:52 -0500 Quick PivotTables [New Window]
Sometime in the mid-1990s, a man named Lyle Lanley walked into Microsoft with an idea. Probably the greatest.... Oh, it's more of an Apple idea. "Just tell us the idea and we'll implement it", said Bill Gates.I'm on a quest to rid my life of wizards. The wizards that Microsoft seems [...]
Mon, 29 Jun 2009 10:41:56 -0500 Animate a greeting with Excel and VBA [New Window]
Inspired by Dick's XL birthday, the upcoming birthday of my fiancee, and tinkering with iphone app development, I thought of an animated greeting in the form of a stadium wave (known to some as an audience wave)...See http://www.tushar-mehta.com/publish_train/xl_vba_cases/greeting_wave.html
Sat, 27 Jun 2009 17:44:34 -0500 XL [New Window]
If I were Roman, that's how old I would be today.Today I will be spending my birthday processing inventory transactions and answering the phone - great fun. Tomorrow, however, we're having a little shindig in my honor with family, friends, and beer.
Fri, 26 Jun 2009 08:36:28 -0500 Euler Problem 83 [New Window]
Euler Problem 83 asks:NOTE: This problem is a significantly more challenging version of Problem 81.In the 5 by 5 matrix below, the minimal path sum from the top left to the bottom right, by moving left, right, up, and down, is indicated in red and is equal to 2297.131 673 234 103 18201 96 [...]
Tue, 23 Jun 2009 20:13:20 -0500 Euler Problem 81 [New Window]
Euler Problem 81 asks:In the 5 by 5 matrix below, the minimal path Min from the top left to the bottom right,by only moving to the right and down, is indicated in red and is equal to 2427.131 673 234 103 18201 96 [...]
Sat, 20 Jun 2009 10:46:20 -0500 Look Up [New Window]
The last control on the right click menu is Look Up. That opens the Research task pane.I'd be interested to know if anyone actually uses this. I accidentally click on it several times a week, but I don't actually use it. Anyone?
Fri, 19 Jun 2009 13:19:00 -0500 Tip: Updates for Microsoft Office Excel 2007 Help [New Window]
Hi allToday I talked about the search results from the Excel 2007 help (Offline) with a fellow MVP. Our results where different, my results were correct and useful but his results on the same search topic were useless. The reason why we have different search results is because that I have installed the help update [...]
Thu, 18 Jun 2009 14:32:16 -0500 Entering Dates Into Cells Part II [New Window]
I'm sure you all remember Entering Dates Into Cells from five years ago. It was a simpler time. Brittney Spears was telling us about her prerogative, the Olympics returned to Athens, and my date entering needs reflected the simplicity of the times. Now I need more.Here are some things I discovered about [...]
Thu, 18 Jun 2009 11:16:41 -0500 Excel Conditional Formatting [New Window]
Excel has a strong and often missed feature, called Conditional Formatting, which allows you to format a cell depending on a rule [it's value]. This can be very useful for worksheets like Product Quantities and Dates. Let me show you what I mean.Lets start off with a simple chart of products quantities in the warehouse.We’ll [...]
Fri, 27 Feb 2009 09:46:32 -0600 VBA Routine to Sort Sheets [New Window]
The following routine will sort the sheets in a workbook alphabetically. The flag Sort_Mode_Descending can be switched between descending and ascending as required. The routine is case INSENSITIVE.Sub Sort_Sheets()Dim Sort_Mode_Descending As BooleanDim No_of_Sheets As IntegerDim Outer_Loop As IntegerDim Inner_Loop As IntegerNo_of_Sheets = Sheets.Count'Change Flag As appropriateSort_Mode_Descending = FalseFor Outer_Loop = 1 To No_of_Sheets [...]
Wed, 5 Nov 2008 12:21:49 -0600 VBA Routine to return Column Letter of Cell [New Window]
The standard Excel Column Function returns the number rather than the letter of the column e.g:Column(E4) returns the number 5 rather than the letter EColumn(AD12) returns the number 30 rather than AD.The following function returns the letter rather than the number of the column. So in the above two examples we have the letters [...]
Mon, 3 Nov 2008 22:27:03 -0600 VBA: Extract Number From String [New Window]
The following function will extract the numerical portion from a string:Function Extract_Number_from_Text(Phrase As String) As DoubleDim Length_of_String As IntegerDim Current_Pos As IntegerDim Temp As StringLength_of_String = Len(Phrase)Temp = ""For Current_Pos = 1 To Length_of_StringIf (Mid(Phrase, Current_Pos, 1) = "-") Then Temp = Temp & Mid(Phrase, Current_Pos, 1)End IfIf (Mid(Phrase, Current_Pos, 1) = ".") Then [...]
Mon, 3 Nov 2008 10:16:49 -0600 Find the nth Word in a String [New Window]
The following function will find the nth word in a string:Function Find_nth_word(Phrase As String, n As Integer) As StringDim Current_Pos As LongDim Length_of_String As IntegerDim Current_Word_No As IntegerFind_nth_word = ""Current_Word_No = 1'Remove Leading SpacesPhrase = Trim(Phrase)Length_of_String = Len(Phrase)For Current_Pos = 1 To Length_of_String If (Current_Word_No = n) Then [...]
Fri, 31 Oct 2008 10:54:19 -0500 Adding and Naming Sheets to Excel Workbook [New Window]
The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesnt already exist to allow for the possibility of it being opened more than once a day.This code makes use of the Workbook Open Event and must [...]
Tue, 28 Oct 2008 15:44:44 -0500 Adding and Saving Workbooks [New Window]
The following code will prompt the user for the name of a workbook. It will then create and save a newworkbook with the same name.Private Sub CommandButton1_Click()Dim Workbook_Name As StringDim New_Workbook As WorkbookSet New_Workbook = NothingWorkbook_Name = InputBox(Prompt:="Workbook Name.", Title:="Enter the WorkBook Name :")Set New_Workbook = Workbooks.AddWith New_Workbook.Activate.SaveAs Workbook_NameEnd WithEnd SubTo download the .XLSM file [...]
Mon, 27 Oct 2008 13:16:57 -0500 VBA Function to Show Cell Formula [New Window]
The following UDF will return the formulae for a given cell reference:Function Show_Cell_Formulae(Cell As Range) As String Show_Cell_Formulae = "Cell " & Cell.Address & " has the formulae : ' " & Cell.Formula & " '"End FunctionSo if we have:Where the cell B7 has the formulae : =MAX(B5:G5) then we can type [...]
Fri, 24 Oct 2008 07:58:32 -0500 Matching Multiple Items [New Window]
Imagine that we have the following 2 lists each consisting of 3 columns of data:And we need to find those items that are in List 1 that are in List 2. However ALL 3 columns of data must match for items to be in both lists. One way would be to construct a comprehensive IF [...]
Tue, 21 Oct 2008 22:58:59 -0500 Convert Matrix to Vector [New Window]
This is the opposite tutorial of the Converting a Single Row Vector into a Matrix tutorial.The following function will take a range as a matrix and convert it to a single column vector:Option ExplicitFunction Create_Vector(Matrix_Range As Range) As VariantDim No_of_Cols As Integer, No_Of_Rows As IntegerDim i As IntegerDim j As IntegerDim CellNo_of_Cols = Matrix_Range.Columns.CountNo_Of_Rows = [...]
Tue, 21 Oct 2008 07:09:44 -0500
RSS Mix Reviews [Beta]
We pick the best professionally-written reviews, and summarise them on one page.
| 