How to Update Logitech Drivers? : [Anwsered] copy a user form

Friday, 23 September 2016

[Anwsered] copy a user form

copy a user form

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.

Anwsers to the Problem copy a user form

Download SmartPCFixer to Fix It (Free)

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

If you failed to fix it, another option is to call Microsoft and walk through the process with a technician. To do this, dial "1-800-936-5700". Explain the error that you received and the steps in the update process during which it was displayed. The technician will be able to walk through the steps from getting a new product key to finishing the installation.