Re: Excel: force uppercase

[Bill <billzbubb@xxxxxxxxxx>]

Dan wrote:

> 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
>  

Interesting scenario.  One thing interesting is that the help text says 
that this method does not fire if the cells are deleted, so you 
shouldn't be entering this procedure at all.

I think the problem lies in that this is written for one cell having 
it's contents changed, and you are changing more than one cell at one 
time.  The Worksheet_Change receives a -range- as the target, so to 
handle multiple cells being in this Target range, we should be able to 
easily solve it by surrounding the "If Target. . ." code with a "For 
Each rngCell in Target.Cells    . . . .    Next rngCell"   loop.   You'd 
have to replace the Target.Column and Target.Value with rngCell.Column 
and rngCell.Value .   You can probably also get rid of the IsNull and 
IsEmpty checks since I was chasing the wrong problem.

> > 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?
>  

In the Immediate window enter the Application.EnableEvents=True line.

Bill