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