Solved: Customizing the Copy Method with a userform

[BoatwrenchV8]

Hello all,
I am trying to create a modification to the Copy method and so far it has not worked properly :banghead: , and need your expert input.

I frequently copy data from one opened word file (Data Source Document) and paste it into a different word file (Target Document). When I am finished I want to have the data that I copied from the Data Source be highlighted so it is vividly clear what data I copied, which will be a help to me in the future if I have to go back and and verify what was copied. There would not be any highlighting of the pasted data in the Target Document.

What I have so far is a custom userform with 2 buttons and a label. Of the 2 buttons, one is a Toggle Button (TglBtn_On_Off) and the other is a Command Button (CB_Quit). The toggle button is initialized as true and is disabled. It is there for decoration only to show the control and the macro are operational. The label (Lbl_Status) is there also for decoration to show the control and macro are operational, but will have a future use at some point.

Below is the code for the userform (HighlighterAppVer5) and the code for in the module that contains the call to the userform (CopyHiglighter) and the hijack of EditCopy.

user form code (HighLighterV5)

Option Explicit

Public Sub CB_Quit_Click()
CB_Quit = True
TglBtn_On_Off.Value = False
Unload Me
End Sub

Private Sub TglBtn_On_Off_Click()
End Sub
Public Sub UserForm_Initialize()
Lbl_Status.Caption = "Highlighter Active"
TglBtn_On_Off.Value = True
End Sub


Module with calling procedure and hijack



Sub CopyHiglighter()
HighLighterV5.Show
End Sub


Sub EditCopy()
'
' EditCopy Macro
' Copies the selection and puts it on the Clipboard
'

Selection.copy

If HighLighterV5.TglBtn_On_Off.Value = True Then
Selection.Range.HighlightColorIndex = wdPink
End If
End Sub




The problem is: When quit the control, the highlighting does not stop occurring when I copy and I have NO idea why.

Please advise.

Thank you,
Rich

[akuini]

Assuming there are only two document open: 'source document' & 'target document'.
First, in the 'target document' you put the cursor where you want the text to be pasted.
Second, open the 'source document' and select the text to be copied.
Third, use the macro below:

Sub tryPaste()

Dim Rng As Range
Dim nameDoc As String
Dim i As Long
Dim j As Long

nameDoc = ActiveDocument.Name
Selection.Copy

If Documents(2).Name = nameDoc Then
Documents(1).Activate
Else
Documents(2).Activate
End If

Set Rng = Selection.Range
i = Rng.Start
Selection.Paste
Set Rng = Selection.Range
Rng.Start = i
Rng.HighlightColorIndex = wdYellow

End Sub

[akuini]

Sorry, I think this one is better than what I post before

Sub tryPaste1()

Dim Rng As Range
Dim nameDoc As String
Dim i As Long

nameDoc = ActiveDocument.Name
Set Rng = Selection.Range
Rng.Copy

If Documents(2).Name = nameDoc Then
Documents(1).Activate
Else
Documents(2).Activate
End If

Set Rng = Selection.Range
Rng.Paste
Rng.HighlightColorIndex = wdYellow

End Sub

[fumei]

1. why do you have the declared Integer i? You do not use it.

2. VBA no longer uses Integer. It always converts them to Long, so we no longer declare Integers.

3. There is no need to use any Activate instruction, especially whyen using anges.

4. The OP wants to use a userform...although I am not sure why.

All that being said, perhaps it would in fact be a good idea to review what the real actions need to be. AND what is happening right now. So...

Are you selecting the text to be copied THEN firing HighLighterV5? OK, but now you have focus on the userform. And nothing happens there. So please describe exactly the steps, and in what order. The reason I ask is that akuini may have a good idea and build the whole thing into one macro. Do you really even need a userform?

[BoatwrenchV8]

Akuini,
I will try your macro out tonight but I need the copy method toggled on or off. That is why I wanted to use a userform.
Fumei,
The reason I wanted to use a userform is to provide a toggle, a visual on and off switch to the modified copy method.
Additional background.
The Data Source Document is 400+ pages long and contains various lab test (ALT, AST, BUN, Basophils, Monocytes, etc.) data in word tables usually in a .rtf format. Each page contains a header and footer with table number data, lab test info and version info. The body of the page contains the data table itself (usually 4 or more columns wide by however many rows long to fit the page) with the lab data I need to extract.
The Target Document is a document that contains a word table that is an empty shell (or a partially empty shell) that has to be populated with specific data from the Data Source Document.
Not all of the data from the Data Source Document will be used to populate the Target Document.

User’s actions.
User would open the Data Source Document and the Target Document and either arrange them vertically on one monitor or maximized on 2 monitors, one document in each monitor (that is what I do, use 2 monitors). Those are the only 2 documents open on the computer. The user would start with the Target Document and look at what lab test values are required.
User would click a button or keyboard shortcut to 1) display the copy highlighter userform and 2) by displaying the copy highlighter form, enable the alterative copy method.
The copy highlighter userform is not set to be modal.
The user would move the mouse over to the Data Source Document, select the needed data from the document, Copy the selection using standard methods AND by doing so, the selection would become highlighted AFTER the selection is copied. The user would move the mouse to the Target Document and paste using standard methods.
The copied data is highlighted when the user copies it. The pasted data is not highlighted because highlighting took place after the copy method.

This entire process would continue back and forth until the user was finished copying everything needed to complete the table in the Target Document, all the while the userform is still visibly on. When the user is finished, the Data Source Document will have various pieces of data that are highlighted in it and it will be vividly clear what was and wasn’t copied into the Target Document. The copy method highlighter can be turned off now by clicking the Quit button and the copy method would go back to normal operation.

The alternative method I have been doing was to copy, then to highlight, then to paste. If you are doing that 20 times, it is not a big deal. If you are doing that for a huge table that needs parts and pieces picked out for it and is inconsistent so a macro cannot be created to automate the process, it is a major pain in the neck. The idea was/is to combine the copy and highlight step into one that can be toggled on or off with a visible on off button. I am going to be the test subject for this and visible indicator / toggle is in the design spec.

I think my fundamental lack of understanding is with the userform and its environment. Please let me know if you need further clarification of what I am trying to do.

Thanks,
Rich

[fumei]

Whoa. WHICH document has the userform module???

[BoatwrenchV8]

Fumei,
The userform module is located in the normal template. I cannot put any macros in either of the 2 documents. I could put it in an add-in though, just not in anything that I would be sending off to someone when i am finished building the document.

[akuini]

Sorry, I thought you wanted to highlight the text in the target document.
So if you want to highlight after copying the text, why don't try something simple like:


Sub tryHilite()
Dim Rng as Range
Set Rng = Selection.Range
Rng.Copy
Rng.HighlightColorIndex = wdYellow
End Sub


then assign the macro to a shortcut, says "Ctrl+E", then instead of using "Ctrl+C" to copy the text, you can just use "Ctrl+E".
And if you want the "normal copy" just use "Ctrl+C" as usual.

[BoatwrenchV8]

That would would work but in this case, it won't because of a coworker I have. I need to have a visual button or visual clue that the "app" is active. If it was just for me, I would use your much simpler method. :)

[fumei]

And this is why giving full information is helpful.

From the original post.

highlighting does not stop occurring when I copy and I have NO idea why.

and finally:

The userform module is located in the normal template.

If the modified (hijacked) EditCopy is in normal (as it appears to be, now that I really look at the image you posted), then it applies for all documents that use EditCopy...that is, that use Copy. And what IS the EditCopy that is used?
Sub EditCopy()
'
' EditCopy Macro
' Copies the selection and puts it on the Clipboard
'

Selection.copy

If HighLighterV5.TglBtn_On_Off.Value = True Then
Selection.Range.HighlightColorIndex = wdPink
End If
End Sub then yup: "highlighting does not stop occurring when I copy"

[akuini]

I think you should use a boolean variable declared public in code module section, says "bHilite".
' in code module

Option Explicit
Public bHilite As Boolean
Sub CopyHiglighter()
HighLighterV5.Show
End Sub

Sub EditCopy()
' EditCopy Macro
' Copies the selection and puts it on the Clipboard
Selection.Copy
If bHilite = True Then
Selection.Range.HighlightColorIndex = wdPink
End If

In the form module
Public Sub CB_Quit_Click()
Unload Me
End Sub

Public Sub UserForm_Initialize()
Lbl_Status.Caption = "Highlighter Active"
bHilite = True
End Sub

Private Sub UserForm_Terminate()
bHilite = False
End Sub

[BoatwrenchV8]

then yup: "highlighting does not stop occurring when I copy"

Fumei, why doesn't it stop highlighting when
If HighLighterV5.TglBtn_On_Off.Value = True Then ...

should be either False or <undefined>? Execution shouldn't enter the If block, why does it??


And this is why giving full information is helpful.

From the original post.
http://www.vbaexpress.com/forum/images/quotes/quot-top-left.gifQuote:http://www.vbaexpress.com/forum/images/quotes/quot-top-right.gifhttp://www.vbaexpress.com/forum/images/quotes/quot-top-right-10.gifThe userform module is located in the normal template.

It has been my experience to never send a word document with any macros in it unless they too are geeks AND are expecting the file to have a macro in it. Otherwise, they get a macro warning and various stages of panic / chaos ensue. Therefore, I made an assumption, which I shouldn't have, that inclusion of the macro would be on my local copy of normal.dotm (or in the future an add-in) only and not be seen or known about by anyone else.



Akuini,

I think you should use a boolean variable declared public in code module section, says "bHilite".
' in code module


YES!!! :bow: That works PERFECT and as intended. Thank you so much. I have been tinkering with it and trying to figure out why it works and why my version did not. Also I am still unsure why in my version, the macro would continue to highlight when it shouldn't have.

I will write out how I think your code works and if you would be so kind, please correct me if needed.

:)

[BoatwrenchV8]

Here are the renamed, updated macro code and userform. Message boxes are in the code only for me to help understand the sequence of events. Please let me know if my understanding of how this operates is correct or not, as userforms are new to me.
Thanks,
Rich

Macro Code

Option Explicit
' Global / Public Declarations
'
'Active status flag for HighLighterV1 Userform
'
' If bCopyHighligherStatus = True, then the
' CopyHighlighter App and userform HighLighterV1
' are active and all copied content will be
' highlighted.
'
' If bCopyHighligherStatus = False, then the
' CopyHighlighter App and userform HighLighterV1
' are NOT active and all copied content will NOT
' be highlighted.
Public bCopyHighligherStatus As Boolean
'
Sub CopyHiglighter()
'Message box line below is for debugging only
MsgBox ("Highliter app has been launched")
HighLighterV1.Show
End Sub

Sub EditCopy()
'
' EditCopy Macro Hijacked and Modified
' Copies the selection and puts it on the Clipboard.
' If the Highlighter App has been enabled, the copied
' selection is highlighted after it is copied to the
' clipboard.
'
' bCopyHighligherStatus = Global/Public boolean Variable
' See explanation of this variable above in
' Global / Public Declarations section.

Selection.copy

If bCopyHighligherStatus = True Then
Selection.Range.HighlightColorIndex = wdPink
'Message box line below is for debugging only
MsgBox ("Highlighter is ON")
End If
End Sub



Userform Code

Option Explicit
'
'This userform is called from the "HighlighterAppVer1" module
' and will set the Global / Public status flag
' variable "bCopyHighligherStatus" based on
' if this userform is On/Active or Off/Quit.
'
' If bCopyHighligherStatus = True, then the
' CopyHighlighter App and userform HighLighterV1
' are active and all copied content will be
' highlighted.
'
' If bCopyHighligherStatus = False, then the
' CopyHighlighter App and userform HighLighterV1
' are NOT active and all copied content will NOT
' be highlighted.
'
'The state of the bCopyHighligherStatus flag determines
' if the normal copy method is executed or if the modified
' copy method is executed.
'
Private Sub CB_Quit_Click()
'Message box line below is for debugging only
MsgBox "CB_Quit_Click, before 'unload.me' statement,inside CB_Quit_Click code"
Unload Me
'Message box line below is for debugging only
MsgBox ("CB_Quit_Click, after 'unload.me' statement, inside CB_Quit_Click code")
End Sub

Private Sub TglBtn_On_Off_Click()
'This event should never fire in the code as it is
' because the toggle button "TglBtn_On_Off"
' is disabled.
'
'Message box line below is for debugging only
MsgBox ("Click Event")
End Sub

Private Sub UserForm_Initialize()
Lbl_Status.Caption = "Highlighter Active"
bCopyHighligherStatus = True
'Message box line below is for debugging only
MsgBox ("Initialize Event")
End Sub

Private Sub UserForm_Terminate()
bCopyHighligherStatus = False
'Message box line below is for debugging only
MsgBox ("Terminate Event")
End Sub


My theory of operation:

Word is launched and the global variable "bCopyHighligherStatus" is created
User executes "CopyHiglighter" which is contained in the code module "HighlighterAppVer1"
Userform "HighLighterV1" is called, isloaded into memory and execution transferrs to the Userform
The userform's initialize event is fired and loads the inital settings to be displayed and sets boolean variable "bCopyHighligherStatus" to True.
The userform is displayed on the screen with the settings
Control returns to "CopyHiglighter" which is contained in the code module "HighlighterAppVer1" and execution runs until an end sub statement is reached.
User copies/pastes as required until copy and highlight function is no longer needed.
On the userform, the user clicks the "OFF/Quit" command button "CB_Quit" to quit the copy highlighter
The userform has focus and execution resumes in the userform.
The CB_Quit_Click event is fired and execution enters the CB_Quit_Click procedure
Inside the CB_Quit_Click procedure is an "Unload Me" statement which deletes the userform from memory
Calling the "Unload Me" statement causes the Terminate event to be fired. The boolean variable "bCopyHighligherStatus" to False.
After the Terminate event fires and completes, control is returned to the "CB_Quit" procedure until it ends at the end sub statement.
The userform is entirely removed from memory and word goes back to normal operation.Is my theory accurate? What is the difference between using Unload Me (deleting the form from memory) and terminate (also deleting the form from memory)? Is the difference simply Unload Me kills it entirely but Terminate allows setting some options (like was done here in the above) upon termination?

And

Why didn't my version work by checking the value of the toggle button? Is it because the value of the toggle button was lost and out of scope when the userform was removed from memory? If so, then in my mind wouldn't the toggle button's value either be defaulted to false or <undefined> and never allow execution of the highlighter?

I am sure I am overlooking the obvious.

Thanks again,
Rich

[fumei]

"toggle button's value either be defaulted to false"

It is.

[akuini]

Hi, BoatwrenchV8
Glad to hear my code works well for you.
Actually you understand more than I do about how the process works.


What is the difference between using Unload Me (deleting the form from memory) and terminate (also deleting the form from memory)? Is the difference simply Unload Me kills it entirely but Terminate allows setting some options (like was done here in the above) upon termination?

Using Unload Me triggers UserForm_Terminate event. The reason I put the code "bHilite = False" in the "Private Sub UserForm_Terminate()" not in the "Public Sub CB_Quit_Click()" is that there are two ways user can quit the form :
First, by clicking " CB_Quit" button.
Second, by clicking the default exit button (the x button at the top right of the form). And clicking this will unload the form.
So if I put the code "bHilite = False" in the "Public Sub CB_Quit_Click()" & the user choose the second way to quit the form, then the code "bHilite = False" won't be processed. So the better way is to 'intercept' the process with UserForm_Terminate event.


Why didn't my version work by checking the value of the toggle button? Is it because the value of the toggle button was lost and out of scope when the userform was removed from memory? If so, then in my mind wouldn't the toggle button's value either be defaulted to false or <undefined> and never allow execution of the highlighter? Sorry, I don't understand that either. Actually I tried your HighlighterAppVer5 (the first one you posted) and it didn't work at all. I mean the text didn't highlited at all after I run the macro and after I quit. Maybe I missed something. Maybe you could send the form module (.frm & .frx) and the code module(.bas) by using the attachment .

[BoatwrenchV8]

Fumei and Akuini,

Thank you both for your input and help so far.

I had to recreate the old version. The zip file contains 3 files: CopyHighlighterModule.bas, HighLighterV5.frm and HighLighterV5.frx.

7678

After putting the userform and the code module in your normal template, create a word file with some random text in it. Then switch to the VBA IDE and run the macro using F5 with the cursor at the line "Sub CopyHiglighter()". The macro should do its thing and show the userform. Move the userform out of the way and copy some random text in your test document. It should highlight everyting you copy. OK, great. Now click the "Off/Quit" button. The highlighter should be off and the boolean value of TglBtn_On_Off.Value should be False. All further copying should not be highlighted in pink BUT IT STILL DOES and I STILL have no idea why. If TglBtn_On_Off.Value = False OR if TglBtn_On_Off.Value is undefined because the module in CopyHighlighterModule.bas knows nothing about it, then why does the line "Selection.Range.HighlightColorIndex = wdPink" inside the If statementit still get executed? Truely do not understand why it does.

Sub EditCopy()
'
' EditCopy Macro
' Copies the selection and puts it on the Clipboard
'

Selection.copy

If HighLighterV5.TglBtn_On_Off.Value = True Then
Selection.Range.HighlightColorIndex = wdPink
End If
End Sub

[fumei]

All further copying should not be highlighted in pink BUT IT STILL DOES and I STILL have no idea why.But I have TOLD you why!

Because it is in Normal!

Watch. Start fresh (with all your stuff in normal).

Go into the VBE and go to your EditCopy. Remember this is BEFORE you ever execute the .Show procedure. Start fresh. You have NEVER showed the userform yet.

Now, Step through EditCopy. Do you know how to Step through code? If not, start learning right away because it is the first thing you should do when trying to figure something out that is acting unexpected.

Use F8. This is the Step command. It executes the procedure one line at a time. So...what happens.

Ah....you will see that it goes like this. Remember, you have NOT shown the userform yet.

Step 1 the Selection.copy instruction
Step 2 the IF statement

and...yes, it goes(Step 3) to the userform Initialize event - EVEN THOUGH YOU HAVE NOT LOADED THE USERFORM.

Why? Because it tries to get the Value of your togglebutton...and the only way to do that is to check. So it does, by executing Initialize. Which makes your togglebutton = True.

So EditCopy ALWAYS makes the toggle = true, thus...

Step 4 The IF statement comes out True, and the highlight is applied. Even though you have NOT done a Show of the userform.

Because it is in Normal.

Is there a solution? Most definitely.

This is the module (it can still be in Normal)

Option Explicit
Public bolMakeHighlight As Boolean

Sub CopyHiglighter()
HighLighterV5.Show
End Sub


Sub EditCopy()
'
' EditCopy Macro
' Copies the selection and puts it on the Clipboard
'

Selection.copy

If bolMakeHighlight = True Then
Selection.Range.HighlightColorIndex = wdPink
End If
End Sub
And this is the userform code module:Option Explicit

Public Sub CB_Quit_Click()
bolMakeHighlight = False
Unload Me
End Sub

Public Sub UserForm_Initialize()
Lbl_Status.Caption = "Highlighter Active"
bolMakeHighlight = True
End Sub

Not sure why you had that CB_Quit = True in the exit button.

Now because EditCopy is NOT checking the value of the toggle (but checking the value of the Public boolean bolMakeHighlight), it works.

When you call the userform, the boolean becomes True, and EditCopy does the highlight. When you exit the userform the boolean becomes False, and so EditCopy does NOT do the highlight.

I would dump that togglebutton on your userform.

[BoatwrenchV8]

Um... WOW...


But I have TOLD you why!

Because it is in Normal!


Really??

Yes, I do know how to step thru code using F8. Did that. Again - Userforms are new to me and I did not understand why the value inside the If statement was true at all times. Now I understand ...


...Why? Because it tries to get the Value of your togglebutton...and the only way to do that is to check. So it does, by executing Initialize. Which makes your togglebutton = True.

So EditCopy ALWAYS makes the toggle = true, thus...

...because you have explained it to me.

Thank you for your explanation Fumei.

I agree with,
I would dump that togglebutton on your userform., and it will be going bye bye very soon in the next (working!!) version.

May I dare ask, would it be better for me to put the code and userform in an Add-In template? Then if I add to the design later, I can just send my coworkers the Add-In and their local versions of normal.dotm are not disturbed. Are projects (the macro code) viewable in an Add-In template by going to the VBA IDE or does the Add-In template itself have to be opened in order to view the project's macro code?

Thank you for your help and clarification.

[fumei]

Are projects (the macro code) viewable in an Add-In template by going to the VBA IDE or does the Add-In template itself have to be opened in order to view the project's macro code?The latter. Only documents/templates that are OPEN can have code viewed. Therefore if the addin file is not open, no the code can not be viewed.

I have to add that akuini had previously suggested the solution when they suggested the use of a Public boolean variable.

[BoatwrenchV8]

BoatwrenchV8: Are projects (the macro code) viewable in an Add-In template by going to the VBA IDE or does the Add-In template itself have to be opened in order to view the project's macro code?

Fumei: The latter. Only documents/templates that are OPEN can have code viewed. Therefore if the addin file is not open, no the code can not be viewed.


Thank you for verifying that.



I have to add that akuini had previously suggested the solution when they suggested the use of a Public boolean variable.
Yes, his code worked as intended and am using it now. Now I know why my code didn't work. I always want to know what makes thing tick. :)

[akuini]

Hi, fumei.
Clearly you have a deep understanding about how the macro works.



and...yes, it goes(Step 3) to the userform Initialize event - EVEN THOUGH YOU HAVE NOT LOADED THE USERFORM.

Why? Because it tries to get the Value of your togglebutton...and the only way to do that is to check. So it does, by executing Initialize. Which makes your togglebutton = True.

I never thought of that. Now I understand, thanks to you.

[fumei]

The key piece of knowledge is that in Normal VBA parses ALL modules. So when it gets the instruction "What is the value of BlahBlahToggleButton", EVEN THOUGH that control is not in the current module (it is in the userform), it WILL go through everything in the VBProject trying to find it. It will only error out (via Option Explicit) if it can not find it in ANY module (not just the current one).

Thus, it does find it (in the userform module), but it still can not find out the value until it executes Initialize - EVEN THOUGH the userform is not initialized.

Nevertheless it does in fact execute Initialize (but without actually doing a Show!), in order to get the value. In this case that action made the togglebutton = True, and thus everytime EditCopy is executed (whether the userform is loaded, or not) the toggle is True, and thus the highlight is applied.

This can be easily seem using Step (which ahould have been done right at the start...


Even by me.