Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input boxes | Excel Discussion (Misc queries) | |||
How do I put input boxes and sumbit buttons in? | New Users to Excel | |||
How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
Mask input in input boxes? | Excel Programming | |||
Input boxes in Excel | Excel Programming |