× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. PCTECH
  3. August 2005

Re: Excel: force uppercase

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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.