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

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

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
 
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)
 
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)
Click to expand...

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
 
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
Click to expand...

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
 
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:
 
(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.
Click to expand...

???

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

Rick
 
Oops. You (and Bob) are right.

The line should be changed to:
If Not Intersect(Target, Sh.Range(WS_RANGE)) Is Nothing Then
 
Dave Peterson said:
Oops. You (and Bob) are right.

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

And Bob is wrong (as you noted). I could have sworn I posted a correction to
that.
 
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!)
 
Back
Top