Userform update

[Ken McLennan]

G'day there People,

I've modified the calendar that John Walkenbach built with an
Array Formula. As he suggested in his book, I've modified it so that the
dates of previous & next months are also displayed, however they are in
a different font colour.

I've also built myself a userform which duplicates the calendar
using commandbuttons for the date cells. I have the both sets of scroll
bars pointing to the same ControlSource, so both should work
simultaneously. What I want is for the user to adjust the month, or
year, on the userfomr and have both calendars change together or use the
worksheet one alone. At the moment, the userform works as planned but
the update is delayed until I click one of the buttons on the userform.
Then both calendars update.

I've tried placing the code to call the update routines in various
places, but only in the AfterUpdate event does it actually update both
the worksheet & the userform details. However it still only does it when
I click another button. How can I get it to update when the scroll bar
is clicked?

Thanks in advance,
Ken McLennan
Qld, Australia

Here is the code I'm using:

####################################################
In the Userform:

Option Explicit

Private Sub cmbCancel_Click()
Unload Me
End Sub

Private Sub ScrollBar2_AfterUpdate()
Worksheets("Calendar").Calculate
calUpdte
End Sub

Private Sub ScrollBar3_AfterUpdate()
Worksheets("Calendar").Calculate
calUpdte
End Sub

Private Sub UserForm_Activate()
' All variables declared in main module to facilitate use by public
subroutines
'
' Get custom list (Month) values into array
listArray = Application.GetCustomListContents(4)
Set mth = Worksheets("Calendar").Range("m")
Set yr = Worksheets("Calendar").Range("y")
' Call routine to update Calendar "Day" values on cmdBtns and
month/year display on textbox
calUpdte
End Sub

Private Sub UserForm_Initialize()
' Declare variables as required. In this case...
' Declare a Control objecttype
Dim ctl As Control
' Step through each control on form
For Each ctl In Me.Controls
' If the control's name starts with "Comm"...
' All cmdButtons not to be in UDF Class have had their names
changed
' to preclude selection
If Left(ctl.Name, 4) = "Comm" Then
' shftBtns is an array of type object which is declared with
30 members
' in the Declarations section of the standard module.
' Each cmdButton name is of the form "CommandButtonXX" where
XX is the
' number of the button starting at 1 and both contiguous and
consecutive
' until 30. This number part is extracted from the cmdButton
name String,
' converted to a number and used to point to an entry in the
array of
' controls. This entry then has the control assigned to it.
Set dateBtns(Val(Mid(ctl.Name, 14, Len(ctl.Name)))).btnGroup
= ctl
End If
' Do I need to explain??
Next ctl

End Sub

###################################################
In a general module:

Sub calUpdte()

' Copy Calendar "Day" values to command button captions
Dim x As Integer
Dim y As Integer
For y = 1 To 6
For x = 1 To 7
dateBtns(((y * 7) + x) - 7).btnGroup.Caption =
[calArray].Cells(y, x).Text
dateBtns(((y * 7) + x) - 7).btnGroup.ForeColor =
[calArray].Cells(y, x).Font.Color
dateBtns(((y * 7) + x) - 7).btnGroup.Font.Bold =
[calArray].Cells(y, x).Font.Bold
Next x
Next y
frmCalendar.TextBox1.Value = listArray(Worksheets("Calendar").Range
("m").Value) & " " & yr
End Sub