Run time error from code capitalizing all cells in multiple ranges

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
75
I implemented the following VB code to my excel sheet where I want all input within multiple ranges forced to capital letters.

Code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("E9:CQ19, E22:CQ32, E35:C45")) Is Nothing) Then
With Target
If (Not .HasFormula) And (.Count = 1) Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

It worked until i selected multiple cells to "Clear Contents" which gave me my first run time error.
I changed the line of code .Value = UCase(.Value) to Target = UCase(Target.Cells(1) which fixed that and now I have another problem.
When I select a cell to just delete the info from and return a blank cell, i get a new run time error. The current code is as follows.



Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("E9:CQ19, E22:CQ32, E35:C45")) Is Nothing) Then
With Target
If (Not .HasFormula) And (.Count = 1) Then
Target = UCase(Target.Cells(1))
End If
End With
End If
End Sub

Any thoughts as to how to fix a run time error when i select a cell and just delete the contents?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,347
Office Version
2013
Platform
Windows
Hi,
See if this update to your code helps:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


   On Error GoTo myerror
    If Not (Application.Intersect(Target, Range("E9:CQ19, E22:CQ32, E35:C45")) Is Nothing) Then
        Application.EnableEvents = False
        With Target
            If (Not .HasFormula) And (.Count = 1) Then Target = UCase(Target.Cells(1))
        End With
    End If


myerror:
Application.EnableEvents = True
End Sub
The error trap is to ensure that EnableEvents is set back to True should an error occur.

Dave
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
75
I've received the following error:

Expected End Sub.

The following was highlighted in yellow:
Private Sub Worksheet_Change(ByVal Target As Range)

What does that mean?
And I assume all this needs to be entered in an Excel Macro-Enabled Workbook correct?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,347
Office Version
2013
Platform
Windows
I've received the following error:

Expected End Sub.

The following was highlighted in yellow:
Private Sub Worksheet_Change(ByVal Target As Range)

What does that mean?
And I assume all this needs to be entered in an Excel Macro-Enabled Workbook correct?
It means you have not copied ALL the lines of code posted & you have missed End Sub

Code should be placed in you worksheets code page & workbook saved as a macro enabled workbook.

Dave
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
75
Got it. I have it it working now! Thank you so much for your assistance.

One more thing, I've been trying to get the spreadsheet to be editable after getting this code working.
Everything I do, I can only get it to open as "Read Only" In use by another user. How do I make it so that not Read Only??


After waiting a few minutes, it seems that the Read-Only lockout goes away... Is that because code needs to run or stop running? I've not very familiar with this Macro-Enabled processing.
 
Last edited:

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,347
Office Version
2013
Platform
Windows
You get that because Excel has detetcted (or thinks it has) another copy of same workbook open.

If you have already saved file - then close Excel completely & then try re-opening your file. Hopefully this will solve for you.

Dave
 

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
75
That makes sense. Both work where in if you close and open after a short period of time it clears the issue, and also if you just click on notify, it only takes about 30 seconds for excel to realize it's the only open copy.

Thanks again for the help on this issue! Greatly appreciated.
 

Forum statistics

Threads
1,085,496
Messages
5,384,000
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top