Tuesday, May 26, 2015

Remote Copy and Paste, version 2 (Optional Paste Special) ...Part 1

Introduction

In Remote Copy and Paste, version 1, I introduced the problem, i.e. copy-and-paste data then return to (or never leave) a worksheet.  In that post I provided a userform and code to do just that.

In this post I consider the requirements of full-blown tool then leverage the work already done to start meeting those requirements.  This post shows how to build each level and provides insights into the process I used.

Added Requirements

The problem with the first solution is it does not go far enough.  Here is the list of shortcomings (in my view) and the functionality that will cover these gaps. (NOTE: This list was modified, expanded and re-ordered.  It will be carried forward to a follow-up post, but may be modified, expanded, contracted, and re-ordered there.)
  1. It pastes all of  the source: values, formulas, formats, etc.  My understanding of the problem indicates the user would be well-served with Paste Values functionality, but it seems if I can add the full flexibility of Excel's Paste Special dialogue the added flexibility would be a great improvement.
  2. It clears the source and target fields of the user-form.  Although the code to do this can be commented out, it would be better to allow the user to choose whether to clear each field each time or to re-use the reference on succeeding uses.
    • User is copying from different ranges to the same target.  Each of the sources is for different departments or product lines, and the target is used for presentation or summarized on a "live" summary and/or chart.
    • User is copying the same information to different workbooks, as in a distribution of a reference sheet that has been updated and needs to be disseminated to multiple workbooks.
  3. The source and target fields and all checkboxes clear-able in one mouse-click.  Add a button to do so, and reset all other settings (e.g. checkboxes) to their defaults.
  4. Since there will now be settings carried forward from a previous usage, there should be the ability to reset controls to their states at the beginning of this usage, For example, if a checkbox was ON when the form opened or a range was in the Copy from or Paste To text boxes, those should be restored.
  5. At this point the only way to exit the form is to execute the copy/paste or to cancel.  Either way, the next time the form is opened it will either have the most recent settings (.i.e. checkboxes and, if the save-for-next-time checkboxes are on, the Copy from or Paste to values. Make it possible to close the form at any point, saving the current settings.
  6. A history of selected ranges should be retained and select-able from a drop-down, either on this user-form or one that pops-up when needed.
  7. The history of selected ranges should be clearable, en masse and individually.
  8. Named ranges must be typed in.  Add the ability to select Names from any open workbook.
  9. Adding the ability to reference names in any open workbook implies the value of being able to open workbooks while looking for the needed data range.
  10. A new source of data may have more or fewer rows than the previous one.  Old data should be cleared before the new data is pasted in.

Including Paste Special  

This covers #1 in the list of Added Requirements.  Finding the information on invoking the PasteSpecial dialogue wasCoding the was much easier than expected.  Changes consisted of
  • Adding a check box to the userform, cbxPasteSpecial 
  • Leveraging Excel's  Paste Special dialogue 
  • Using the True/False value of the new check box to control whether the code will use a Range.Copy <Destination>  command  or perform a Range.Copy followed by invoking Excel's  Paste Special dialogue 
  • Use Range variables (rngS and rngT) instead of using"Range(.reSource.Value)" or "Range(.reTarget.Value)" each time the associated range is needed.

Form with Use Paste Special Checkbox

The options were to add another Button control for "Copy/Paste Special", or a binary control (Checkbox, RadioButton, or ToggleButton).  I decided to go with the checkbox control because 
  • There are not multiple, mutually exclusive choices, as would be appropriate for a radioButton.
  • Off/On are more intuitive with less programming than a ToggleButton. 
Figure 1 - Copy/Paste Special form, base functionality

Changes to Copy/Paste button Click Event Sub

Rather than using more instances of the Range(<objectname.value>) the code was refined to assign that statment to Range variables, rngS (Source or Copy range) & rngT (Target or Paste range).
The main change was to include an If/Else construct, using Copy with the PasteSpecial dialouge the when Use Paste Special checkbox (cbxPasteSpecial ).  Otherwise, the rngS.Copy rngTline is the same as before except for using the Range variables instead of Range(<objectname.value>) .

Changes are in bold.

Private Sub cbtnCPExecute_Click()
    Dim rngS As Range
    Dim rngT As Range
    With Me
        Set rngS = Range(.reSource.Value)
        Set rngT = Range(.reTarget.Value)
        If cbxPasteSpecial Then
            rngS.Copy
            rngT.Select
            Application.Dialogs(xlDialogPasteSpecial).Show
        Else
            rngS.Copy rngT
        End If
        .reSource.Value = ""
        .reTarget.Value = ""
        .Hide
    End With
    GoTo EXIT_cbtnCPExecute_Click
ERR_cbtnCPExecute_Click:
    MsgBox prompt:="Not able to perform Copy & Paste Remote.   Check the entries for 'Copy from:' 'and Paste to:'." _
            & "They must be valid range addresses or Named ranges.", _
           Title:="Remote Copy and Paste", _
           Buttons:=vbOKOnly + vbExclamation

EXIT_cbtnCPExecute_Click:
    On Error GoTo 0
End Sub

Keep Target and/or Source

As mentioned before, the code is easily modified (commenting out two lines) to make the form keep the previous entries for the next usage.  There are cases made for keeping one or the other from usage to usage, but it would be rare to copy/paste the same information to the same target.  Covering #2 in the list of new requirements, this allows for clearing all, keeping only one or the other, or keeping both,

Userform Changes

Two checkboxes were added to the userform,


First, there are module level data declarations.  These allow for the next usage after a Cancel to use the most recent successful execution

    Dim strPreviousTarget As String
    Dim strPreviousSource As String
    Dim booUsePreviousTarget As Boolean
    Dim booUsePreviousSource As Boolean
The Click event Sub for the cancel button resets to userform controls to what they had been at the beginning of the cycle
Private Sub cbtnCancel_Click()
    Application.EnableEvents = False
    With Me
        .reSource.Value = strPreviousSource
        .reTarget.Value = strPreviousTarget
        .cbxReUseSource = booUsePreviousSource
        .cbxReUseTarget = booUsePreviousTarget
        .Hide
    End With
    Application.EnableEvents = True
End Sub

The Click event Sub for the Copy/Paste button resets the values for the userform controls.
Private Sub cbtnCPExecute_Click()
    Dim rngS As Range
    Dim rngT As Range
    Application.EnableEvents = False
    With Me
        Set rngS = Range(.reSource.Value)
        Set rngT = Range(.reTarget.Value)
        If cbxPasteSpecial Then
            rngS.Copy
            rngT.Select
            Application.Dialogs(xlDialogPasteSpecial).Show
        Else
            rngS.Copy rngT
        End If
        If Not .cbxReUseSource.Value Then .reSource.Value = ""
        strPreviousSource = reSource.Value
        booUsePreviousSource = .cbxReUseSource.Value
        If Not .cbxReUseTarget.Value Then .reTarget.Value = ""
        strPreviousTarget = reTarget.Value
        booUsePreviousTarget = .cbxReUseTarget.Value
        .Hide
    End With
    GoTo EXIT_cbtnCPExecute_Click
ERR_cbtnCPExecute_Click:
    MsgBox prompt:="Not able to perform Copy & Paste Remote.   Check the entries for 'Copy from:' 'and Paste to:'." _
            & "They must be valid range addresses or Named ranges.", _
           Title:="Remote Copy and Paste", _
           Buttons:=vbOKOnly + vbExclamation
EXIT_cbtnCPExecute_Click:
    On Error GoTo 0
End Sub

Reset and Restore

Requirements #3 and #4 call for changing current settings either back to the default or to the state when the form was last opened.

New Buttons on the form

This calls for the addition of two buttons: Clear and Reset.  These are to be enabled under specfic curcumstances

  • Clear is to be enabled if either of the RefEdit controls have data in them or if any Checkbox controls are on (checked).
  • Reset is to be enabled if  RefEdit or Checkbox controls don't match the values they had when the form was most recently opened. 

Additional Variable

One piece of module-level data needs to be added.  It is needed for re-setting to the state the form was in when most recently opened.  This was not needed previously because the form holds the value from use-to-use.
    Dim booPasteSpecial As Boolean

The value for booPasteSpecial is set in cbtnCPExecute_Click along with booUsePreviousSource and booUsePreviousTarget .

Change to Copy/Paste Click Event Sub

In addition to the previously written code, the Click event subroutine for the Copy/Paste button needs to capture the current value of the checkbox for using Paste Special so it can be used for Reset button function during the next use of the form.  The RefEdit fields and other checkboxes are already being captured per the Keep Target and/or Source functionality

Private Sub cbtnCPExecute_Click()
    Dim rngS As Range
    Dim rngT As Range
    Application.EnableEvents = False
    With Me
        Set rngS = Range(.reSource.Value)
        Set rngT = Range(.reTarget.Value)
        If cbxPasteSpecial Then
            rngS.Copy
            rngT.Select
            Application.Dialogs(xlDialogPasteSpecial).Show
        Else
            rngS.Copy rngT
        End If
        If Not .cbxReUseSource.Value Then .reSource.Value = ""
        strPreviousSource = reSource.Value
        booUsePreviousSource = .cbxReUseSource.Value
        If Not .cbxReUseTarget.Value Then .reTarget.Value = ""
        strPreviousTarget = reTarget.Value
        booUsePreviousTarget = .cbxReUseTarget.Value
        booPasteSpecial = .cbxPasteSpecial.Value
        .Hide
    End With
    GoTo EXIT_cbtnCPExecute_Click
ERR_cbtnCPExecute_Click:
    MsgBox prompt:="Not able to perform Copy & Paste Remote.   Check the entries for 'Copy from:' 'and Paste to:'." _
            & "They must be valid range addresses or Named ranges.", _
           Title:="Remote Copy and Paste", _
           Buttons:=vbOKOnly + vbExclamation
EXIT_cbtnCPExecute_Click:
    On Error GoTo 0
End Sub

Change to Cancel Click Event

Now that we're capturing the value of the Checkbox for PasteSpecial, it needs to be included in the Sub for the Cancel CommandButton.  (hmmm...maybe this should have been in previous step...oh, well, onward and updward.)
Private Sub cbtnCancel_Click()
    Application.EnableEvents = False
    With Me
        .reSource.Value = strPreviousSource
        .reTarget.Value = strPreviousTarget
        .cbxReUseSource = booUsePreviousSource
        .cbxReUseTarget = booUsePreviousTarget
        .cbxPasteSpecial = booPasteSpecial
        .Hide
    End With
    Application.EnableEvents = True
End Sub

Clearing Values and Resetting to Original Values with CommandButton Click events

These are the crux of what I am trying to accomplish with this version: Clearing the values or making them revert to what they were the last time the form was open.  

Private Sub cbtnClear_Click()
    Application.EnableEvents = False
    With Me
        .reSource.Value = ""
        .cbxReUseSource.Value = False
        .reTarget.Value = ""
        .cbxReUseTarget.Value = False
        .cbxPasteSpecial.Value = False
        .Hide
    End With
    Application.EnableEvents = True
End Sub

Private Sub cbtnReset_Click()
    Application.EnableEvents = False
    With Me
        .reSource.Value = strPreviousSource
        .reTarget.Value = strPreviousTarget
        .cbxReUseSource = booUsePreviousSource
        .cbxReUseTarget = booUsePreviousTarget
        .cbxPasteSpecial = booPasteSpecial
    End With
    Application.EnableEvents = False
End Sub

Wait a gosh durn minute!

The code for cbtnReset_Click is almost identical to the code for cbtnCancel_Click.  The only difference is a .Hide.  Therefore, I put the common code in a separate Sub and shortened each <CommandButton>_Click Sub by several lines.

Private Sub cbtnCancel_Click()
    Call ResetFormValues
    Me.Hide
End Sub

Private Sub cbtnReset_Click()
    Call ResetFormValues
End Sub

Private Sub ResetFormValues()
    Application.EnableEvents = False
    With Me
        .reSource.Value = strPreviousSource
        .reTarget.Value = strPreviousTarget
        .cbxReUseSource = booUsePreviousSource
        .cbxReUseTarget = booUsePreviousTarget
        .cbxPasteSpecial = booPasteSpecial
        .Hide
    End With
    Application.EnableEvents = True
End Sub

Enabling and Disabling the Clear and Reset buttons

Every form should only have available to the user those controls that when the user "touches" it there is some effect.  If the Clear and Reset buttons are always enabled then the user may believe that by clicking on them something should happen.  This is logical, considering the vast majority of applications follow the principle stated 

Functions to Check Whether to Enable/Disable Clear and Reset Buttons

Two functions are added, one to check if the form has data (fn_booFormHasData), the other if the data has changed (fn_booFormHasChangedValues).   These will be invoked by a Sub to enable/disable the Clear and Reset buttons
  • fn_booFormHasData
    • Looks at the RefEdit  controls  to see if there is data in them, i.e. the length is greater than 0.
    • Looks at checkboxes to see if any are turn on.
  • fn_booFormHasChangedValues looks at RefEdit and checkboxes to see if they are different from the values in them when the form was most recently opened.
Function fn_booFormHasData() As Boolean
    With Me
        If Len(.reSource.Value) > 0 _
                Or Len(.reTarget.Value) > 0 _
                Or .cbxPasteSpecial.Value _
                Or .cbxReUseSource.Value _
                Or .cbxReUseTarget.Value Then
            fn_booFormHasData = True
        Else
            fn_booFormHasData = False
        End If
    End With
End Function

Function fn_booFormHasChangedValues()
    With Me
        If strPreviousSource <> reSource.Value _
                Or booUsePreviousSource = .cbxReUseSource.Value _
                Or strPreviousTarget <> reTarget.Value _
                Or booUsePreviousTarget <> .cbxReUseTarget.Value _
                Or booPasteSpecial <> .cbxPasteSpecial.Value Then
            fn_booFormHasChangedValues = True
        Else
            fn_booFormHasChangedValues = False
        End If
    End With
End Function

Subroutine to Enable/Disable Clear and Reset Buttons

This is another case of isolating common code in its own procedure.  The functions above are used by EnableDisableButtons to control whether the Clear and Reset buttons are enabled.

Sub EnableDisableButtons()
    With Me
        If fn_booFormHasData Then
            .cbtnClear.Enabled = True
        Else
            .cbtnClear.Enabled = False
        End If
        If fn_booFormHasChangedValues Then
            .cbnReset.Enabled = True
        Else
            .cbnReset.Enabled = False
        End If
    End With
End Sub

Checkbox Change Event Subroutines

These Subs simply call EnableDisableButtons each time one of the checkboxes are changed from ON to OFF or OFF to ON.

Private Sub cbxPasteSpecial_Change()
    Call EnableDisableButtons
End Sub

Private Sub cbxReUseSource_Change()
    Call EnableDisableButtons
End Sub

Private Sub cbxReUseTarget_Change()
    Call EnableDisableButtons
End Sub

Using Checkbox Enter Event as proxies for RefEdit Exit Event

For similar types of input controls (e.g. TextBoxes and ComboBoxes)  the <control>_Exit event Sub can be used to check if the field has changed, is empty, or otherwise validate it.  Because the RefEdit events are unreliable (per a whole slew of people more knowledgeable than I), I decided not to use any reSource_<event> or reTarget_<event> Subs. Instead, based on what I read from these more knowledgeable people, I used the Enter event subs for the checkbox controls.

NOTE:  According to this Google Search, there are alternatives to using RefEdit controls.  Because I am trying to get this project done quickly, am satisfied with the results I am getting, and am a bit leery of Class Modules because of my own skill level, I'll save using an alternative for later.

Before relying on this, it is important to establish the proper flow of tabbing from one field to the next.  Do this by selecting Tab Order from the VBE's View menu.
Figure x - Setting Tab Order
Now add the Enter event driven subs.
Private Sub cbxPasteSpecial_Enter()
    Call EnableDisableButtons
End SubPrivate
 Sub cbxReUseSource_Enter()
    Call EnableDisableButtons
End SubPrivate
 Sub cbxReUseTarget_Enter()
    Call EnableDisableButtons
End Sub

Known shortcoming...and two fixes

The only user action known at this point that does not trigger a call of EnableDisableButtons when it should, would be if the user moused from one of the RefEdit controls. Tabbing from field to field works fine, and so does the Enter key when the focus is on one of the RefEdit controls. 

Use the UserForm_MouseMove Event, Fix Number 1

The following overcomes this shortcoming by using the MouseMove event for the userform.  Whenever the mouse is moved across the form but between its controls, UserForm_MouseMove triggers.  The sub for the UserForm_MouseMove event then calls the same Sub as before. 

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call EnableDisableButtons
End Sub

Limit the MouseMove Event , an improvement to Fix Number 1

Using the UserForm_MouseMove event has the disadvantage of constantly triggering EnableDisableButtons, whenever the mouse is moving over the form, as long as it is not over a control. This may cause some which may cause some performance issues.

The objective of this improvement is to eliminate much of the form's area from the event and trip the mouse movement only to those areas where it might leave the RefEdit controls.  To limit the area where the MouseMove event is triggered, each RefEdit control is put inside it's own Frame control.
Figure x - Frames added to Control MouseMove Area (Design view)
The steps are
  • Expand the form to allow space to work.
  • Add Frame controls (frRefEditSource and frRefEditTarget)
    NOTE: Two frames were used instead of one to preserve the tab order
  • Move RefEdit controls inside their respective Frames
  • Re-size Frames so that a bare minimum of non-border shows around each RefEdit control. 
  • Move Frames to carry the RefEdit controls to their proper places
  • Format Frames be invisible when the form was shown
  • Modify Tab Order 

Code for Frames' MouseMove Events

The following Subs solve the problem of mousing out of the RefEdit controls without calling EnableDisableButtons excessively

Private Sub frRefEditSource_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call EnableDisableButtons
End Sub

Private Sub frRefEditTarget_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Call EnableDisableButtons
End Sub

Where are we so far?

At this point the functionality has changed in the following ways
  1. Instead of a simple Paste, there is now an option to Paste Special
  2. Users can choose to keep settings, e.g. for the ranges, to be kept from one use to the next or cleared in between,
  3. The source and target fields and all checkboxes clear-able in one mouse-click. 
  4. The user can reset the form to how it looked when the user started this time, e.g. re-populate the range references.