Insert "Proper Function" into existing macro

  • Thread starter Thread starter jackel
  • Start date Start date
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,
 
You do not give us the "macro" or formula that you are using. We are not
mind readers.

Tyro
 
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
 
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
 
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
 
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
 
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
 
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,
 
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
 
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.
 
'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

Macro to copy value in field and insert it into filter 2
PROPER Text Function 2
changing to proper text 1
Macro Help 1
Macro for Proper function 4
How do I apply the formula =PROPER(text) to existing text in works 1
Average Function 2
Protection Macro 4

Back
Top