Re: Excel: force uppercase

[Dan <dan27649@xxxxxxxxx>]

I tried your suggestion with "For Each rngCell In Target.Cells", but
same problem.

I probably took the cheap way out, but I inserted an On Error
statement (prior to the "If Target.Column...") that takes it to the
Application.EnableEvents = True statement.  Works like a charm.

Figured out how to digitally sign so I would stop getting the annoying
"Macros Warning" every time I opened the file.

I tried your advice on the Immediate window to re-enable the events
status, but nothing happened.  Was there a way to "invoke" it after I
entered the code?

On 8/3/05, Bill <billzbubb@xxxxxxxxxx> wrote:
> 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
> 
> --
> This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
> To post a message email: PcTech@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/pctech
> or email: PcTech-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/pctech.
>