userform help (having trouble passing info from form to macro)

cyraxote · #1 09-05-2015, 09:43 AM

The purpose of the form and macro is to allow the user to search for text highlighted in one color and change it to another color or no color.

This is the form:

This is the code:

Code:

Option Explicit

Private Sub cmdOK_Click()

    Dim SearchColor As String
    Dim ReplaceColor As String

    If optYellow1 = True Then SearchColor = wdYellow
    If optBrightGreen1 = True Then SearchColor = wdBrightGreen
    If optTurquoise1 = True Then SearchColor = wdTurquoise
    If optPink1 = True Then SearchColor = wdPink
    If optBlue1 = True Then SearchColor = wdBlue
    If optRed1 = True Then SearchColor = wdRed
    If optDarkBlue1 = True Then SearchColor = wdDarkBlue
    If optTeal1 = True Then SearchColor = wdTeal
    If optGreen1 = True Then SearchColor = wdGreen
    If optViolet1 = True Then SearchColor = wdViolet
    If optDarkRed1 = True Then SearchColor = wdDarkRed
    If optDarkYellow1 = True Then SearchColor = wdDarkYellow
    If optGray501 = True Then SearchColor = wdGray50
    If optGray251 = True Then SearchColor = wdGray25
    If optBlack1 = True Then SearchColor = wdBlack
    
    If optYellow2 = True Then ReplaceColor = wdYellow
    If optBrightGreen2 = True Then ReplaceColor = wdBrightGreen
    If optTurquoise2 = True Then ReplaceColor = wdTurquoise
    If optPink2 = True Then ReplaceColor = wdPink
    If optBlue2 = True Then ReplaceColor = wdBlue
    If optRed2 = True Then ReplaceColor = wdRed
    If optDarkBlue2 = True Then ReplaceColor = wdDarkBlue
    If optTeal2 = True Then ReplaceColor = wdTeal
    If optGreen2 = True Then ReplaceColor = wdGreen
    If optViolet2 = True Then ReplaceColor = wdViolet
    If optDarkRed2 = True Then ReplaceColor = wdDarkRed
    If optDarkYellow2 = True Then ReplaceColor = wdDarkYellow
    If optGray502 = True Then ReplaceColor = wdGray50
    If optGray252 = True Then ReplaceColor = wdGray25
    If optBlack2 = True Then ReplaceColor = wdBlack
    If optNoColor = True Then ReplaceColor = wdNoHighlight
    
    DoColorChange

End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub DoColorChange()

Dim oDoc As Document
Dim oRng As Range
Dim intPosition As Long
'Dim SearchColor As String
'Dim ReplaceColor As String

'SearchColor = wdYellow
'ReplaceColor = wdDarkBlue

Set oDoc = ActiveDocument
Set oRng = oDoc.Range

With oRng.Find
    .Highlight = True
    .Forward = True
    Do While oRng.Find.Execute
        If oRng.HighlightColorIndex = SearchColor Then
           oRng.HighlightColorIndex = ReplaceColor
        End If
        intPosition = oRng.End
        oRng.Start = intPosition
    Loop
End With

End Sub

The DoColorChange macro works fine when it's not in the form and I feed it the info with the red statements above (commented out for the form code). When I use it in the form, I get one of two errors:

1) If the Dim statements for SearchColor and ReplaceColor in DoColorChange are commented out (as above), I get "Compile error: Variable not defined."

2) If those Dim statements are active, I get "Run-time error '13': Type mismatch.

I'm not really sure where to define those variables (OK button code? macro?), what to define them as (since there's no type mismatch outside the form), and how to get the information from the form to the macro.

If I put a breakpoint at

If optNoColor = True Then ReplaceColor = wdNoHighlight

I can hover the mouse and see that the variables for the colors I select in the form are true, and the others are false, so I'm fairly certain that code is OK.

I've been fiddling with this for hours, so I think I'm officially stumped.

Any help would be greatly appreciated.

gmayor · #2 09-05-2015, 09:19 PM

Pass the values from the form to the macro e.g. as follows and unload the form when you have finished with it either as here or in the calling macro.

Code:

Private Sub cmdOK_Click()

    Dim SearchColor As Long
    Dim ReplaceColor As Long

    If OptYellow1 = True Then SearchColor = wdYellow
    If optBrightGreen1 = True Then SearchColor = wdBrightGreen
    If optTurquoise1 = True Then SearchColor = wdTurquoise
    If optPink1 = True Then SearchColor = wdPink
    If optBlue1 = True Then SearchColor = wdBlue
    If optRed1 = True Then SearchColor = wdRed
    If optDarkBlue1 = True Then SearchColor = wdDarkBlue
    If optTeal1 = True Then SearchColor = wdTeal
    If optGreen1 = True Then SearchColor = wdGreen
    If optViolet1 = True Then SearchColor = wdViolet
    If optDarkRed1 = True Then SearchColor = wdDarkRed
    If optDarkYellow1 = True Then SearchColor = wdDarkYellow
    If optGray501 = True Then SearchColor = wdGray50
    If optGray251 = True Then SearchColor = wdGray25
    If optBlack1 = True Then SearchColor = wdBlack
    
    If optYellow2 = True Then ReplaceColor = wdYellow
    If optBrightGreen2 = True Then ReplaceColor = wdBrightGreen
    If optTurquoise2 = True Then ReplaceColor = wdTurquoise
    If OptPink2 = True Then ReplaceColor = wdPink
    If optBlue2 = True Then ReplaceColor = wdBlue
    If optRed2 = True Then ReplaceColor = wdRed
    If optDarkBlue2 = True Then ReplaceColor = wdDarkBlue
    If optTeal2 = True Then ReplaceColor = wdTeal
    If optGreen2 = True Then ReplaceColor = wdGreen
    If optViolet2 = True Then ReplaceColor = wdViolet
    If optDarkRed2 = True Then ReplaceColor = wdDarkRed
    If optDarkYellow2 = True Then ReplaceColor = wdDarkYellow
    If optGray502 = True Then ReplaceColor = wdGray50
    If optGray252 = True Then ReplaceColor = wdGray25
    If optBlack2 = True Then ReplaceColor = wdBlack
    If optNoColor = True Then ReplaceColor = wdNoHighlight
    
    DoColorChange SearchColor, ReplaceColor
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub DoColorChange(SearchColor As Long, ReplaceColor As Long)

Dim oDoc As Document
Dim oRng As Range
Dim intPosition As Long

Set oDoc = ActiveDocument
Set oRng = oDoc.Range

With oRng.Find
    .Highlight = True
    .Forward = True
    Do While oRng.Find.Execute
        If oRng.HighlightColorIndex = SearchColor Then
           oRng.HighlightColorIndex = ReplaceColor
        End If
        intPosition = oRng.End
        oRng.Start = intPosition
    Loop
End With

End Sub

cyraxote · #3 09-05-2015, 11:08 PM

I'll give that a try.

As you can see, I borrowed some of your code from the other macro.

One question: is it significant that "Option Explicit" is missing in your version?

gmayor · #4 09-06-2015, 05:04 AM

It is only significant in that it wasn't in the selection I pasted.

Option Explicit forces you to declare your variables. It is good to include it and can help when debugging. You can get the VBA editor to add it automatically to your new modules, by setting the option in Tools > Options > Editor.

cyraxote · #5 09-06-2015, 09:38 AM

That works.... but the Do While loop never ends.

gmayor · #6 09-06-2015, 09:36 PM

It ends at the end of the document. The macro works as written in Word 2010 and 2013. I didn't change anything in your macro except supply it with the parameters.

You can test it easily enough e.g.

Code:

Sub Macro1()
DoColorChange wdYellow, wdPink
End Sub

Personally I might have written it a little differently e.g.

Code:

Private Sub DoColorChange(SearchColor As Long, ReplaceColor As Long)

Dim oDoc As Document
Dim oRng As Range

Set oDoc = ActiveDocument
Set oRng = oDoc.Range

With oRng.Find
    .Highlight = True
    Do While oRng.Find.Execute
        If oRng.HighlightColorIndex = SearchColor Then
           oRng.HighlightColorIndex = ReplaceColor
        End If
        oRng.Collapse 0
    Loop
End With

End Sub

but it should work just the same as you had it.

cyraxote · #7 09-06-2015, 11:36 PM

I did the test as you suggested, and that got stuck in the endless loop, as did your alternate version. I'm using Word 2007, if that makes a difference.

My macro worked apart from the form when SearchColor and ReplaceColor were dimensioned as String, not Long. That's the only big difference I can see between this and the last working version. So I changed them back to strings:

Code:

Private Sub DoColorChange(SearchColor As String, ReplaceColor As String)

Dim oDoc As Document
Dim oRng As Range

Set oDoc = ActiveDocument
Set oRng = oDoc.Range

With oRng.Find
    .Highlight = True
    Do While oRng.Find.Execute
        If oRng.HighlightColorIndex = SearchColor Then
           oRng.HighlightColorIndex = ReplaceColor
        End If
        oRng.Collapse 0
    Loop
End With

End Sub
Sub Macro50()
    DoColorChange wdYellow, wdPink
End Sub

And this too causes the endless loop.

I went all the way back to the first one I posted, and now it TOO has the endless loop.

This is puzzling.

gmaxey · #8 09-07-2015, 05:22 AM

Find parameters are sticky. Perhaps you have changed your wrap parameter since your last successful test. Try adding:

Selection.Find.Wrap = wdFindStop
before your oRng.Find line.

cyraxote · #9 09-07-2015, 11:41 AM

Well, I shut Word between the different sessions (i.e., when I first posted the loop problem and when Graham responded), so it seems unlikely that something is "sticking." However, I added this:

Code:

With oRng.Find
    .Highlight = True
    .Wrap = wdFindStop
    Do While oRng.Find.Execute

I think that accomplishes the same thing as what you suggest, right? However, it had no effect on the endless loop.

I think I may have to back up and maybe use the Selection object to just test characters (i.e., "^?") instead of whatever this is doing. I'm unfamiliar with the Range object; stepping through this macro puzzles me, because it finds whole swaths of highlighted text at once, instead of just one character.

Then again, that's what happens when you search for no text with "Highlight" in the Find and Replace box.

I don't know what to think.

cyraxote · #10 09-07-2015, 12:02 PM

Maybe I could put some kind of marker at the end of the text, or near the end of the text, and tell the macro that when it finds this marker it should stop?

gmaxey · #11 09-07-2015, 02:34 PM

Can you attach the document with the form and the text you testing with. I don't see anything in the code that would cause a continuous loop.

Best Regards,
Greg Maxey
Saru mo ki kara ochiru (literally: Monkey even tree from fall). ~ Japanese Proverb

cyraxote · #12 09-07-2015, 03:52 PM

OK, I uploaded them. Thanks.

gmaxey · #13 09-07-2015, 06:33 PM

I ran your code in Word 2003/2007/2010/2013 and it ran fine with no continuous loop.

cyraxote · #14 09-07-2015, 06:48 PM

OK, but it's not like I'm making this up. I guess I'll keep trying.

Guessed · #15 09-07-2015, 07:34 PM

In the sample doc you provided, the final return in the document is highlighted so the loop is repeating there. If you remove that 'invisible' highlight then the code loop ends as expected.

For the purposes of discovering where the oRng was located during the loop, I added a line to your code so I could see the range.

Code:

Private Sub DoColorChange(SearchColor As Long, ReplaceColor As Long)

Dim oDoc As Document
Dim oRng As Range

Set oDoc = ActiveDocument
Set oRng = oDoc.Range

With oRng.Find
    .Highlight = True
    .Wrap = wdFindStop
    Do While oRng.Find.Execute
        If oRng.HighlightColorIndex = SearchColor Then
           oRng.HighlightColorIndex = ReplaceColor
        End If
        oRng.Select
        oRng.Collapse 0
    Loop
End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to copy cell info to multiple documents	Patrick Innes	Word VBA	2	02-18-2015 08:38 PM
Trouble automatically running user form	Lostinvba	Word VBA	3	11-23-2013 07:22 PM
I'm having trouble creating a Merge using label form and placing a shape in the text	blockie	Mail Merge	8	11-13-2013 11:28 PM
Macro trouble	Zack	Excel	2	10-14-2010 12:07 PM
Trouble updating a form	TomCaesar	Outlook	0	11-29-2006 10:52 AM