How to get a macro to update the document?

[JenniferMurphy]

I have a macro that changes the colors in the selected text. Before we upgraded to Win 10 and Office 365, I'm pretty sure the page used to show the results as the marco operated. Now it doesn't until I exit the macro.

What command do I need to execute in the macro to get it to show the results while the macro is still working?

In Excel, I think the command is something like Activesheet.refresh, but I can't even find that.

Thanks

 

[Joe4]

Selection needs to be a single cell. If a range is selected the len function will be passed an array instead of a string yielding a type mismatch error.

If the original goal was for this to work on a range of cells then it would need to be modified.

If that is the case, I would probably recommend adding in some checking for that to avoid errors.

You can get the number of cells in a selection with this calculation:

Selection.CountLarge

So you can check to see if that is greater than 1 (and if it is, return some sort of warning & exit).

Or, you can just use ActiveCell instead of Selection, which will always just be one cell (if a multi-cell selection, it is the first cell in that selection).

 

[JenniferMurphy]

try

Sub RandColorsTemp()
    Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
    Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
    Dim Colors, i As Long
    Application.ScreenUpdating = True
    Colors = Array(RGBRed, RGBGreen)
    For i = 1 To Len(Selection)
        Selection.Characters(i, 1).Font.Color = Colors(i Mod 2)
        If MsgBox("Continue", vbYesNo) <> vbYes Then Exit For
    Next i
End Sub

Your code gets a compile error on the Selection.Characters line. It was not immediately obvious to me how to fix it, so I just added the screenupdating line to my code. It still works the same.

Here's my code:

Sub RandColorsTemp()

Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)
Dim NextColor As Long
Dim Char As Range
Dim msg As String

Application.ScreenUpdating = True
NextColor = RGBRed
For Each Char In Selection.Characters
  If NextColor = RGBRed Then
    NextColor = RGBGreen
  Else
    NextColor = RGBRed
  End If
  Char.Font.Color = NextColor
'  msg = "'" & char "' done, continue?"
  If MsgBox("'" & Char & "' done, continue?", vbYesNo) <> vbYes Then Exit For
Next Char

End Sub

Here's the text before running the marco:

After 1 iteration, the "M" is colored and the macro is still running.

After 2 iterations, the "e" is "done", but the screen is nopt updated:

After 7 iterations, everything up to the "C" have been colored, but the screen is not updated:

After the macro finishes and exits, the screen is updated.

Why does it show the first character, but not the rest?

 

[JenniferMurphy]

PS: The original code used a lot more than just 2 colors and it offered the user choices for the set of colors. It then chose them randomly. After the entire selection was colored, it would ask the user if was OK. If not, it would redo it. It did not stop after each character. I provided this simplified example to avoid posting hundreds of lines of code.

 

[dmt32]

A bit belated but had a quick play & came up with this

Sub RandColorsTemp()
    Dim NextColor   As XlRgbColor
    Dim i           As Long
    
If Selection.CountLarge > 1 Then Exit Sub

    For i = 1 To Len(Selection)
        NextColor = IIf(NextColor = RGBRed, RGBGreen, RGBRed)
        Selection.Characters(Start:=i, Length:=1).Font.Color = NextColor
    Next i

End Sub

Not sure if it is what you were looking for but may give some further idea.

You will note that I have declared your variable NextColor as XLRgbColor the built-in enumeration for RGB colors. XlRgbColor enumeration (Excel)

I have also include @Joe4 suggestion to check for multi-cell selection

Hope Helpful

Dave

 

[JenniferMurphy]

A bit belated but had a quick play & came up with this

. . .

Hope Helpful

Dave

I copied your code and ran it. It gets a compile error on the .countlarge attribute.

 

[dmt32]

Try changing it to ActiveCell

If ActiveCell.CountLarge > 1 Then Exit Sub

and if still have issue try

If ActiveCell.Count > 1 Then Exit Sub

the main purpose of my post was to show the built in XLRGBColor enumeration which negates the need to do this

Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)

Dave

 

[Joe4]

Try changing it to ActiveCell

If ActiveCell.CountLarge > 1 Then Exit Sub

and if still have issue try

If ActiveCell.Count > 1 Then Exit Sub

the main purpose of my post was to show the built in XLRGBColor enumeration which negates the need to do this

Dim RGBRed As Long:    RGBRed = RGB(255, 0, 0)
Dim RGBGreen As Long:  RGBGreen = RGB(0, 255, 0)

Dave

I don't believe that ActiveCell can ever be anything more than one cell, can it?
If so, that line would really serve any purpose.

Not sure why "CountLarge" would return any sort of error. It is a valid VBA property.

Range.CountLarge property (Excel)

Office VBA reference topic

docs.microsoft.com

An Overview Of Range.CountLarge

I’ve had a few questions and comments about the Range.CountLarge property so I thought it was high time that I give you my take on it. What Is It? Here’s how Range.CountLarge is describ…

colinlegg.wordpress.com

 

[RoryA]

I believe this is in Word...

Try adding DoEvents after the If MsgBox... line.

 

[dmt32]

I don't believe that ActiveCell can ever be anything more than one cell, can it?
If so, that line would really serve any purpose.

@Joe4 Its what you call a senior moment which I seem to have plenty of these days.
I did realize the daft error after I had posted but too late to delete it.

Dave

 

[Joe4]

@Joe4 Its what you call a senior moment which I seem to have plenty of these days.
I did realize the daft error after I had posted but too late to delete it.

Dave

No worries.
I find myself having more and more of those these days too!