Crossword Builder Version 2

Posted on by Dick Kusleika

I couldn’t get my crossword template to work just right, so I’m taking a new tack. I used this Yahoo! crossword to test, so I don’t want you to think I filled this in on my own. I tried that once and it’s really hard.

I went with textboxes overlaying cells to keep the numbers and letters separate. I created a duplicate grid starting in cell V3. The cells in that grid have this formula:

All that grid does is generate the numbers that will be used in the textboxes. Next I wrote some code to create the textboxes.

That puts a transparent textbox over each cell in my original grid and links it to the duplicate grid. The downside to this is that I can’t select cells in the original grid with the mouse because I end up selecting the textbox. I have to use the keyboard, which, I’m sure you can appreciate, doesn’t break my heart. Because the textboxes are transparent, any text in the cells underneath them shows through.

That works keenly. Entering letters one at a time is a bit of a pain. I just want to type the whole word and be done with it. I refactored my change event code thusly.

That’s a bit of a long one. If a space is entered, a corresponding symmetrical space is entered. If the cell is deleted, any symmetrical spaces are deleted. Beyond those two scenarios, it puts each letter of the word typed into a square, then puts a space at the end. To indicate that the word should go down instead of a cross, I precede the word with a period – I picked that character arbitrarily. To type in the word for 1-Down, I select C3 and type “.golda” and the word gets filled in.

Finally, there are a couple rows of formulas at the bottom. This guy says to keep your black space to around 1/6. In the above example, Bruce Venzke did a heck of a job.

To start anew, select C3:Q17 and press delete. At this rate, this could start getting useful around version 5. Thanks for your comments on the last post. I’m interested to hear what you have to say about this iteration.

You can download crossword2.zip

7 thoughts on “Crossword Builder Version 2”

  1. Hey Dick. I’m not really a fan of solving (or creating) crossword puzzles – too much thinking for me, but I find this crossword template fun and intersting. While I was creating my own puzzle I thought it might be useful to have the program select the next entry-point for me after I enter a word. The following routine will accomplish this task.

    Private Sub SelectNextEntrySquare(rcell As Range)
        Dim rSelCell As Range
       
        ‘ If you are at the end of the puzzle then goto the beginning of the puzzle
        If rcell.AddressLocal = “Q17” Or rcell.Offset(0, 1).AddressLocal = “Q17” Then
            Range(“C3”).Select
            GoTo ExitHere
       
        ‘ If you have reached the last column in the puzzle then resume on the following line
        ElseIf rcell.Column = 17 Then
            Set rSelCell = rcell.Offset(1, -14)
        ElseIf rcell.Offset(0, 1).Column = 17 Then
            Set rSelCell = rcell.Offset(1, -13)
       
       
        Else
            Set rSelCell = rcell.Offset(0, 2)
        End If

    FindCell:
        ‘ Find the next empty cell from the starting point
        Do Until rSelCell.Column = 17
            If IsEmpty(rSelCell) Then
                rSelCell.Select
                GoTo ExitHere
            End If
           Set rSelCell = rSelCell.Offset(0, 1)
        Loop
       
        ‘ Reached the last column in the puzzle
        ‘ Resume at the next line
        Set rSelCell = rSelCell.Offset(1, -14)
        GoTo FindCell
       
    ExitHere:
        Exit Sub
    End Sub

    Then I added a call to this routine at the end of the last Else statement in Worksheet_Change:

    …

                            ‘Put a space after the word
                            If i = Len(sWord) Then
                                If bAcross Then
                                    rNext.Offset(0, 1).Value = Space(1)
                                    AddSpace rNext.Offset(0, 1)
                                Else
                                    rNext.Offset(1, 0).Value = Space(1)
                                    AddSpace rNext.Offset(1, 0)
                                End If
                            End If
                        Next i
                       
                        SelectNextEntrySquare rNext
                    End If

    …

    This would be useful if the user is entering the words horizontally. I don’t think it would be all that helpful for entering the words vertically.

    -David Landry

  4. David, if your feeling keeping to allow for mouse usage you can use a standard rectangle with text populated and lock the spreadsheet so it’s unselectable. It would make for a more user friendly experience.

    But otherwise an awesome bit of coding

  6. Stelios

    I think you only need to change this line

    If sLetter Like “[A-Z]” Then

    This limits the entry to capital letters of the alphabet, but you can use Like to limit it however you like.

  7. I am wondering if there is any way to modify the program to use Sunday crosswords, or modify to use grids that are 13 boxes wide and 14 boxes high? I’d like to especially use 23×23 grids for Sunday puzzles.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.