Insert "Proper Function" into existing macro

J

jackel

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,
 
T

Tyro

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

Tyro
 
D

Dave Peterson

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
 
G

Gord Dibben

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
 
J

jackel

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
 
R

Ron Rosenfeld

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


Application.WorksheetFunction.Proper(your_macro_output_string)
--ron
 
D

Dave Peterson

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
 
J

jackel

Dave said:
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 Nothin
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, sorr
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
(e-mail address removed)
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 row
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,
Click to expand...

Dave,

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

Rick Rothstein \(MVP - VB\)

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

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
 
D

Dave Peterson

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

jackel

'Rick Rothstein \(MVP - VB\)[_191_ said:
;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
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need VBA Macro to Loop through pages and print counter in footer of each page 9
Macro using CLEAN & PROPER on a worksheet. 1
PROPER FUNCTION 5
Help w/ the PROPER function 2
WORD VBA Search and switch to advanced ... 1
XIRR vs. IRR Function 11
Splitting a number in one cell into other cells 0
Proper function for a chart 1

Top