J
John Davies
HOW CAN I MAKE A SINGLE CELL IN EXCEL DISPLAY UPPERCASE ONLY
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
Just because you want upper case in that cell, you don't have to inflict it
on us.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$1" Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
on us.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$1" Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.
J
John Data
Thanks Bob Phillips
This works fine on 1 cell, however there are another 3 individual cells
that should show only uppercase. I have tried pasting the code again with
different cell values but I get a compile error.
Is there a way to do this?
This works fine on 1 cell, however there are another 3 individual cells
that should show only uppercase. I have tried pasting the code again with
different cell values but I get a compile error.
Is there a way to do this?
B
Bob Phillips
Change
If Target.Address = "$H$1" Then
to the cells you want, for example
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
If Target.Address = "$H$1" Then
to the cells you want, for example
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
J
John Data
Bob
I changed the line as you advised but nothing happens after I paste the code.
The pasted lines are as follows:-
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Is ther anything wrong with these?
Thanks
I changed the line as you advised but nothing happens after I paste the code.
The pasted lines are as follows:-
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Is ther anything wrong with these?
Thanks
B
Bob Phillips
John,
It works fine for me. Make sure you have events enabled, type this in the VB
IDE immediate window
Application.EnableEvents = True
and return.
It works fine for me. Make sure you have events enabled, type this in the VB
IDE immediate window
Application.EnableEvents = True
and return.
J
John Data
Thanks Bob
I restarted Excel and the code now works fine but I did not understand what
you meant by "have events enabled, type this in the VB IDE immediate window".
Where is that?"
I restarted Excel and the code now works fine but I did not understand what
you meant by "have events enabled, type this in the VB IDE immediate window".
Where is that?"
B
Bob Phillips
Hi John,
The code I gave you is worksheet event code. There is a property called
EnableEvents which can be turned on or off. If Off, the event will not fire,
so nothing happens. If you look at the code you will see
Application.EnableEvents = False
at the start, and
Application.EnableEvents = True
at the end. This is done to stop events cascading from our code.
The immediate window is a window in the VB IDE (Alt-F11). You open it from
the View>Immediate Window (or Ctrl-G). This allows you test things out in
immediate mode, or look at variables when stepping through code.
The code I gave you is worksheet event code. There is a property called
EnableEvents which can be turned on or off. If Off, the event will not fire,
so nothing happens. If you look at the code you will see
Application.EnableEvents = False
at the start, and
Application.EnableEvents = True
at the end. This is done to stop events cascading from our code.
The immediate window is a window in the VB IDE (Alt-F11). You open it from
the View>Immediate Window (or Ctrl-G). This allows you test things out in
immediate mode, or look at variables when stepping through code.
J
John Data
Hi Bob
Thanks for all your help
Thanks for all your help
Bob Phillips said:Hi John,
The code I gave you is worksheet event code. There is a property called
EnableEvents which can be turned on or off. If Off, the event will not fire,
so nothing happens. If you look at the code you will see
Application.EnableEvents = False
at the start, and
Application.EnableEvents = True
at the end. This is done to stop events cascading from our code.
The immediate window is a window in the VB IDE (Alt-F11). You open it from
the View>Immediate Window (or Ctrl-G). This allows you test things out in
immediate mode, or look at variables when stepping through code.