Insert "Proper Function" into existing macro
 

Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,




--
jackel

Insert "Proper Function" into existing macro
 

You do not give us the "macro" or formula that you are using. We are not
mind readers.

Tyro

"jackel" wrote in message
...

Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,




--
jackel

Insert "Proper Function" into existing macro
 

VBA has a StrConv function.

I'm not quite sure what you're doing, but you can use this kind of function to
convert a string to proper case:

dim myStr as string
myStr = "this is a test"
mystr = strconv(mystr,vbProperCase)
msgbox myStr



jackel wrote:

Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,

--
jackel

--

Dave Peterson

Insert "Proper Function" into existing macro
 

Try this macro.

Sub Proper_All_Sheets()
Dim cell As Range
Dim moretext As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wkbkToCount = ActiveWorkbook
For Each ws In wkbkToCount.Worksheets
ws.Activate
For Each cell In ws.UsedRange
If Not cell.HasFormula And Not IsNumeric(cell) Then
cell.Formula = Application.Proper(cell.Formula)
End If
Next cell
Next ws
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Sun, 9 Mar 2008 01:16:55 +0000, jackel
wrote:


Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,

Insert "Proper Function" into existing macro
 

Here is the code I need the proper function to merge with, sorry about
not posting it to view.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
'Update date-time stamp when a job is set in column E
If Not Intersect(Range("A3:A300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 4).ClearContents
Else
If .Offset(0, 4).Value = "" Then
With .Offset(0, 4)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
End If
End If
Application.EnableEvents = True
'Update date-time stamp when job is completed in column H
ElseIf Not Intersect(Range("K3:K300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
.Offset(0, -2).ClearContents
Else
With .Offset(0, -3)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
'Calculate job duration in column I
.Offset(0, -2) = .Offset(0, -3) - .Offset(0, -6)
End If
Application.EnableEvents = True
End If
End With
Application.EnableEvents = False
Select Case Target.Column
Case 5, 8, 9
Application.Undo
MsgBox "This cannot be Changed!"
End Select
Application.EnableEvents = True
End Sub


Gord Dibben;2663769 Wrote:
Try this macro.

Sub Proper_All_Sheets()
Dim cell As Range
Dim moretext As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wkbkToCount = ActiveWorkbook
For Each ws In wkbkToCount.Worksheets
ws.Activate
For Each cell In ws.UsedRange
If Not cell.HasFormula And Not IsNumeric(cell) Then
cell.Formula = Application.Proper(cell.Formula)
End If
Next cell
Next ws
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Sun, 9 Mar 2008 01:16:55 +0000, jackel

wrote:
-

Greetings,

I cannot figure out how to use the Proper Function in a macro that
runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would
be
appreciated!

Thanks,
-




--
jackel

Insert "Proper Function" into existing macro
 

On Sun, 9 Mar 2008 01:16:55 +0000, jackel
wrote:


Greetings,

I cannot figure out how to use the Proper Function in a macro that runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would be
appreciated!

Thanks,


Application.WorksheetFunction.Proper(your_macro_ou tput_string)
--ron

Insert "Proper Function" into existing macro
 

You want to change case for the cell that the user changed?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
'Update date-time stamp when a job is set in column E
If Not Intersect(Me.Range("A3:A300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 4).ClearContents
Else
.Value = StrConv(.Value, vbProperCase)
If .Offset(0, 4).Value = "" Then
With .Offset(0, 4)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
End If
End If
Application.EnableEvents = True
'Update date-time stamp when job is completed in column H
ElseIf Not Intersect(Me.Range("K3:K300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
.Offset(0, -2).ClearContents
Else
.Value = StrConv(.Value, vbProperCase)
With .Offset(0, -3)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
'Calculate job duration in column I
.Offset(0, -2) = .Offset(0, -3) - .Offset(0, -6)
End If
Application.EnableEvents = True
End If
End With
Application.EnableEvents = False
Select Case Target.Column
Case 5, 8, 9
Application.Undo
MsgBox "This cannot be Changed!"
End Select
Application.EnableEvents = True
End Sub

jackel wrote:

Here is the code I need the proper function to merge with, sorry about
not posting it to view.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
'Update date-time stamp when a job is set in column E
If Not Intersect(Range("A3:A300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Offset(0, 4).ClearContents
Else
If .Offset(0, 4).Value = "" Then
With .Offset(0, 4)
NumberFormat = "dd/mmm/yyyy - hh:mm"
Value = Now
End With
End If
End If
Application.EnableEvents = True
'Update date-time stamp when job is completed in column H
ElseIf Not Intersect(Range("K3:K300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Offset(0, -3).ClearContents
Offset(0, -2).ClearContents
Else
With .Offset(0, -3)
NumberFormat = "dd/mmm/yyyy - hh:mm"
Value = Now
End With
'Calculate job duration in column I
Offset(0, -2) = .Offset(0, -3) - .Offset(0, -6)
End If
Application.EnableEvents = True
End If
End With
Application.EnableEvents = False
Select Case Target.Column
Case 5, 8, 9
Application.Undo
MsgBox "This cannot be Changed!"
End Select
Application.EnableEvents = True
End Sub

Gord Dibben;2663769 Wrote:
Try this macro.

Sub Proper_All_Sheets()
Dim cell As Range
Dim moretext As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wkbkToCount = ActiveWorkbook
For Each ws In wkbkToCount.Worksheets
ws.Activate
For Each cell In ws.UsedRange
If Not cell.HasFormula And Not IsNumeric(cell) Then
cell.Formula = Application.Proper(cell.Formula)
End If
Next cell
Next ws
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Sun, 9 Mar 2008 01:16:55 +0000, jackel

wrote:
-

Greetings,

I cannot figure out how to use the Proper Function in a macro that
runs
my spreadsheet. I need the out put to be normal and not all caps. The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would
be
appreciated!

Thanks,
-

--
jackel

--

Dave Peterson

Insert "Proper Function" into existing macro
 

Dave Peterson;2664435 Wrote:
You want to change case for the cell that the user changed?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
'Update date-time stamp when a job is set in column E
If Not Intersect(Me.Range("A3:A300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 4).ClearContents
Else
.Value = StrConv(.Value, vbProperCase)
If .Offset(0, 4).Value = "" Then
With .Offset(0, 4)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
End If
End If
Application.EnableEvents = True
'Update date-time stamp when job is completed in column H
ElseIf Not Intersect(Me.Range("K3:K300"), .Cells) Is Nothing
Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -3).ClearContents
.Offset(0, -2).ClearContents
Else
.Value = StrConv(.Value, vbProperCase)
With .Offset(0, -3)
.NumberFormat = "dd/mmm/yyyy - hh:mm"
.Value = Now
End With
'Calculate job duration in column I
.Offset(0, -2) = .Offset(0, -3) - .Offset(0, -6)
End If
Application.EnableEvents = True
End If
End With
Application.EnableEvents = False
Select Case Target.Column
Case 5, 8, 9
Application.Undo
MsgBox "This cannot be Changed!"
End Select
Application.EnableEvents = True
End Sub

jackel wrote:-

Here is the code I need the proper function to merge with, sorry
about
not posting it to view.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
'Update date-time stamp when a job is set in column E
If Not Intersect(Range("A3:A300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Offset(0, 4).ClearContents
Else
If .Offset(0, 4).Value = "" Then
With .Offset(0, 4)
NumberFormat = "dd/mmm/yyyy - hh:mm"
Value = Now
End With
End If
End If
Application.EnableEvents = True
'Update date-time stamp when job is completed in column H
ElseIf Not Intersect(Range("K3:K300"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
Offset(0, -3).ClearContents
Offset(0, -2).ClearContents
Else
With .Offset(0, -3)
NumberFormat = "dd/mmm/yyyy - hh:mm"
Value = Now
End With
'Calculate job duration in column I
Offset(0, -2) = .Offset(0, -3) - .Offset(0, -6)
End If
Application.EnableEvents = True
End If
End With
Application.EnableEvents = False
Select Case Target.Column
Case 5, 8, 9
Application.Undo
MsgBox "This cannot be Changed!"
End Select
Application.EnableEvents = True
End Sub

Gord Dibben;2663769 Wrote:-
Try this macro.

Sub Proper_All_Sheets()
Dim cell As Range
Dim moretext As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Set wkbkToCount = ActiveWorkbook
For Each ws In wkbkToCount.Worksheets
ws.Activate
For Each cell In ws.UsedRange
If Not cell.HasFormula And Not IsNumeric(cell) Then
cell.Formula = Application.Proper(cell.Formula)
End If
Next cell
Next ws
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Sun, 9 Mar 2008 01:16:55 +0000, jackel

wrote:
-

Greetings,

I cannot figure out how to use the Proper Function in a macro that
runs
my spreadsheet. I need the out put to be normal and not all caps.
The
users, use all caps because they are too lazy to hit the shift key.

I have 12 sheets, one for each month with 12 columns and 200 rows
per
sheet. I know from reading the other "proper, tags" in the forum the
entire book can be done with a macro, at the location I try to place
it, everything after the proper, stops working. Any assistance would
be
appreciated!

Thanks,
--

--
jackel-

--

Dave Peterson

Dave,

I need the first letter in Cap & the rest in lower, I tried what you
gave me and it doesn't seem to work. Thanks,




--
jackel

Insert "Proper Function" into existing macro
 

I need the first letter in Cap & the rest in lower, I tried what
you gave me and it doesn't seem to work. Thanks,

What version of Excel are you using? I am using XL2003 and its AutoCorrect
(Tools/AutoCorrect Options) feature already does this (live, as you type)
when the "Capitalize first letter of sentences" check box is checked.

Rick

Insert "Proper Function" into existing macro
 

Proper Will Make The First Letter Of Each Word Uppercase.

..Value = StrConv(.Value, vbProperCase)
becomes
..value = ucase(left(.value,1)) & lcase(mid(.value))

Get both spots.

jackel wrote:

Dave,

I need the first letter in Cap & the rest in lower, I tried what you
gave me and it doesn't seem to work. Thanks,

--
jackel

--

Dave Peterson

Insert "Proper Function" into existing macro
 

'Rick Rothstein \(MVP - VB\)[_191_ Wrote:
;2664724'] I need the first letter in Cap & the rest in lower, I tried
what-
you gave me and it doesn't seem to work. Thanks,-

What version of Excel are you using? I am using XL2003 and its
AutoCorrect
(Tools/AutoCorrect Options) feature already does this (live, as you
type)
when the "Capitalize first letter of sentences" check box is checked.

Rick

I am using the same version as you and have already chosen the auto
correct option and it doesn't work.




--
jackel