ProperCase problem

  • Thread starter Thread starter ditchy
  • Start date Start date
D

ditchy

Hi there Excelers
I have a problem that I am unable to fix, is there someone that can
help me. Just learning VBA and have some trouble with the following

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = vbProperCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

The vbproperCase gets highlighted and a message, Compile Error,
Expected Array
Also should this be put in sheet1, or this workbook.
Any assistance would be greatly appreciated
Thanks
DITCHY, Ballarat Australia
 
Try changing:
Target(1).Value = vbProperCase(Target(1).Value)
to
Target.Value = StrConv(Target.Value, vbProperCase)
 
Ditchy,

vbProperCase is a constant, not a function. StrConv is the function you are
after.

Try this in the Immediate window and you'll see how it works:
?StrConv("blaha blaha",vbProperCase)

HTH
Anders Silvén
 
Anders S said:
Ditchy,

vbProperCase is a constant, not a function. StrConv is the function you are
after.

Try this in the Immediate window and you'll see how it works:
?StrConv("blaha blaha",vbProperCase)

HTH
Anders Silvén
Click to expand...
thank you for your prompt reply, what is the immediate window? (new to vba)
 
Hi Ditchy,

In the VBA editor type Ctrl+G or choose View>Immediate window (not sure what
it's called, I don't have an English version of Excel).

For example, in the Immediate you can execute one-line commands like
?1+2
?range("B3").Value
to get values , or
range("B2").Value= 45
ActiveSheet.Name = "Roger"
to set values, and much more.

I find it very useful and I use it all the time for debugging and testing (and
learning).

When stepping through a macro that doesn't work you can examine your data in
detail, in addition to the Watcher window.

Best regards
Anders Sílvén
 
Anders S said:
Hi Ditchy,

In the VBA editor type Ctrl+G or choose View>Immediate window (not sure what
it's called, I don't have an English version of Excel).

For example, in the Immediate you can execute one-line commands like
?1+2
?range("B3").Value
to get values , or
range("B2").Value= 45
ActiveSheet.Name = "Roger"
to set values, and much more.

I find it very useful and I use it all the time for debugging and testing (and
learning).

When stepping through a macro that doesn't work you can examine your data in
detail, in addition to the Watcher window.

Best regards
Anders Sílvén
Click to expand...
Thank you again Anders
found the window and on the way to working it out
much appreciated
Ditchy
 
Back
Top