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.

Another Safe way to Fix the Problem: copy a user form:

How to Fix copy a user form with SmartPCFixer?

1. You can Download Error Fixer here. Install it on your computer. When you open SmartPCFixer, it will perform a scan.

2. After the scan is finished, you can see the errors and problems which need to be fixed.

3. When the Fixing part is done, your computer has been speeded up and the errors have been removed


Related: How Can You Update & Download NVidia 6100, 6800 GS/XT Display Driver v.260.89 WHQL,Where to Download NVidia GeForce 8800 GT WHQL-certified driver v.196.21,How to Update & Download NVidia GeForce Go 7400 Display Driver v.260.19.12,[Solved] Download NVidia GeForce GTX 460M Driver v.331.82,Download NVidia Quadro Plex Model IV VGA Driver v.304.43 Certified,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Troubleshoot:Converting decimals to time Error,Solution to Problem: Copy \/ Combine all Sheets into One New \/ Separate Sheet,[Solved] Connection to Facebook inconsistant and frequent drops,Troubleshooting:Context menu item remains after uninstall Error,Cursor moves slower than my typing or moving with the arrow keys [Solved],Closing Internet Explorer causes error,Compare 2 Tables and Calculate the PROFIT Faster,Computer goes to Windows Boot Manager after Start,Connect to one of my network computer remotely when don't allow exception box Marked,computer locking up randomly

No comments:

Post a Comment