Re: Excel: force uppercase

[Dan <dan27649@xxxxxxxxx>]

Thanks Bill!

responses inline:

On 8/3/05, Bill <billzbubb@xxxxxxxxxx> wrote:
> 
> >I would like to apply this to a range of cells in one column (and for
> >the purposes of this excercise, I am just testing the column and not a
> >range of rows).  It works great, except in the case when I select more
> >than one of the cells and press the Delete key to clear the contents.
> >In this case, I get an error on the line "Target.Value =
> >UCase(Target.Value)".
> >
> If you want it to work for a range of cells or for any cell, then you
> would adjust the row and column tests accordingly.  For it to work on
> all cells, you would remove the checking completely.
> 
> For it to handle cases where you are deleting contents, then you'd have
> to surround the UCase statement with the IsEmpty and IsNull checks.

Well, what I can't figure out is that deleting the contents of just
one selected cell does NOT cause an error.  BTW, it is 'Error 13'
(Type mismatch).  I implemented your suggestions, still getting the
error; here is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

   Application.EnableEvents = False

   If Target.Column = 7 And Not IsEmpty(Target.Value) And Not
IsNull(Target.Value) Then
       Target.Value = UCase(Target.Value)
   End If

   Application.EnableEvents = True

End Sub


> >I guess I should say, it "worked" great.  I can't get it to work
> >anymore.  Did it get "deactivated" when the error occurred?
> >
> Yes, the key is the EnableEvents method.  If it blows up, the enable
> events status is still off, so the procedure will not fire again until
> it's either set back on or Excel is restarted.

How can I set it back on w/o restarting Excel?

- Dan