Multy function Button VBA

[ONP Nino]

The attached code is not working as I would like

The wish I have is 1 button with multi functions each allowing different texts to populate the Button so I can easily see whats happening with the workbook.

By default the first Text in the Button would be "Ready" In Yellow

Then if I press on Ready, text "Running" in green would populate the button and the VBA code would run.
And if I press on Running text "Stopped" would populate the Button in red and the attached vba code would be called to Stop and here is where the screenUpdating would refresh and show me the results up to the point when stopped was pressed.

Then if I press on Stopped text "ready" would populate the Button and I would press once more to run and finish the code task.
And upon the code completion text "Completed" would be on the button and the ScreenUpdate would be True.

Hope I am not asking for a complicated Code although to me sounds like it is.
Thank you for your Help
Cheers


current code I am using

Sub ValueStore()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Dim rw As Integer
    
    For rw = 12 To 37
        Cells(rw, 1).Value = 1
        
        Application.Calculate
        
        Do While Application.CalculationState <> xlDone
            DoEvents
        Loop
        
        Range(Cells(rw, 3), Cells(rw, 46)).Copy ' check if this is the right column to stop on
        Range(Cells(rw, 3), Cells(rw, 46)).PasteSpecial (xlPasteValues)
        'Range(Cells(rw, 3), Cells(rw, 46)).Value = Range(Cells(rw, 3), Cells(rw, 46)).Value
    Next

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Sub Toggle()
Set shp = ActiveSheet.Shapes(1)
If shp.TextFrame.Characters.Text = "Ready" Then
    With shp
        .Fill.ForeColor.RGB = rgbRed
        .TextFrame.Characters.Text = "Stopped"
    End With
    Call ValueStore
ElseIf shp.TextFrame.Characters.Text = "Stopped" Then
    With shp
        .Fill.ForeColor.RGB = rgbGreen
        .TextFrame.Characters.Text = "Ready"
    End With
    
End If
End Sub

 

[dmt32]

Hi,

See if this update to your code does what you want

Sub Toggle()
    Static ButtonState As Integer
    Dim ButtonFontColor As Long
    Dim ButtonCaption As String
    
'set buttonstate index
    ButtonState = ButtonState + 1
    If ButtonState > 5 Then ButtonState = 1


'button caption
    ButtonCaption = Choose(ButtonState, "Ready", "Running", "Stopped", "Ready", "Completed")


'caption colorindex value
    ButtonFontColor = Choose(ButtonState, 6, 10, 3, 6, 6)


'apply settings
        With ActiveSheet.Buttons(1)
            .Font.ColorIndex = ButtonFontColor
            .Caption = ButtonCaption
        End With


'pass button state to common code
        CommonCode ButtonState


End Sub

I have assumed that your button is a FORMS button.
You will need to adjust the button captions sequence & their colorindex values (shown in Red) as required.

If you are not familiar with colorindex values then look here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/colorindex-property

When button toggled, you can pass the button state to a common code to perform the various tasks

e.g.

Sub CommonCode(ByVal State As Integer)


    Select Case State
    
    Case 1 '"Ready"
    'code for ready state
    
    Case 2 '"Running"
    'code for running state
    
    Case 3 '"Stopped"
    'code for stopped state
    
    Case 5 '"Completed"
    'code for completed state


End Select


MsgBox State
    
End Sub

In this example I have only passed the buttonstate integer but you could just as easily pass the caption (or both) if required.

Hope Helpful

Dave

 

[ONP Nino]

Dear John,
Thank you for dedicating and taking the time to write the code, It works really well and I have learned a bit more
Hope someone-else will find this code useful as well.
Many Thanks
Cheers
Nino