How to make a popup macro

Z

zibanitum

Board Regular
Joined
Feb 26, 2008
Messages
88
I have created a custom toolbar and have placed several buttons on it to run common macros that I use.

I have 3 macros that changes the case of the text - proper, upper and lower. Instead of having 3 macros. Is there a way I can combine those macros to one and when I click the button, a popup will display Upper/Lower/Proper? I can then select the one I want.

These are the 3 the macros that I am use.

Code: 
Sub LowerCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = LCase(cell)
End If
Next
End Sub
Code: 
Sub UpperCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub
Code: 
Sub MakeProper()
    Dim rngSrc As Range
    Dim lMax As Long, lCtr As Long

    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
    lMax = rngSrc.Cells.Count

    For lCtr = 1 To lMax
        If Not rngSrc.Cells(lCtr).HasFormula Then
            rngSrc.Cells(lCtr) = Application.Proper(rngSrc.Cells(lCtr))
        End If
    Next lCtr
End Sub
Can anyone help me?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Hi zibanitum,

Probably the neatest way would be to create an user form with the three options in say a list box or using radio buttons, though the following is another way that I can post straight here for you:

Code: 
Sub Macro3()

    Dim strCaseSelection As String
    
InputBoxSelection:
    
    strCaseSelection = InputBox("Enter the first letter corresponding the case selection you want:" & _
                       vbNewLine & vbNewLine & _
                       "Lower, or" & vbNewLine & _
                       "Upper, or" & vbNewLine & _
                       "Proper or", _
                       "Case Selector", "L")
    
    'Exit the routine if the user has clicked the <Cancel> button _
    or has made an entry.
    If Len(strCaseSelection) = 0 Then
        Exit Sub
    Else
        'Select only the first character of what was in entered in the _
        input box (in case "Upper" was enter for example) in uppercase.
        Select Case Left(StrConv(strCaseSelection, vbUpperCase), 1)
            Case "L"
                Call LowerCase
            Case "U"
                Call UpperCase
            Case "P"
                Call MakeProper
            'If the response can't be evaluated, ask if the user wants _
            to try again.
            Case Else
                If MsgBox(strCaseSelection & " is not a valid entry." & vbNewLine & "Try again?", vbQuestion + vbYesNo, "Rate Update") = vbYes Then
                    GoTo InputBoxSelection
                Else
                    Exit Sub
                End If
        End Select
        
    End If
        
End Sub

HTH

Robert
 
Last edited:
Upvote 0
I keep getting an error message: "Compile error. Sub or function not defined" when it gets to Call Lowercase.

What can I do to correct this?
 
Upvote 0
When calling a macro in this way it needs to reside in the same module (workbook). If it doesn't you'll have to incorporate the code directly into the applicable case statement , i.e.

Code: 
Case "L"
                Dim cell As Range
                    For Each cell In Selection.Cells
                        If cell.HasFormula = False Then
                            cell = LCase(cell)
                        End If
                    Next

HTH

Robert
 
Upvote 0
try
Code: 
Sub test()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
                    "2 for Lower Case" & vbLf & "3 for Proper Case", type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
Set rng = Selection.SpecialCells(2)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each r In rng
        r.Value = StrConv(r.Value, myCase)
    Next
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Propercase - macro so that it will change all names in a column to Proper case
Replies
5
Views
354
MARK858
MARK858
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
582
Aurum1
A
D
  • Question Question
why show the frame of userform as the photo1
Replies
0
Views
210
david2005wang
D
A
  • Solved
This VBA macro does not work - Why? - (expected to capitalize first letter of first word in sentence, following period & space)
Replies
9
Views
411
abramo
A
A
  • Solved
Macros Help - How to show Values from data pulled?
Replies
5
Views
243
RobP
R

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top