November 29, 2005

A New Old Game - Mastermind

Just a quick post to upload a new version of my Mastermind game.

A bit of background - I met with the local Excel elite last weekend - very serious thinkers, drinkers and gamers :-) - and was sitting next to a friend who specialises in the userform type.

Anyway, I was sitting on the hotel bed later thinking I'd like to try this technique and as I didn't have any inspiration for a new game, I thought I'd give this a shot first.

Here's the download link.

I managed to catch the flu too so an early night for me. Nighty night.  

Posted by andrewe at 22:19

November 21, 2005

BeforeSave only with Save, not Save As

I see this question asked a few times and just as often answered...anyway I figure I'd include it here too.

Q. When you use a BeforeSave event, is there a way to stop it from firing when using Save As?

A. Yep, use a variation of this code. This example asks the user whether they really want to save, ie they have pushed Save, not Save As which you use to give a workbook a (new) filename.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI = False Then
        If MsgBox("Do you really want to save this Workbook? ", vbYesNo) = vbNo Then
            Cancel = True
            Exit Sub
        End If
    End If
End Sub

It's the SaveAsUI part that exits the sub. Keep in mind that the code also exits when working with unsaved workbooks, saving them would involve using a new filename (ie Save As).  
Posted by andrewe at 19:07

November 09, 2005

Another Approach To Formatting

Here's a question I was asked last week by Kelly (Hi Kelly!)

"I need to know how to format numbers so the decimal automatically comes up. For example, if I key 123456--I need 56 to automatically go into the decimal place."

And my reply (after cleaning the code up :-) )

Enter this into the appropiate sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Dim myDecimalPlace As Integer
    myDecimalPlace = Len(Target) - Len(Application.Substitute(Target, "-", ""))
    If myDecimalPlace > 0 Then
        Application.EnableEvents = False
        Target = Application.Substitute(Target, "-", "")
        Target = Target / Application.Power(10, myDecimalPlace)
        Target.NumberFormat = "0." & Application.Rept("0", myDecimalPlace)
        Application.EnableEvents = True
    End If
End Sub

I thought this was interesting because the "formatting" of the Target consists of 2 separate parts. The part to actually format, and the part to set the format and be deleted thereafter.

So intead of using something like a Custom Format or "Case 10 To 20" in code, the formatting doesn't depend on the value but on how you type it.

123- will result in 12.3 but 123-- will result in 1.23. This is far more simpler than trying to format each indivdual cell. (Ugh!)

Needless to say, other kinds of formatting are also possible.

Target.Font.Bold = True

Target.Interior.ColorIndex = 34

etc, etc, etc. Add them as you like - have fun ;-)  
Posted by andrewe at 02:49

November 01, 2005

Surfs Up

Last week I made a simple browser to use with Excel, half serious, half fun.

Here's how it works (the size is too big for a decent image file).

1. If there is a value in the active cell, it will be searched for automatically on start up. You can also copy to it directly from cells at any time by pushing "Copy Selection". Move the browser using the title bar down from it's default start position in order to view the cells.

Then you can do web searches by pushing the "Search" button.

2. You can also type into the text box at the top and do a web search this way (push the "Search" button as above). In addition, the text box can also be used to write multi-line messages (good for web mail etc)

To copy the text box contents to the browser, push "Copy Text" then right click the browser itself and select Paste where appropriate.

When you click the "Favorites" button, the URL is copied to the clipboard which you can then paste into the Favorites userform. Double click a Favorites link to open it. (The top URL is your default homepage)

One added feature. If you click the userform (on the right side or top is easiest), it drops down to the bottom of the screen, you may have to adjust the height position to preference where shown in the code. This is a) to gain selection access to the cells in Excel and b) a quick way to hide it if necessary :-)

Click it again and it pops back up. Do this when the coast is clear.

You can launch it or the Favorites userform from the Tools menu. Here's the link.  
Posted by andrewe at 21:29