Can anyone help with input boxes?

  • Thread starter Thread starter index
  • Start date Start date
I

index

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?
 
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?


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
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
 
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
 
Back
Top