Flashing controls on a userform

[RawlinsCross]

Good day,

I have some controls on a userform that have their BackColor property set to 'vbRed'. I want these controls to blink on and off. Eventually there should be an acknowledge button to stop it but for now I'm working on getting these controls to blink. Here's my attempt. I'm getting an error:

"Cannot the run the maco {macro location string of 'StartFlashing'}. The macro may not be available in this workbook or all macros may be disabled"

The idea is to identify all controls that have a backcolor vbRed (not all do) and store their names in an array. I checked the array, they're all in there. I can set the controls 'visible' property to False but looks like I can't re-run the Flashing subroutine. Any thoughts?

Setup Code

'member Array for Userform
Private msFlashArray() As Variant

Private Sub CheckControls()

For Each ctrl In Me.Controls
    If ctrl.BackColor = vbRed Then
        i = i + 1
        ReDim Preserve msFlashArray(1 To i)
        msFlashArray(i) = ctrl.Name
    End If
Next ctrl
' I confirmed that the correct controls were indeed listed in the FlashArray
Call StartFlashing
Application.OnTime (Now + TimeValue("00:00:01")), "StartFlashing"

End Sub

Flashing Code

Private Sub StartFlashing()

Dim item As Variant

For Each item In msFlashArray
    If Me.Controls(item).Visible = True Then
        Me.Controls(item).Visible = False
    ElseIf Me.Controls(item).Visible = False Then
        Me.Controls(item).Visible = True
   End If
Next item

End Sub

 

[RawlinsCross]

Sorry, the line

Application.OnTime (Now + TimeValue("00:00:01")), "StartFlashing"

should appear at the end of the StartFlashing subroutine. Same error though.

 

[CountTepes]

Application.ontime, only runs once.

Move this line from the main sub to being the last item in StartFlashing

But also change the startflashing routine, like this

Sub StartFlashing(OnOff as Boolean)

Dim item As Variant

For Each item In msFlashArray
    If Me.Controls(item).Visible = True Then
        Me.Controls(item).Visible = False
        ElseIf Me.Controls(item).Visible = False Then
        Me.Controls(item).Visible = True
   End If
Next item

if OnOff then
       Application.OnTime (Now + TimeValue("00:00:01")), "StartFlashing(True)"
  Else
'   -------  Put your code to set the state of the buttons to the visible state you require
End If

End Sub

When you want the flashing to stop

Call StartFlashing(False)

Make sure that StartFlashing is in a Module and not private.

 

[RawlinsCross]

Hi... so I've made some changes but get the identical error prompt as above. Here are my changes:

Userform Code (Userform Name = UTWells)

Private Sub CheckControls()

Dim i As Integer
Dim ctrl As Control

For Each ctrl In Me.Controls
   If ctrl.BackColor = vbRed Then
        i = i + 1
       ReDim Preserve sFlashArray_PVar(1 To i)
       sFlashArray_PVar(i) = ctrl.Name
   End If
Next ctrl

Call StartFlashing(True, UTWells)

End Sub

Module Code

'Added global variable for the array
Public sFlashArray_PVar() As Variant

Public Sub StartFlashing(OnOff As Boolean, myForm As UserForm)

Dim item As Variant

For Each item In sFlashArray_PVar
    If myForm.Controls(item).Visible = True Then
       myForm.Controls(item).Visible = False
    ElseIf myForm.Controls(item).Visible = False Then
       myForm.Controls(item).Visible = True
    End If
Next item

If OnOff Then
    Application.OnTime (Now + TimeValue("00:00:01")), "StartFlashing(True, myForm)"
Else
    MsgBox "You clicked OFF"
End If

End Sub

 

[CountTepes]

Does it crash on this;
Call StartFlashing(True, UTWells) ?

or this;
Application.OnTime (Now + TimeValue("00:00:01")), "StartFlashing(True, myForm)"


And you have StartFlashing in a module?


P.S.
Rather than doing this;

   If myForm.Controls(item).Visible = True Then
       myForm.Controls(item).Visible = False
    ElseIf myForm.Controls(item).Visible = False Then
       myForm.Controls(item).Visible = True
    End If

Do this

myForm.Controls(item).Visible = not  myForm.Controls(item).Visible

Just invert the visibility, without caring if it is visible or not. It will certainly run faster as you have fewer lines of code.

 

[RawlinsCross]

I like that last suggestion. It's failing on the Application.OnTime line - although, it's strange - it's not a typical RunTime error but more like an error you get when the Subroutine is not there (or available). I click okay and clear the prompt and the userform is still there but all the red controls are gone (i.e. visible = False) so I know StartFlashing subroutine has executed in part. See if I can post a picture here.

 

[CountTepes]

I created a userform with 2 button. I wrote the routine to toggle the visibility of the routine with an ontime statement to call itself once a second.

For me the code runs. But because I wrote the routine, directly into a standard module, I had no problems calling it. However, the ontime, although not crashing, does not call itself. I only get the one time run through.

I have had this before, years ago and I cannot remember how I resolved it. But I do remember that it took a lot of research.

The only thing which I can suggest for you at the moment, is to save everything and then restart Excel, to clear out all detritus which Microsoft products tend to accumulate in memory.

In the meantime, I'll see if I can find the solution.

 

[CountTepes]

I worked out my issue.

Ontime cannot call itself.

Call a small routine which just calls StartFlashing.

It may work for you.

 

[RawlinsCross]

Just an update - I don't get the above error when I remove the (NOW) portion of the recursive call. Of course the userform is blank with all the controls invisible but no error prompt.

Oh, just got your last message so I'll edit this one - will give that a go. Stand By

 

[RawlinsCross]

Okay, got it to work. I'm guessing you can't send arguments with OnTime? I think that's why I was getting the error. There was no actual subroutine called "StartFlashing(True, myForm)". Your thoughts on this?

Plus I think I need a 'termination' button as when I kill the userform it gives me a 'runtime' error of '92' For Loop Not Initialized within the StartFlashing subroutine - so there is still some work to do. So this is how I got around it - kindof sloppy but meh. A picture first of the 'residual' error and then the code itself.

Userform Code

Private Sub CheckControls
Dim i As Integer
Dim ctrl As Control

For Each ctrl In Me.Controls
   If ctrl.BackColor = vbRed Then
      i = i + 1
       ReDim Preserve sFlashArray_PVar(1 To i)
       sFlashArray_PVar(i) = ctrl.Name
   End If
Next ctrl

Call StartFlashing(True, UTWells)

End Sub

Module Code
Public sFlashArray_PVar() As Variant
Public myForm_PVar As UserForm

Public Sub StartFlashing(OnOff As Boolean, myForm As UserForm)

Dim item As Variant

For Each item In sFlashArray_PVar  'FOR LOOP NOT INITIALIZED IF YOU STOP THE FLASHING FROM THE IDE
     myForm.Controls(item).Visible = Not myForm.Controls(item).Visible
Next item

Set myForm_PVar = myForm  'This because I can't send the Userform via TempCall

If OnOff Then
     Application.OnTime Now + TimeValue("00:00:01"), "TempCall"
Else
     MsgBox "You clicked OFF"
     Exit Sub 
End If

End Sub

Public Sub TempCall()

Call StartFlashing(True, myForm_PVar)

End Sub