Recently Answered Excel Questions

_________________________________________________________

I have a combobox which gets the initial focus on a form. I want the user to select or enter a text string, however if they just press <CR> I'd like to generate Beep and stay focussed on the combo.  How can I accomplish this?

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If ComboBox1.Value = "" Then 
        Beep
        Cancel = Not (Cancel)
    End If 

End Sub

This code prevent defocussing rather than actively moves the focus.

_____________________________________________________________________________________

How can I build in a 'limiting' function so that a user can only open the spreadsheet 20 times (for example)? After that it would not open and they'd be prompted via a message box to contact me for a new copy.  This would make sure that our users always have the most up to date version. Also this could be useful for a ShareWare (try before you buy) application.

Sub Auto_Open()

    MaxUses = 10

    Application.DisplayAlerts = False

    Sheets(5).Cells(1, 1).Value = Sheets(5).Cells(1, 1).Value + 1
        If Sheets(5).Cells(1, 1).Value < MaxUses Then ThisWorkbook.Save

    If Sheets(5).Cells(1, 1).Value > MaxUses Then
        MsgBox "Please contact the author for an updated version", vbOKOnly, "Whoa Partner"
        ThisWorkbook.Close
    End If

End Sub

This subroutine name must be used exactly as typed above in any module in the existing project.  Obviously I am saving the number in cell A1 of  sheet 5.  Obviously said sheet must exist and not have a text value in it.  Any available workbook cell will do provided it is accurately referenced.  The saving of the workbook will ensure the number is incremented and saved upon every workbook opening.

_____________________________________________________________________________________

Help!!! I have a workbook that I've been working on for some time and it is starting to act strange.  Often it does not let me save it or it crashes Excel.  It also asks me (annoyingly) about mysterious links to non-existent workbooks.  What can be happening?

As irresponsible as it seems, MS Excel the flagship product of the Office package, and in my mind the only truly excellent piece of software of the basic four, does not have a good internal cleanup routine or something that resembles a repair feature.  The workbooks most likely to get corrupted are the ones that have been edited and saved often (of course the ones you care about the most).  Fortunately, there are code cleaners available.  The one I use is Robert Bovey's and available at Baarn's Consulting.  It has really saved my bacon a number of times and it a must have for a semi-serious to serious Excel developer.