Script to make text in uppercase, on workbook level, but not for other opened workbooks.

[AA Arens]

Hi,

(A) I do have undermentioned script, but I cant't get it worked. I
want to have cells changed to uppercase after Enter-command.
(B) How to have this script for all sheets, so to have it on workbook
level? And, how to avoid that other excel documents that are opened
also have the text capitalized?

Thank you.


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Error_handler
With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Row = 10 Then Target.Value = UCase(Target.Value)
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End With

Error_handler:
Resume Next

Const WS_RANGE As String = "D4:AJ380"

On Error GoTo Error_handler
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = UCase(Target.Value)
End If
End If
End With

Error_handler:
Application.EnableEvents = True

 

[Bob Phillips]

This should be all that you need


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const WS_RANGE As String = "D4:AJ380"

On Error GoTo Error_handler
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = UCase(Target.Value)
End If
End If
End With

Error_handler:
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[AA Arens]

This should be all that you need

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const WS_RANGE As String = "D4:AJ380"

On Error GoTo Error_handler
Application.EnableEvents = False

With Target
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not Target.HasFormula Then
Target.Value = UCase(Target.Value)
End If
End If
End With

Error_handler:
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I get an error message: Method or data member not found

at .Range in: If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
Then

 

[Rick Rothstein \(MVP - VB\)]

I get an error message: Method or data member not found

at .Range in: If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
Then

If looks like it will work if you change the 'Me' object to 'ActiveSheet'...

If Not Intersect(Target, ActiveSheet.Range(WS_RANGE)) Is Nothing Then

Rick

 

[Dave Peterson]

Remember that this is a worksheet event and the code goes in a worksheet module.

Read Bob's instructions one more time.

AA Arens wrote:

 

[Rick Rothstein \(MVP - VB\)]

(B) How to have this script for all sheets, so to have it on workbook

Remember that this is a worksheet event and the code goes in a worksheet
module.

???

Bob said it was workbook event code and the OP said he wanted it to work on
all sheets in his open workbook.

Rick

 

[Dave Peterson]

Oops. You (and Bob) are right.

The line should be changed to:
If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then

 

[Bob Phillips]

Oops. You (and Bob) are right.

The line should be changed to:
If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then

And Bob is wrong (as you noted). I could have sworn I posted a correction to
that.

 

[Dave Peterson]

But Bob was correct about how to install the macro.

(Sometimes, when someone (me) only glances at the code, it can all look the
same. I saw the me reference, so I thought it was a worksheet_change event. I
should have glanced up!)