EXCEL

[Guest]

HOW CAN I MAKE A SINGLE CELL IN EXCEL DISPLAY UPPERCASE ONLY

 

[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.

 

[Guest]

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?

 

[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

 

[Guest]

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

 

[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.

 

[Guest]

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?"

 

[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.

 

[Guest]

Hi Bob
Thanks for all your help

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.