Can anyone help with input boxes?
 

I have 3 simple macros to convert text to Upper, Lower or Proper case
respectively. At present these are independant of each other. What i
would like to do is combine them into one macro where the user clicks
to run the macro and a box comes up asking the user which format they
want to convert the text into. The user would then select the format,
click OK and the text would be converted...

Sound easy? Not for me! Can anyone help please?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Can anyone help with input boxes?
 

Here is a possibility for you. Rather than using an input
box, in which the user has to type from the keyboard, I
use a shortcut menu which I think is nicer for the user -
just click the one you want!

Copy the following to a module and enter your three macro
names in the "OnAction" lines - GIVE THIS A TRY!:

Sub CaseShorcutMenu()

Dim cmdBars As CommandBars
Dim strMenuName As String
Dim intX As Integer

strMenuName = "TEMP"
Set cmdBars = Application.CommandBars

'Delete it if it already exists
For Each xBar In Application.CommandBars
If UCase(xBar.Name) = UCase(strMenuName) Then cmdBars
(strMenuName).Delete: Exit For
Next

'Add a new one
cmdBars.Add Name:=strMenuName, Position:=msoBarPopup,
Temporary:=True

With CommandBars(strMenuName)
intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "UPPER CASE"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_1"

intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "Proper Case"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_2"

intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "lower case"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_3"

End With
cmdBars(strMenuName).ShowPopup

End Sub

-----Original Message-----

I have 3 simple macros to convert text to Upper, Lower or
Proper case
respectively. At present these are independant of each
other. What i
would like to do is combine them into one macro where the
user clicks
to run the macro and a box comes up asking the user which
format they
want to convert the text into. The user would then
select the format,
click OK and the text would be converted...

Sound easy? Not for me! Can anyone help please?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Can anyone help with input boxes?
 

Please note the following about my previous post:

The following lines should appear as one line in your
module, please edit accordingly - sorry about that :(

If UCase(xBar.Name) = UCase(strMenuName) Then cmdBars
(strMenuName).Delete: Exit For

cmdBars.Add Name:=strMenuName, Position:=msoBarPopup,
Temporary:=True


-----Original Message-----
Here is a possibility for you. Rather than using an input
box, in which the user has to type from the keyboard, I
use a shortcut menu which I think is nicer for the user -
just click the one you want!

Copy the following to a module and enter your three macro
names in the "OnAction" lines - GIVE THIS A TRY!:

Sub CaseShorcutMenu()

Dim cmdBars As CommandBars
Dim strMenuName As String
Dim intX As Integer

strMenuName = "TEMP"
Set cmdBars = Application.CommandBars

'Delete it if it already exists
For Each xBar In Application.CommandBars
If UCase(xBar.Name) = UCase(strMenuName) Then cmdBars
(strMenuName).Delete: Exit For
Next

'Add a new one
cmdBars.Add Name:=strMenuName, Position:=msoBarPopup,
Temporary:=True

With CommandBars(strMenuName)
intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "UPPER CASE"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_1"

intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "Proper Case"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_2"

intX = intX + 1
.Controls.Add Type:=msoControlButton
.Controls(intX).Caption = "lower case"
.Controls(intX).OnAction = "YOUR_MACRO_NAME_3"

End With
cmdBars(strMenuName).ShowPopup

End Sub

-----Original Message-----

I have 3 simple macros to convert text to Upper, Lower
or
Proper case
respectively. At present these are independant of each
other. What i
would like to do is combine them into one macro where
the
user clicks
to run the macro and a box comes up asking the user
which
format they
want to convert the text into. The user would then
select the format,
click OK and the text would be converted...

Sound easy? Not for me! Can anyone help please?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step
guide to creating financial statements
.

.

Can anyone help with input boxes?
 

Here's one already written for your study.

Sub TextConvert()
'By Ivan F Moala
'will change the text that you have selected,
'if no text is selected it will change the whole sheet
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.text)
Case "U": ocell = UCase(ocell.text)
Case "S": ocell = UCase(Left(ocell.text, 1)) & _
LCase(Right(ocell.text, Len(ocell.text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.text)
End Select
Next

End Sub

Gord Dibben XL2002

On Fri, 5 Dec 2003 17:37:55 -0600, index
wrote:


I have 3 simple macros to convert text to Upper, Lower or Proper case
respectively. At present these are independant of each other. What i
would like to do is combine them into one macro where the user clicks
to run the macro and a box comes up asking the user which format they
want to convert the text into. The user would then select the format,
click OK and the text would be converted...

Sound easy? Not for me! Can anyone help please?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements