Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Insert "Proper Function" into existing macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,091
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 22,906
Default 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,

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,651
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F daves Excel Discussion (Misc queries) 3 April 24th 07 04:52 AM
Change the "Insert Function" display for mail merge Beach Lover Excel Worksheet Functions 3 November 1st 06 06:41 PM
SUM Calculations Returns #VALUE but displays correct total with "Insert Function"=fx Yo! Excel Worksheet Functions 2 September 7th 06 08:22 PM
Is it possible to insert a macro command in an "IF" function? SandyLACA Excel Worksheet Functions 4 November 8th 05 10:46 PM
Insert "-" in text "1234567890" to have a output like this"123-456-7890" Alwyn Excel Discussion (Misc queries) 3 October 25th 05 11:36 PM


All times are GMT +1. The time now is 05:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement