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