CommandButton Back Colour is not set on WorkBook Open

[bursledon]

Hi All,
I am new to this forum so please accept my apologies if this has been asked before. In my workbook open process I have a line of VBA code to set the backcolour of one of my worksheet command buttons to indicate that it is active (to distinguish it from the other buttons). The code is invoked but the button colour is not altered. However, if I then click on the same button the exact same code is invoked to set the backcolour and the colour of the button changes straightaway i.e. as soon as the code is executed.

The only thing I can surmise is that the button colour has not changed during "start up" because the button has not actually been physically clicked. ?

Thanks Craig

[Bob Phillips]

Show the code in question.

[bursledon]

Hi Bob,
The start=up code invokes the table Change procedure

Public Sub Worksheet_Activate()
     
    buttonActiveColour = RGB(115, 135, 156)
    If (currentTable = "") Or (currentTable = "Bookings") Then
        currentTable = "Bookings"
        tableChange
    Else
        tableChange
    End If


End Sub

This is the tableChange procedure. On startup the backcolor function does nothing but when the procedure is invoked from a button click event the back colour changes as you would expect.

Private Sub tableChange()
   
    Dim tableWidth As Long
    Dim i As Integer
    Dim currentOnly As Boolean
    
    ClearScreen


    Select Case currentTable
        Case Is = "Bookings"
            cmdBooking.BackColor = buttonActiveColour

[Bob Phillips]

Doesn't clear up too much for me. Unless buttonActiveColour is defines as a global variable, the variable in the Activate event and in the tableChange are different variables, each local to that procedure.

The same applies to currentTable as to buttonActiveColour.

Are you sure that currentTable is Bookings in that Activate event?

[bursledon]

Hi Bob,
Yes, both buttonActiveColour and currentTable are global variables. I have debugged the code on startup and the currentTable is set to Bookings and the tableChange event is triggered from the Activate event, and then in turn the line of code that sets the button's backColour is triggered but it has no affect. However, if the tableChange procedure is triggered by a user actually clicking on a CommandButton the nthe back colour changes.

The use of another procedure is really a red herring as I have seen the same issue even if I code everything within the Activate event i.e. I originally coded the Activate event to set the back colour of the button directly to an RGB value (as opposed to a variable) again nothing; although in the same block of code I amended the caption property which did change. So, essentially I have proved that I can change the commandButton caption within the WorkSheet Activate event but NOT the backColour .. very ODD.

Thanks again, Craig

[Paul_Hossler]

I couldn't get the snippets of code to work so I changed stuff for testing

I think that this layout is a little more reliable, and has the advantage that it seems to work


In ThisWortkbook

Option Explicit


Private Sub Workbook_Open()
    buttonActiveColour = vbGreen        '   RGB(115, 135, 156)
    buttonInactiveColour = vbRed        '   RGB(225, 225, 225)
    
    tableChange
End Sub

In a Standard Module

Option Explicit


Public buttonActiveColour As Long
Public buttonInactiveColour As Long
Public currentTable As String


Sub tableChange()   '   not Private
    
    With Worksheets("Sheet1")
        
        'for testing
        currentTable = .Range("K3").Value
   
        Select Case currentTable
            Case Is = "Bookings"
                .cmdBooking.BackColor = buttonActiveColour
            Case Else
                .cmdBooking.BackColor = buttonInactiveColour
        End Select
    End With


End Sub

In the Worksheet Module

Option Explicit


Private Sub cmdBooking_Click()
    tableChange
End Sub


Private Sub Worksheet_Activate()
    tableChange
End Sub