Run time error from code capitalizing all cells in multiple ranges

[EBoudreau]

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]

Hi,
See if this update to your code helps:

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]

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]

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]

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.

 

[dmt32]

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]

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.