VBA for converting data into various CASES.

P

Philip

Hello Friends

There is an immediate need at my work place where I need to convert huge
amount of data into different cases like proper, lower, upper and sentence.
If there is any freeware that lets me do all of the above please advice. I
would also love to know how to write such programmes in VBA for Excel as an
interest(specially sentence case).

Thank you

Philip Jacob
Senior Executive Quality Appraisal
First American Corporation
 
M

Max

One way is to try the sub SwitchCase* below,
which enables one to toggle the case of selected cells
*sub posted by Frank Isaacs '97

Steps
--------
1. Press Alt + F11 to go to VBE
2. Click Insert > Module
3. Copy > Paste the sub below
(everything between the dotted lines)
into the white empty space in the right-side window

-------begin vba------

Sub SwitchCase()
'Frank Isaacs May '97
'Select the cells and run
'Cells' case toggle order:
'if Lower > Upper
'if Upper > Proper
'if Proper** > Lower
'**or neither of the 3 case types

Dim Cell As Range

For Each Cell In Selection
Select Case True
Case Cell = LCase(Cell) 'It's lowercase
Cell = UCase(Cell)
Case Cell = UCase(Cell) 'It's uppercase
Cell = Application.Proper(Cell)
Case Else 'It's neither upper nor lower
Cell = LCase(Cell)
End Select
Next

End Sub

-------end vba------

4. Press Alt + Q to return to Excel

Running the macro
----------------------
5. Select the range of cells, e.g.: select A1:D200
(Or, to select entire sheet, press Ctrl + A.
The sub will work even on discontiguous selections/ranges.)

6. Press Alt + F8 (or click Tools > Macro > Macros)

In the dialog box:
Click on "SwitchCase" > Run
(or just double-click on "SwitchCase")

7. When complete, the text-case of the
selected cells will be converted
depending on their existing case, viz.:

Cells' case toggle order:
----------------------------------
if Lower > Upper
if Upper > Proper
if Proper* > Lower
*or neither of the 3 case types

To further toggle the case,
just run / re-run the sub over on the selected range(s)

---------------------
To make it easier to run / re-run the sub,
we could also assign the sub to a button drawn
from the Forms* toolbar in the sheet
(*If necesssary, activate toolbar via View > Toolbars > Forms)

Or, assign to an autoshape drawn in the sheet
(via right-click on autoshape > Assign Macro)
 
B

Bob Phillips

Philip,

Here is a simple procedure. Select the cells then run it, it should be
self-explanatory.

Sub ChangeCase()
Dim ans
ans = InputBox("Which type:" & vbNewLine & _
"1 - Upper case" & vbNewLine & _
"2 - Lower case" & vbNewLine & _
"3 - Proper Case" & vbNewLine & _
"4 - Sentence case")
If ans = 1 Or ans = 2 Or ans = 3 Or _
ans = 4 Then
ChangeData CLng(ans)
Else
MsgBox "Invalid selection, try again with a value 1-4"
End If
End Sub

Private Sub ChangeData(pzType As Long)
Dim cell As Range

For Each cell In Selection
With cell
If Not cell.HasFormula Then
Select Case pzType
Case 1: .Value = UCase(.Value)
Case 2: .Value = LCase(.Value)
Case 3: .Value = Application.Proper(.Value)
Case 4:
If Len(.Value) > 0 Then
.Value = Left(.Value, 1) & _
LCase(Right(.Value, Len(.Value) - 1))
End If
End Select
End If
End With
Next cell
End Sub
 
P

Philip

Thanks Bob

Bob if you could please look into the sentence case part of the code. It
doesn't seem to be doing it's job. The sentence doesn't have a capital letter
to start with and I don't think the periods are taken into consideration
after which again a check should be repeated. Please review and help.

Also, is there a way I can assign this macro into the right click pop up
options after selecting a range instead of te command button.

Thnaks again
Was very helpful.
 
G

Giovanni D via OfficeKB.com

Hi Sir this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please
help me.. I used excel 2000 in creating an inventory program in the hospital.
I used this excel inventorry program in our suppply room, i used one
worksheet per item. and i have almost 300 items in the supply room or almost
300 worksheets. I saved it as a template for all i know it is safer to save
it as template rather than saving it as ordinary excel files. The program was
working well, but not when i started linking(hyperlink) it from a certain
file that i always used. Then i have save it several times as a template but
i notice that the program malfuncitons, it doesnt compute the formulas i
created and some formulas are gone. Why is this happening. when i add some
items in the inventory it wouldnt add to the current balance, why is this
happening? Will you please help me, you wer the only people who can only
help me with this kind of problem......please....
 
B

Bob Phillips

Philip,

Here it is, completely revised.

What I have done is to create a context menu as you asked, but I took out
the input box, and added all of the options to the context menu. Sentence
case works properly as well now

Private Sub ChangeCase()
Dim cell As Range
Dim aryParts
Dim iPos As Long

For Each cell In Selection
With cell
If Not .HasFormula Then
Select Case Application.CommandBars.ActionControl.Parameter
Case "Upper": .Value = UCase(.Value)
Case "Lower": .Value = LCase(.Value)
Case "Proper": .Value = Application.Proper(.Value)
Case "Sentence": .Value = SentenceCase(.Value)
End Select
End If
End With
Next cell
End Sub

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCtl As CommandBarControl

On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0

With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.BeginGroup = True
.Caption = "Case Changer"

With .Controls.Add
.Caption = "Upper case"
.OnAction = "ChangeCase"
.Parameter = "Upper"
End With
With .Controls.Add
.Caption = "Lower case"
.OnAction = "ChangeCase"
.Parameter = "Lower"
End With
With .Controls.Add
.Caption = "Proper case"
.OnAction = "ChangeCase"
.Parameter = "Proper"
End With
With .Controls.Add
.Caption = "Sentence case"
.OnAction = "ChangeCase"
.Parameter = "Sentence"
End With
End With
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code
 
P

Philip

Bob,

Excellent. Great help. Thank you very much. Exactly what I was looking for.
This reduces my menial tasks to a great extent at work.

Bob, I had posted another question in this forum under the heading 'Copy
Name and email address from web sites to excel sheet' in Excel category some
time ago. I haven't received any help regarding the same. It is a part of the
same task I want to complete at work. If you could please look into it as
well and help. Probably there could be a small VB program itself that could
run as a service and be a part of the right click options in Internet
Explorer that transfers the selected text to an excel file. The excel file
acts as a database with two fields:

Name Email Address

Philip [email protected]
Bob [email protected]

And the new details gets added onto the excel sheets as I right click in
Internet explorer and choose whether the selected text goes into the first or
second field of the open workbook. The program should also contain an option
to change the workbook name and location from time to time.

I hope I am not asking too much.

Once again, I appreciate your help and thanks.

Bob Phillips said:
Philip,

Here it is, completely revised.

What I have done is to create a context menu as you asked, but I took out
the input box, and added all of the options to the context menu. Sentence
case works properly as well now

Private Sub ChangeCase()
Dim cell As Range
Dim aryParts
Dim iPos As Long

For Each cell In Selection
With cell
If Not .HasFormula Then
Select Case Application.CommandBars.ActionControl.Parameter
Case "Upper": .Value = UCase(.Value)
Case "Lower": .Value = LCase(.Value)
Case "Proper": .Value = Application.Proper(.Value)
Case "Sentence": .Value = SentenceCase(.Value)
End Select
End If
End With
Next cell
End Sub

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim oCtl As CommandBarControl

On Error Resume Next 'just in case
Application.CommandBars("Cell").Controls("Case Changer").Delete
On Error GoTo 0

With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.BeginGroup = True
.Caption = "Case Changer"

With .Controls.Add
.Caption = "Upper case"
.OnAction = "ChangeCase"
.Parameter = "Upper"
End With
With .Controls.Add
.Caption = "Lower case"
.OnAction = "ChangeCase"
.Parameter = "Lower"
End With
With .Controls.Add
.Caption = "Proper case"
.OnAction = "ChangeCase"
.Parameter = "Proper"
End With
With .Controls.Add
.Caption = "Sentence case"
.OnAction = "ChangeCase"
.Parameter = "Sentence"
End With
End With
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob Phillips

Philip said:
Thanks Bob

Bob if you could please look into the sentence case part of the code. It
doesn't seem to be doing it's job. The sentence doesn't have a capital letter
to start with and I don't think the periods are taken into consideration
after which again a check should be repeated. Please review and help.

Also, is there a way I can assign this macro into the right click pop up
options after selecting a range instead of te command button.

Thnaks again
Was very helpful.
Click to expand...
Click to expand...
 
L

lilleke

Max -
I found this very helpful, however I only need the sub to convert to all
caps so I tried to eliminate the other stuff and did not work.

can you tweek the sub below to only convert to all caps and i would also
appreciate step by step how to assign macro to an auto shape or button.

you say right-click on autoshape - assign macro - Excel2000 doesn't say
'assign macro' after I right click say the 3-D box- also in Form tool bar -
assign sub to a button - didn't let me customize a button - either one is ok
- please give me step by step. I have done the button one years ago and no
longer have any books to refer to.
thanking you in advance.
 
M

Max

.. only need the sub to convert to all caps

For your needs, try this beauty by Gord Dibben ..

Sub Upper_Case()
'Gord Dibben .misc
Dim cell As Range
'rotate through all the cells and convert to upper case
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
cell.Value = UCase(cell.Value)
Next
End Sub

As to your problem:
.. right-click on autoshape - assign macro
Excel2000 doesn't say 'assign macro'
Click to expand...

I don't know/have xl2000, so I'm not sure why, although the behaviour should
be similar.

Maybe try it again: After installing Gord's sub above, in Excel, draw say, a
rectangle on the sheet. Then just right-click on it. There should be an
option "Assign Macro". Choose this option, then select "Upper_Case" in the
dialog and click OK.
 
Top