Spreadsheet Page Blog Comments

Comment on: Another Excel Artist (by Dick Kusleika)

Dick Kusleika — 2009-01-06T13:46:25-07:00

I kind of hope what's on his screen isn't indicative of his 'art'. Or maybe I hope it is.

Comment on: Excel Drawing (by Harald Staff)

Harald Staff — 2009-01-02T06:59:59-07:00

Note that the workbook is named "Libro1" all through the process. He's not hitting Save, not once.How brave is that ?

Comment on: Excel Drawing (by Doug Jenkins)

Doug Jenkins — 2008-12-30T04:19:19-07:00

Scobby - I really only meant that you could do drawings from coordinates with the same vertical and horizontal scale, so that circles stay cirular and squares stay square, unlike in an XY graph. That's illustrated in my blog post I linked to.

Plotting drawings that will plot to an exact scale is a bit more complicated, well actually quite a lot more complicated, but I'm working on it.

I'm currently working on a routine that allows you to zoom in on any point on a drawing; i.e. it re-scales the drawing and trims the lines to fit within the selected boundaries. When that's done it should be fairly straightforward to add a routine that will adjust the scale so the drawing plots to an exact scale.

I'll be posting these things on my blog when they are in a presentable form, including sample files and the source code, so keep an eye on that if you are interested.

Comment on: Excel Drawing (by Scobby)

Scobby — 2008-12-29T10:38:12-07:00

Excel might not be any threat to Autocad, but you can do respectable scale drawings.

Can you explain this? How can you produce say, a 1"=300' scale drawing in Excel?

Comment on: Excel Drawing (by Doug Jenkins)

Doug Jenkins — 2008-12-27T01:03:30-07:00

Amazing the number of people commenting along the lines of WOW, YOU CAN DRAW IN EXCEL ????? !!!!!

Excel might not be any threat to Autocad, but you can do respectable scale drawings.

Blog post with download here:

http://newtonexcelbach.wordpress.com/2008/11/11/drawing-in-excel-7-creating-drawings-from-

I suspect that the drawing of a bat skull at the bottom of the blog post is more complex than the You-Tube drawing. It isn't animated though, sorry about that.

Comment on: Excel And AV Software (by ahmed)

ahmed — 2008-12-15T02:33:44-07:00

I've got the same problem with comments on a particular file. None of the workarounds work!! Tried with Office 2000 as well as Office 2007.

As soon as I delete a comment, excel hangs!

Comment on: A Multi-Site Google Search Box (by Wendy!)

Wendy! — 2008-12-07T11:28:08-07:00

I think this is very useful... if nothing else, it makes sure you don't get any answers from About.com!

Comment on: Function Dictionary Updated (by Jason Sanchez)

Jason Sanchez — 2008-12-06T19:29:07-07:00

Hello John,

Thanks for posting a link to his website. I looked over his dictionary and it was very nice.

Thanks

JS

Comment on: The End Of PC Magazine (by Wendy!)

Wendy! — 2008-12-06T19:12:47-07:00

John,
Fine job on this blog and the new improved The Spreadsheet Page!
I just spent a whole lot of time looking around. This is the weekend- reading about Excel and thereby increasing my skills helps my [employer's] corporation to become a bit more productive. So I got to wondering - is this the antidote to the J-Walk Blog? If I spend too much time at J-Walk, will this offset my naughty behavior?

Comment on: Music Theory And Excel (by Modeste)

Modeste — 2008-12-05T07:34:09-07:00

Oh , yes I dit it !!!
;o)))

http://www.dailydoseofexcel.com/archives/2007/02/06/musical-excel/#comment-22351

Comment on: Web Sites That Suck (by The.Q)

The.Q — 2008-12-05T03:54:27-07:00

Which sucks more? About.com without a doubt. It's just rubbish!

Comment on: Cleaning Up Data (by rob)

rob — 2008-12-04T23:04:33-07:00

I didn't try it, but it looked interesting for simple things. I'm glad to hear that a well-written affordable commercial product performs better. BTW, I didn't mean the submission to be a dig at PUP or anything. Just thought of you when I saw it.

Comment on: Music Theory And Excel (by John Walkenbach)

John Walkenbach — 2008-12-04T18:24:05-07:00

Mainly, I'd like to update the look of it.

Plus, I've learned some things since I did that. Maybe I can improve the efficiency or make it more "elegant."

I like the idea of adding tuning notes. I think I can do that. See this post at Daily Dose of Excel.

Comment on: Music Theory And Excel (by Modeste)

Modeste — 2008-12-04T17:17:08-07:00

@John

It really needs to up updated ???

Scales and Modes were changing ???

I suppose only you are kidding ...

maybe adding some sounds for tuning will be plaisant!!!

;o)))

Comment on: Web Sites That Suck (by Christian)

Christian — 2008-12-04T13:14:45-07:00

I agree... when i saw that post i expected something more than a few text boxes and a red line...

Comment on: Web Sites That Suck (by John Walkenbach)

John Walkenbach — 2008-12-04T12:21:07-07:00

Thanks, Christian. I didn't bother to scroll down to see how to solve that "problem." I downloaded that template and it confirmed my suspicion. It sucks. Hardly worth 10 pages of explanation at about.com.

Comment on: Web Sites That Suck (by Christian)

Christian — 2008-12-04T11:40:38-07:00

Hi, i passed through the same path too - so I completely agree with you. But, there's a tiny button link below the section 'To download this template manually without solving this problem:', below the warning. Here is the direct download link:

http://office.microsoft.com/download/file.aspx?AssetID=TC010162661033&Application=XL&Result=2&Version=8&CTT=71

Regards,
C.
from Spain

Comment on: Web Sites That Suck (by Jon Peltier)

Jon Peltier — 2008-12-04T10:51:40-07:00

About.com is teh suck. I have found material from my web site copied there, without permission or attribution, and I have tried repeatedly to have it removed. I don't even know whom I should contact, because their web site makes useful information impossible to find.

Comment on: Cleaning Up Data (by Debra Dalgleish)

Debra Dalgleish — 2008-12-03T15:10:09-07:00

I tried it too, using their sample data. It did some things well, but I'll stick with PUP too.

I'm sure they're nice people but I'm not going to upload any personal or business data to a non-secured site, run by strangers. There aren't any privacy assurances that I can see.

Comment on: Excel And AV Software (by Arpit)

Arpit — 2008-11-28T00:17:13-07:00

I have faced similar problem in past. I discovered that Google Desktop plugin was causing this problem.
I just unchecked Google Desktop Add-in under COM AddIns for Excel 2007) and problem was solved.
Try it and let me know your experience.

Comment on: Extraneous Plus Sign Removal (by Jazzer)

Jazzer — 2008-11-27T00:34:18-07:00

Seems to me, that ** is the same than e (or E or 10^).

- Asser

Comment on: Extraneous Plus Sign Removal (by Volt)

Volt — 2008-11-24T14:28:00-07:00

I don't think it's weird. It's doing what you'd expect.

The use of the character "+" is ambiguous in modern numeric annotation. It could be a sign character indicating positive "+2" or it could mean addition. When you enter +2 it thinks 2, signed positive and removes the extraneous "+".

Whenever you enter =++++2 it thinks “nothing + nothing + nothing + (+2)” and removes the extraneous sign. Each time it is evaluated the formula has one less + and the "+" contiguous to the numeral is considered an extraneous positive sign.
e.g. =---+2 becomes =---2 and then stops changing.

try =--2--++++5

What I think is weird is that Excel tolerates null operands in arithmetic except in the last operand and it does tolerate null operands for the + and - operators, but not others (e.g. *, multiplication).

Why this?

=5**2 evaluates to 500

Comment on: Extraneous Plus Sign Removal (by rcriii)

rcriii — 2008-11-24T13:25:12-07:00

Next, activate the cell and press F2 to enter EDIT mode. Don't make any changes; just press Enter. The formula now appears as:

I'm late in the day with this comment, but the formula is altered (one plus is deleted) when you hit enter or the arrow key to accept the formula the each time, not when you hit f2 to edit it.

Comment on: Excel And AV Software (by Volt)

Volt — 2008-11-24T10:00:47-07:00

This may have to do with the Garbage Collection algorithms in .NET.

What versions of Framework are you running?

The difference you perceive between XP and Vista may be occurring because Vista came installed with a later release of Framework.

Comment on: Excel And AV Software (by HelpExcel.com)

HelpExcel.com — 2008-11-24T08:31:26-07:00

Excel runs significantly slower in Vista.

I run a simple 1 second VBA loop with a before & after timer to guage performance & XP performs more cycles always, hands down.

Comment on: Microsoft Has A Download Store (by Volt)

Volt — 2008-11-21T01:07:45-07:00

or pay $59.95 directly from Microsoft if you are a student, teacher or have a kid who is. They're pretty lenient about who qualifies even kindergarteners (K-12).
http://www.microsoft.com/student/discounts/theultimatesteal-us/default.aspx

Comment on: The End Of PC Magazine (by Z)

2008-11-20T12:40:35-07:00

I'm Jewish and a Sabbath observer, and I don't use computers, TV, etc., from Friday night until Saturday night. During that time I like to read magazines instead, and my two favorites are PC Magazine and The Week. During the rest of the week, I do most of my reading online, but it's been nice having something to read on Saturday.

Oh well, so long PC Mag.

Comment on: Excel And AV Software (by Luke)

Luke — 2008-11-20T09:16:46-07:00

Note that anti-virus software and google desktop use the hard disk quite extensively. Given that hard disk access is two orders of magnitude slower (about 100x) than using RAM, you will see significant hits to performance if any software uses it. Typically, with respect to windows, virtual memory--and how windows uses it--is the culprit. If you are out of RAM, for example when you have a spreadsheet that is quite full of data, some of that will be transferred to Virtual Memory. Then updating and deleting data suddently becomes much slower. I'd suggest using Process Explorer to dig a bit deeper.

Comment on: Excel And AV Software (by Charles Williams)

Charles Williams — 2008-11-20T01:18:44-07:00

On my system the problem is caused by the Google Office COM addins.
If you uninstall these (Office Button-->Excel Options-->Addins-->Manage-->Com Addins-->Deselect or remove Google Desktop Addins) then the problem goes away, but you can still use Google Desktop Search if you want to.

I suspect this Excel 2007 slowdown is linked to the VBA slowdown in Excel 2007: see
http://www.decisionmodels.com/VersionCompare.htm

Comment on: Excel And AV Software (by Rembo)

Rembo — 2008-11-20T00:49:11-07:00

@chip
I once experienced a similar problem where a system would lock up frequently when connected to a network. I downloaded Process Explorer (search MS Download) and found that it was lsass.exe (=Local Security Authentication Server) that was causing the problem. Deleting the file fixed it. Windows automatically restores it on startup.

Found this as well:
http://www.mrexcel.com/forum/showthread.php?t=75988

Comment on: Excel And AV Software (by chip)

chip — 2008-11-19T19:55:20-07:00

Doug, here's an oddball one that may fit your issue since you've eliminated a lot of stuff already. I am on wireless at home, and when I am connected, certain operations take forever in Excel. I'm still on XL2003, however, so not your exact situation. When I disable wireless, everything works normally.

On my wireless at work, I do not have this problem with Excel. It must be something with my router--though why my internet connection has anything to do with my actions within Excel, I have no idea. Switching from Excel to a browser (say) or vice versa also take a long time--like 10-15 seconds. Excel seems to be the only program I have these issues with.

Comment on: Excel And AV Software (by Alex J)

Alex J — 2008-11-19T19:08:39-07:00

On the subject of Excel slowing down, I remember the bad old days of xl97 and xl2000 when an application crash would leave hundreds of little graphics files in the TEMP directory (can't remember the file extension). Excel was limited to 1 Meg of disk mapped from TEMP for creating new graphics images when it opened again as well as other operations, and so multiple crashes would choke performance until TEMP was cleaned out again. (I remember one piece of vba code that took all night to run when it should have taken seconds - that was really spooky until I figured it out - with the help of the NGs).

Is it possible that AV or Google desktop are stealing xl2007 resources in a similar manner?

Comment on: Excel And AV Software (by Doug Jenkins)

Doug Jenkins — 2008-11-19T15:28:23-07:00

Rembo - an archiving system really isn't an alternative to a fast search program for my purposes. Fortunately the Windows indexing system included in Vista doesn't have the same problems with XL2007 as Google Desktop does, so I have got that running and now get the searching without the lock-ups when clearing big ranges.

Comment on: Excel And AV Software (by Rembo)

Rembo — 2008-11-19T06:14:39-07:00

I've seen bizar delays when people opened up an excel workbook from their email which makes sense if you know that a virus scanner will scan the attachment first.

Indexing, any indexing, ultimately delays everything. Indexing results in ever growing databases. If you don't frequently search your harddisk you don't need it. In fact, you don't need it at all. If you do use searching a lot you need a good archiving system.

The first thing I do when I install a Vista computer is set UAC to quite mode (or disable it), remove all indexing from the harddisk and uninstall McAfee/Symantec and Google and Yahoo bars (usually installed by default on new laptops). For normal workstations I also disable the build in firewall (we have one integrated in our router) and disable spyware detection as well.
I only install antivirus (Clamwin) on laptops, the workstations are protected by the virus scanner on our mail server.

Rembo

Comment on: Excel And AV Software (by Doug Jenkins)

Doug Jenkins — 2008-11-19T04:17:20-07:00

Success!

Previously I switched off Google Desktop (which made no difference). This time I uninstalled it.

Problem gone

Comment on: Excel And AV Software (by Lance)

Lance — 2008-11-19T04:00:44-07:00

I discussed this with a colleague, Michael Quayle, and he writes:

"I've never seen this myself.

"I wonder if it could be related to the new features being added to CPU's to avoid buffer overflow attacks? An extra bit is used for memory addressing (I think) to indicate whether the code is executable or not. This chip feature a) is only supported on some CPU's (AMD frequently) and b) was only supported by Windows from XP sp 2 onwards.

"It sounds something that may be monitored by antivirus software and could come into play with very large cell operations?

"Alternatively, the AV software might be trying to monitor the clipboard or active-X or VB operations?"

Comment on: Excel And AV Software (by KeithC)

KeithC — 2008-11-19T00:31:03-07:00

I have XL2007 SP1 on Vista SP1 and am running the latest McAfee (on an AMD Triple Core with 3GB of ram).

I have tried to duplicate Doug's issue but my results mirrored those of J-Walk (instantaneous response).

I did notice huge delays in XL2003 SP3 using ZoneAlarm months ago but chalked that up to crappy updates from CheckPoint (everything was so slooooow).

Comment on: Excel And AV Software (by Doug Jenkins)

Doug Jenkins — 2008-11-18T21:23:44-07:00

I have tried:
Switching off background error checking
Turning recalc to manual
Saving as xlsx

None of those make any noticeable difference.

What does work is if I select the range, then save, then delete.
Then deleting and return of control are near instantaneous, and saving takes the normal time.

If I save, then select and delete I still get the lockup.

Comment on: Excel And AV Software (by John Walkenbach)

John Walkenbach — 2008-11-18T20:30:24-07:00

Doug, I tried that and the two values in A1:N1048576 were deleted instantly.

Comment on: Excel And AV Software (by Doug Jenkins)

Doug Jenkins — 2008-11-18T19:41:31-07:00

At least on my machines, this is an Excel 2007 problem, not connected with Vista or McAfee (or any other virus software).

On a dual core machine with Vista, XL2007, and no virus software installed I opened a new file, saved as xlsb, closed and re-opened, then entered a 1 in cell A1 and cell N1048576 (i.e. the last row). I selected A1:N1048576 and pressed delete. 3 1/2 minutes later it had finished clearing the two 1's, and returned control. All this time the task monitor showed Excel as using 50-60% of CPU.

On a single core machine with XP, XL2007 and also no virus programme of any sort behaviour was much the same, except that CPU usage was running at about 99%, and it took even longer.

Shuuting down all obvious extraneous software such as Google Desktop made no obvious difference.

I'd be very interested in the experience of others with the same operation; i.e. clear A1:N1048576 with a blank worksheet, other than a 1 in the first and last cells.

Comment on: Excel And AV Software (by ScoobyDoo)

ScoobyDoo — 2008-11-18T19:10:12-07:00

I absolutely hate hate hate McAfee anti-virus software. For one thing, they at McAfee seem to think the user wants to be kept updated on all of the virus protecting actions of the software. When I had it installed, windows would keep on popping up all the time. Shut the hell up and do your job software, I don't want to be notified of every little thing you're doing (why wouldn't they have an option to disable this "feature")?

Comment on: Excel And AV Software (by Doug Jenkins)

Doug Jenkins — 2008-11-18T18:31:37-07:00

I am using XL2007 and Vista. I frequently have long delays when clearing large ranges, both from the keyboard and from macros. For very large ranges this can extend to minutes or until I get fed up and hit ctrl-shift-esc. XL2000 on this machine has no such problems at all.

I will experiment on an XP machine with XL2007 and after removing any virus checkers, and report back.

Comment on: Excel 2007 Help Updated (by Ron de Bruin)

Ron de Bruin — 2008-11-18T14:58:31-07:00

Hi all

Today it automatic download the new Help files with Windows update for :

Word
PowerPoint
Excel
Outlook

Comment on: Excel And AV Software (by sc)

sc — 2008-11-18T13:45:53-07:00

I use AVG. It is free and I have never had any problems with it slowing my computer down. I like better than McAfee and Norton.

Comment on: AC/DC Video In Excel (by John Walkenbach)

John Walkenbach — 2008-11-15T07:00:42-07:00

The paper airplane simulator was probably a workbook with a Flash game embedded. I've seen several of those, and I don't understand the point.

Along the same lines, people often send me a screen shot to illustrate a problem. A significant number of them put the image in a Word document, rather than just sending the image itself.

Comment on: AC/DC Video In Excel (by Mumtaz)

Mumtaz — 2008-11-15T06:36:05-07:00

Reminds me of that paper-airplane flight simulator in excel that created quite an excitement sometime ago. But that was a binary object embedded in a spreadsheet. Right?

Comment on: Annoying Date Entry Behavior (by chip)

chip — 2008-11-12T09:36:20-07:00

aphrodaia, simplest solution would be to put columns with =MONTH and =DAY formulas next to your birth dates and sort based on those. Or you could "standardize" your dates and always enter MM/DD/2000, say, assuming you do not know the year.

Comment on: Small Basic (by Ross)

Ross — 2008-11-12T03:38:41-07:00

I think there are better options out there, if you want to teach your kid programing, I can think of the name now but I think theres a thing from MIT, which is very good.
This just looks like a little version of VB to me, you might aswell just get you kid to us VB express! - There's nothing in small basic that I can see would help a kid learn or get into programing?!

Comment on: Annoying Date Entry Behavior (by aphrodaia)

aphrodaia — 2008-11-11T16:38:46-07:00

what stinks is if you have a birthday list, for instance, with just month and day and NO year - if the list is maintained over time (years pass, for instance), you cannot sort on the birthday because even though year does not SHOW if you format it to be dd-mm, the year is counted in the sort so anything you input one year, comes before the next, etc.

Comment on: Small Basic (by HelpExcel.com)

HelpExcel.com — 2008-11-10T15:10:29-07:00

I learned on a Commodore 64 & TI-99

Comment on: Buzzword Bingo Card Generator Posted (by John Johnson)

John Johnson — 2008-11-07T16:16:32-07:00

Hi John, Great site. Thanks. I too remember VisiCalc. I noted you said had VisiCalc on 5.25" floppy disk(s) and could not read then as you did not have a 5.25" drive. The format on 5.25" floppy disks used on the Apple II are not compatible with the format used on PC's 5.25" floppy disks. You would need to to use an Apple II, find and run a binary transfer program to move the excutable from the Apple II to a PC or other machine. Then ... [ I digress ] Regards. John Johnson

Comment on: Displaying An Annual Calendar (by Gemini)

Gemini — 2008-11-06T04:31:56-07:00

Hi, I don't quite understand the array formula and other stuff (why the days have the Transpose (0,1,...)??)
Anyway, I just wnated to have the same calendar with Monday as the first day of the week. How to tweak yours to achieve that?

Comment on: Excel In A Browser (by Eddie)

Eddie — 2008-11-05T14:55:29-07:00

I wonder if VBA/.NET will be available in the online version. If so, that could be the major dealbreaker.

Comment on: Excel In A Browser (by chip)

chip — 2008-10-30T15:46:12-07:00

Sam, if it's like Google docs, you'd be able to set up groups of people that you allow to view and edit the doc, just like your local network. But at least you know who controls your local network and its security. I haven't used Google Docs (though a client of mine uses it a lot) but I worry about the day that they decide to mine the docs for--well, who knows what research reason there may be? The Google Books is a "research project" or something. Don't be evil, indeed.

Comment on: Excel In A Browser (by sam)

sam — 2008-10-29T19:21:04-07:00

I am resonalby close to the business world.. and I am not excited about it at all...
If I want to share some information I would put it on a network folder and ask IT to give the right people the right accesses.
I cant think of a business related spreadsheet that I would want to share with the www

Comment on: Excel In A Browser (by simon)

simon — 2008-10-29T18:31:53-07:00

John I don't think its for business
We've had Excel in a browser for ever with OWC etc. And everyone has stayed well away.
Its Web 2.0 - build it, and worry about getting paying customers later.

Comment on: Excel In A Browser (by Harald Staff)

Harald Staff — 2008-10-29T13:07:43-07:00

Spreadsheets in a web browser?
I want it in iTunes! Book1.mp3

Comment on: AC/DC Video In Excel (by Ross)

Ross — 2008-10-27T06:34:04-07:00

John,
There are a few free wear apps that do it, have a look on the code project site, I have seen a few on there "image to text" should get you started

Comment on: AC/DC Video In Excel (by John Nevill)

John Nevill — 2008-10-26T12:43:53-07:00

Very impressive.

Blayne, the code is fairly simple. It's just starting the wav file then looping through the cells that you found (column 17 starting at row 100 to 639) and assigning that value to the cell that is displaying the video.

Does anyone know what software they used to perform the video to text conversion?

Comment on: Color Contrast Functions Posted (by BrianR)

BrianR — 2008-10-26T11:30:57-07:00

Out of laziness, I wrote the macro below to change the font colour of the selected cell(s) to either black or white, depending on which was the most visible. Of course, this doen't give the maximum contrast, but at least the result is reasonably legible.

Sub Visible_Font_Colour()
' For each cell in a selection, calculate its luminance and change its font colour (using Black and White only) so that it's visible.
' Brian Redmond

Dim xCell As Range
Dim xInterior As Long
Dim xLuminance As Long
Dim xresponse As Long

For Each xCell In Selection
    
    xInterior = xCell.Interior.Color
    xLuminance = Int(xInterior / 16 ^ 4) * 0.0721 _
                    + Int((xInterior - Int(xInterior / 16 ^ 4) * 16 ^ 4) / 16 ^ 2) * 0.7154 _
                    + (xInterior Mod 16 ^ 2) * 0.2125
    xCell.Font.ColorIndex = IIf(xLuminance / 255 < 0.64, 2, 1)

Next

End Sub

Regards,
Brian.

Comment on: AC/DC Video In Excel (by Blayne)

Blayne — 2008-10-25T21:06:19-07:00

Why wouldn't it play the entire new song?

How would you insert a new song from say a different album or artist?

I found the code (down to row 639), but how does it work?

Can it be 'colorized'?

Cool stuff anyway.

Whoops, just got this message ...

Comment on: Excel 2007 VBA Challenge (by Charley Kyd)

Charley Kyd — 2008-10-25T14:40:12-07:00

Thing 2...

One significant implementation problem is that the TintAndShade value is hard-coded to the object when we assign a theme color from the palette. If a different theme uses a different TintAndShade value for the same position in the palette, the object still uses the originally assigned TintAndShade value.

To illustrate, begin with the Foundry theme, which has a light color in the 10th position. Assign the colors for rows 2, 3, and 4 of column 10 (the right-most column). According to the UI, these colors are 10%, 25%, and 50% darker. Their Luminance values vary from 187 to 104.

Now switch to the Verve theme, which has a dark color in the 10th position. Rows 2-4, column 10, of this theme are 80%, 60%, and 40% lighter. But our colors, set with the Foundry theme, are still 10%, 25%, and 50% darker. That is they run from very dark (Lum: 71) to nearly black (Lum: 39).

Charley Kyd

Comment on: Excel 2007 VBA Challenge (by Charley Kyd)

Charley Kyd — 2008-10-25T14:39:26-07:00

Two things...

Thing 1:

It looks like there's a bug with regard to the TintAndShade property of a shape. Specifically, this macro...

Sub GetTintAndShade()
Dim shp As Shape
Set shp = ActiveSheet.Shapes(1)
ActiveCell = shp.Fill.BackColor.TintAndShade
ActiveCell.Offset(0, 1) = shp.Fill.ForeColor.TintAndShade
End Sub

...always returns zeros for any color and any theme I assign. Of course, your results may vary.

Thing 2...continued on next comment

Charley Kyd

Comment on: Using The Enter Key In The Enhanced Data Form (by Harald Staff)

Harald Staff — 2008-10-25T07:21:05-07:00

I see, that complicates it. Oh well ..

Comment on: Using The Enter Key In The Enhanced Data Form (by John Walkenbach)

John Walkenbach — 2008-10-23T12:53:14-07:00

After giving it some thought, Harald's idea won't work for the Enhanced Data Form. The TextBox and ComboBox controls are generated on the fly. Using the KeyDown even would require code that writes an event procedure for each control. It could be done by using a class module, but that would require quite a bit of changes.

Comment on: Using The Enter Key In The Enhanced Data Form (by John Walkenbach)

John Walkenbach — 2008-10-23T10:22:11-07:00

Good point, Harald. I forgot about that KeyDown event. That would certainly be better than SendKeys.

I'll re-visit my code and see if I can get it work.

Comment on: Using The Enter Key In The Enhanced Data Form (by Harald Staff)

Harald Staff — 2008-10-23T10:00:46-07:00

John;
I wonder if you could simplify by replacing the ascii in KeyDown (I'm not knowing this specific form or sourecode):

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then KeyCode = 9
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then KeyCode = 9
End Sub

Best wishes Harald

Comment on: Hyperlink Misinformation (by John Walkenbach)

John Walkenbach — 2008-10-23T05:20:19-07:00

Mystery solved. Thanks Alastair.

Comment on: Hyperlink Misinformation (by sam)

sam — 2008-10-23T05:14:38-07:00

Opps.... Paste (from Clipboard....) - Choose Match Destination formating from the Smart Tag

Comment on: Hyperlink Misinformation (by sam)

sam — 2008-10-23T05:08:41-07:00

How about Ctrl+C twice (copy to clipboard), Delete, Paste

Comment on: Hyperlink Misinformation (by Alastair)

Alastair — 2008-10-23T03:39:07-07:00

We have a culprit - it's no.6 in a google search for "Excel remove hyperlinks"

http://www.mrexcel.com/archive/Hyperlinks/1652.html

Comment on: Hyperlink Misinformation (by John Walkenbach)

John Walkenbach — 2008-10-22T21:13:41-07:00

Good one, Ashish! As long as the hyperlinks have the same formatting that will work perfectly.

I think Excel 2002 was the first version that lets you select cells by format.

Comment on: Hyperlink Misinformation (by Ashish Mathur)

Ashish Mathur — 2008-10-22T20:43:11-07:00

Hi,

There is indeed a way to remove all hyperlinks at once. The following is the procedure:

1. Type 1 in any cell of the worksheet and copy this cell
2. Now while you are in any blank cell, do Ctrl+F and click on the options box
3. Now click on the down arrow on the Format tab and select "Chiise Format from cell"
4. Select the cell which carries the hyperlink
5. Now Do a Ctrl+A
6. Close the Find box. You will notice that all hyperlinked cells will be highlighted
7. Now simply do Edit > Paste Special > Multiply
8. This will remove all the hyperlinks
7.

Comment on: Excel-Related Problem At Barclays (by bob smith)

bob smith — 2008-10-20T10:55:02-07:00

agreed on the blame. The 1st year associated did have an MBA...

Comment on: Office Offline (by David Salaguinto)

David Salaguinto — 2008-10-17T14:34:39-07:00

Thanks for linking to my comic.

Comment on: Excel-Related Problem At Barclays (by Mr B)

Mr B — 2008-10-16T16:34:05-07:00

I was more disturbed by the firm laying blame on a first year law associate. Surely a spreadsheet of such importance undergoing conversion to PDF would be proofed again post conversion. working in a corporation and knowing the degrees of bureaucracy with document proofing by legal teams and sign-off I feel a little sorry for the fall guy. I wonder if he is still a first year law associate?

Comment on: Progress Bars: The Movie (by Doug Glancy)

Doug Glancy — 2008-10-16T14:10:07-07:00

I installed Adobe InDesign today on somebody's computer. I've loaded Adobe products dozens of times now and am always dismayed by their stressful "2 steps forward, 1 step back" progress bars. They literally jerk you around - "you're almost done, or maybe not" for several minutes until they display the blessed "quit" button.

Comment on: Excel-Related Problem At Barclays (by chip)

chip — 2008-10-16T13:11:06-07:00

Sounds like a filtering issue to me. They filtered for "Yes" in the "Buy" field but totalled the entire column instead of just the filtered items.

I was in a client's office a couple of weeks ago and overheard someone asking a passing IT person "if I total this column in Excel, will it total everything, or just the unfiltered things?" Ouch, maybe you should test a bit if you're unsure. I was there for a totally unrelated meeting, but I listened to hear what the response was. Fortunately it was the correct one.

Comment on: Blog Comment Enhancements (by chip)

chip — 2008-10-15T17:55:30-07:00

Thanks for the tipoff. Google seems to have improved it a lot since last time I tried it. It does have some behavior I'm not fond of but it's getting my feeds now. I can probably adjust some settings, just haven't taken the time to figure out how.

I found that some of the less frequently updated blogs I follow had had recent updates that Bloglines did not catch either, so it was not just this one.

Comment on: Blog Comment Enhancements (by John Walkenbach)

John Walkenbach — 2008-10-13T09:19:38-07:00

I was a Bloglines fan for a long time. But during a recent Bloglines outage, I tried Google Reader. Now I'm a convert. It is SO much better than Bloglines.

Comment on: Blog Comment Enhancements (by chip)

chip — 2008-10-13T08:03:00-07:00

Are others having issues with this site's rss feeds? I know Bloglines has been balky lately, but yours seems to be the only blog that's stopped feeding to it (of those I subscribe to.) I deleted and resubscribed, and it gave me the "last 10 posts" it usually does--but the most recent was from 9/24.

I just got a load of comments in the feed after a couple of weeks of nothing, and they mostly concerned posts I had not seen, which is how I even noticed it.

I just subscribed using the Live Bookmarks and it seems to work perfectly. Hmmm...may have to try the google reader again.

Comment on: Progress Bars: The Movie (by Jon Peltier)

Jon Peltier — 2008-10-12T11:56:30-07:00

Nice find.

Comment on: Annoying Date Entry Behavior (by JP)

JP — 2008-10-10T13:56:21-07:00

John,

There's one workaround: Open a blank worksheet and press Ctrl-1, then format the cells with the date format you want. Save it as type Template, filename "book.xlt" in your XLSTART folder. This will be the default workbook when you start Excel.

Now whenever you start Excel or go to File>New and enter a date, it shows up in the format you want.

Not a perfect solution, but one way to simulate the behavior you want.

--JP

Comment on: Annoying Date Entry Behavior (by Ron de Bruin)

Ron de Bruin — 2008-10-10T13:04:42-07:00

Maybe this Add-in will help
http://www.rondebruin.nl/qde.htm

Comment on: Annoying Date Entry Behavior (by John Walkenbach)

John Walkenbach — 2008-10-07T05:38:58-07:00

The problem (to me) is not that the day appears before the month. It's that the year is not displayed.

Comment on: Annoying Date Entry Behavior (by Tim)

Tim — 2008-10-06T23:29:10-07:00

Surely just a small form of retribution from the rest of the world for the usual assumption from that side of the pond that the whole world works the same way as you do .... e.g. feet instead of metres, pounds instead of kg, miles instead of km, gallons instead of litres, color instead of colour, etc, etc, etc.

Most of the world would class Excel's behaviour (note the spelling) here as a 'useful feature'.

Have a nice day.

Comment on: Annoying Date Entry Behavior (by Les. Ferguson)

Les. Ferguson — 2008-10-06T23:15:58-07:00

I never noticed this problem.
Of course since I hail from the U.K. I wouldn't!
The other half of the world probably thinks this is a really cool feature
Shalom

Comment on: Annoying Date Entry Behavior (by Debra Dalgleish)

Debra Dalgleish — 2008-10-06T19:06:38-07:00

Thanks, that bugs me too, especially since I never use that dd-mmm format.

Comment on: Still An MVP (by Ross)

Ross — 2008-10-06T14:07:54-07:00

Cool, so your just a kid then really!
Thanks John,

Comment on: Poor Guy (by Rick Teale)

Rick Teale — 2008-10-05T10:49:45-07:00

I have that book on the shelf in my office and my name appears in the acknowledgments. While I have ungraded to newer versions of Excel, much of what is covered in the book is still pertinent.

Now that I am approaching the completion of six decades, I wonder . . . just what age is considered an old man?

Comment on: Still An MVP (by John Walkenbach)

John Walkenbach — 2008-10-05T06:42:36-07:00

I'm pretty sure it's Bob Umlas. I think this is his 14th year.

Comment on: Still An MVP (by Ross)

Ross — 2008-10-05T04:08:44-07:00

Well done John,
Heres a question, who is the longest serving Excel MVP?

Comment on: Buzzword Bingo Card Generator Posted (by Alex J)

Alex J — 2008-10-04T09:55:39-07:00

Is that the one with a ribbon?

Comment on: Poor Guy (by Jon Peltier)

Jon Peltier — 2008-10-04T09:35:00-07:00

I thought maybe the poor guy was the author of the book

Comment on: Buzzword Bingo Card Generator Posted (by John Walkenbach)

John Walkenbach — 2008-10-03T06:35:01-07:00

Those features are slated for Version 2.0.

Comment on: Buzzword Bingo Card Generator Posted (by Alex J)

Alex J — 2008-10-03T06:10:04-07:00

Shouldn't this be automated with click events to fill in the form and auto email winning cards to the other players? Could be a more popular online game than DOOM.

Bring your laptop/blackberry to the meeting and play!

Comment on: Buzzword Bingo Card Generator Posted (by Buck star)

Buck star — 2008-10-02T17:52:21-07:00

Hi John,
I have been compiling a corporate cliche and buzzword list over my years working at leading Australian Telco. I have added these to the Bingo format and added a Macro to reset range names by button click. I currently have 421 buzzwords and cliches. Thank you for the inspiration behind this outlet. It will be well used in all future thinktanks, brainstorms and idea heamorage meetings.

Comment on: Old Dog Learns New Trick (by John Walkenbach)

John Walkenbach — 2008-10-02T17:45:06-07:00

Good to know, JP. But I still don't see any reason to use that setting.

Comment on: Old Dog Learns New Trick (by JP)

JP — 2008-10-02T17:08:49-07:00

If you want to stop Excel from changing the active cell after editing a cell, press Ctrl-Enter. You'll finish the edit and keep the current cell active. Thanks to Bob Umlas for that tip.

Comment on: More Fan Mail (by Michael Alexander)

Michael Alexander — 2008-10-01T10:40:19-07:00

You get all the good readers. Here's a sample of the emails I get.

"This is the third email I'm sending you. I need to get my Excel issue resolved ASAP. Please let me know if you got my previous emails, and where you stand on my Excel question."

Here is what I responded.

"Sorry I haven't responded to your request to look at your spreadsheet. I normally check email at dick@dicks-clicks.com. Write me there."