copy a user form

Question

I created a user form with associated event handler procedures in a new worksheet.  It was my first attempt and I didn't want to create it in my personal.xlsm file.   Two questions:

1.  It's supposed to only display the user form if a range has been selected, but instead it runs the VBA code on the entire worksheet.  I had added this module to the project:

Sub ChangeCase()

'Exit if a range is not selected

    If TypeName(Selection) = "Range" Then

'Show the dialog box

        UserForm1.Show

   End If

End Sub

It's as if the UserForm isn't connected to this condition.   (In case anyone's familiar with it, I'm creating the user form and its associated macro step-by-step from Chapter 16 of "Excel 2007 VBA Programming for Dummies"   Very appropriately named.  I've uploaded my test version at

http://www.mediafire.com/file/f0h7y9w7rqd0v1j/UpperCaseMacro User Form.xlsm

Second question (once I get #1 resolved):  Is there an easy way to copy this user form to personal.xlsm?  I know how to copy code from modules.

Answer 1

Hi,

I now see the problem. If you select a single cell and call your code it's changing the case of every cell on the worksheet instead of just the selected cell.

I have never understood why but this is an oddity in the way specialcells seems to behave. If you select a single cell and execute this line

Set WorkRange = Selection.SpecialCells(xlCellTypeConstants, xlCellTypeConstants)

then Workrange will be set to Set to all cells so if you try and execute your code on a single cell it will fail and change the case of cells that weren't selected. If you select multiple cells then the code works fine and sets Workrange to cells containing text values within that selection. Here's one way

EDIT.... In the sample workbook then If TypeName (Selection)... does nothing because there's nothing else to select but if the worksheet had some shapes or a chart in that was selected then it would be useful. I would leave it in.

Private Sub OKButton_Click()

    Dim WorkRange As Range

'Process only text cells, no formulas

    'On Error Resume Next

    If Selection.Cells.Count > 1 Then

        Set WorkRange = Selection.SpecialCells(xlCellTypeConstants, xlCellTypeConstants)

    ElseIf WorksheetFunction.IsText(ActiveCell.Value) Then

        Set WorkRange = ActiveCell

    Else

        MsgBox "Only numeric cells selected"

       Unload Me

       Exit Sub

    End If

    'Set WorkRange = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)

'Upper Case

    If OptionUpper Then

        For Each cell In WorkRange

        If Not cell.HasFormula Then

            cell.Value = UCase(cell.Value)

        End If

        Next cell

    End If

'Lower Case

    If OptionLower Then

        For Each cell In WorkRange

        If Not cell.HasFormula Then

            cell.Value = LCase(cell.Value)

        End If

        Next cell

    End If

'Proper Case

    If OptionProper Then

        For Each cell In WorkRange

        If Not cell.HasFormula Then

            cell.Value = Application. _

                WorksheetFunction.Proper(cell.Value)

        End If

        Next cell

    End If

    Unload UserForm1

End Sub

Answer 2

I want it to run ONLY if  the user has identified a range for it to affect.  As it now works, it runs on the entire spreadsheet if no range is specified.   In the example that I uploaded, If I select A1:A3 and then run the macro, it works fine; only changing the case for those three cells.  If I run the macro without choosing a range; having just one cell "selected" (the cell where my mouse happens to be 'parked' at the time) then the entire worksheet's case is changed.  I don't want the macro to run at all unless a range is selected first.  

According to the Excel 2007 VBA Programming for Dummies instructions, this module will stop the macro from running if no range is selected:

Sub ChangeCase()

  'Exit if a range is not selected

       If TypeName (Selection) = "Range" Then

'show the dialog box

       UserForm1.Show

   End If

End Sub

The author's note:  "This procedure is simple.  It checks to make sure a range is selected.  If not, the macro ends with no action.  If a range is selected, the dialog box appears..."  

Does it also need an " If TypeName (Selection) <> "Range" Then... " statement?   There was none in the book's example.

Answer 3

Hi,

I've looked at this workbook and there's nothing else to select apart from a range i.e. there will always be cell(s) selected so the userform will always run. Perhaps you could explain a bit more clearly under what xircumstances you want the userform to run or not.

Answer 4

Thanks!  I added to my question above.  I only THOUGHT it was working as expected.

Answer 5

Hi Cheryl,

You can simply drag the form from your current VBA project to the VBA project belonging to personal.xlsb