Run time error from code capitalizing all cells in multiple ranges

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
153
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Solution
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top