Generalising macro 2 cycle thru checkboxes in frames/pages on User

[LFNFan]

Hi

Apologies for the length of this post. I guess 'less is more'. Structure
of post will be: Background, Prototype Functionality (longest part!),
Problem, Code, Question. I am running Excel 2000.

Background: I have designed a simple prototype in Excel, to replace a paper
'project completion checklist' - 'sign your name against each step' for a
process that has a number of steps to be completed, in the correct order
(approx 50) - with an electronic checklist. There can be upto 15 projects
running at one time, each with their own checklist.

Prototype functionality: An initial userform appears when the spreadsheet
is opened. The user chooses their project from the list in the form. This
then brings up the completion checklist userform proper. The prototype is
laid out as a set of checkboxes (Cbx1, Cbx2) in a frame, with labels of
narrative outside the frame, describing the nature of each completion step.
There is an 'info sheet' in the spreadsheet which has Cbx1 Cbx2 down the left
hand side, and project names across the top.

When the completion checklist userform is initialised, a macro references
the info sheet and sets current checkbox values based on checks made in
previous sessions. The user checks the checkbox for the step they have just
completed. This initiates a msgbox to ask for confirmation (y/n). If 'y' is
selected, a macro inputs their system username and the date in the 'caption'
field of the cbx, and locks it. If no is selected, the check is removed from
the cbx.

Ok so far? There is a 'save and close' command button for when the user has
finished updating the checklist. This initiates a macro to cycle through the
cbx captions in the frame and paste these into the correct cell in the info
sheet ready for the next time that project completion checklist is called.
The workbook then saves and closes. This all works really well.

Problem: Now, my problem arises when I try to expand the prototype
checklist userform to include a number of frames with checkboxes in, and also
a number of pages (each with their own frames and checkboxes). I want to do
this as each project has discrete phases that I want in separate frames, or
pages, for user's clarity.

I have posted the code below, but in essence, to avoid a 'no object' error
when the macro executes, the prototype specifies
userform.frame1.activecontrol to get to the correct checkbox. Therefore, I
can't figure out how to generalise the macros to get them to work for a
number of different frames, or pages, short of duplicating a whole set of
macros for each frame or page - which is do-able, but seems a bit mad....

Code:
Sub PopulateCbxValuesOnInitialise()

Dim myConfigCbx As String
' turn of screen updating to preserve white background
Application.ScreenUpdating = False

'reset activecell to facilitate more accurate 'Find'
Worksheets("info sheet").Activate
Range("A1").Activate

'identify relevant column for source data based on audit selected
Cells.find(What:=FrmCompletionChecklist.LblSelectedAudit.Caption, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchDirection:=xlNext).Activate
myColumnRef = ActiveCell.Column

For Each myCbxCtrl In FrmCompletionChecklist.Frame1.Controls
'identify relevant target row
Cells.find(What:=myCbxCtrl.Name, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlNext).Activate
myRowRef = ActiveCell.Row

'assign details to a variable - easier to use this for amending Cbx properties
myConfigCbx = Intersect(Rows(myRowRef), Columns(myColumnRef)).Value
If myConfigCbx = Empty Then
myCbxCtrl.Enabled = True
Else
myCbxCtrl.Caption = myConfigCbx
myCbxCtrl.Enabled = False
End If

Next myCbxCtrl

'Application.ScreenUpdating = True (turned off when press 'save & close')
End Sub

Sub ListCbxCaptions()
Worksheets("info sheet").Activate
For Each myCbxCtrl In FrmCompletionChecklist.Frame1.Controls
'if not then loop put in to avoid pasting the value of an unticked
'control caption into the spreadsheet - all these will start 'Cbx'.
If Not Mid(myCbxCtrl.Caption, 1, 3) = "Cbx" Then
'identify relevant target column
Cells.find(What:=FrmCompletionChecklist.LblSelectedAudit.Caption, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchDirection:=xlNext).Activate
myColumnRef = ActiveCell.Column
'identify relevant target row
Cells.find(What:=myCbxCtrl.Name, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext).Activate
myRowRef = ActiveCell.Row
'find where the two intersect and insert cbxctrl caption in that cell
Intersect(Rows(myRowRef), Columns(myColumnRef)).Value =
myCbxCtrl.Caption
End If
Next myCbxCtrl
End Sub

Sub ConfirmAction()
Dim Msg, Style, Title, Response
Msg = "Please confirm before selection finalised" & vbCrLf & "
(double-click required if 'No')"
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons, 'No' as
default
Title = "Confirmation"

'coming up is where the message box gets displayed
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
NameDateStamp
MakeCbxDisabled
'make the Cbx have a tick in it.
FrmCompletionChecklist.Frame1.ActiveControl.Value = -1
Else
'this needs a double-click on 'no' to close the box for some unknown
reason!!
'FrmCompletionChecklist.Frame1.ActiveControl.Value = 0
'FrmCompletionChecklist.Controls("CmdSaveAndClose").SetFocus
BackOut
End If
End Sub

Sub NameDateStamp()
FrmCompletionChecklist.Frame1.ActiveControl.Caption = Mid("UsrNm", 1, 5) & "
: " & WorksheetFunction.Text(Now(), "hh:mm:ss")
End Sub

Sub MakeCbxDisabled()
Dim myCbx As CheckBox
FrmCompletionChecklist.Frame1.ActiveControl.Locked = True
End Sub

Sub BackOut()
FrmCompletionChecklist.Frame1.ActiveControl.Value = 0
FrmCompletionChecklist.Controls("CmdSaveAndClose").SetFocus
End Sub

Question: based on the above code, how can I add more frames, and pages, to
the userform and just adapt the existing code to cycle through each
frame/page?

Big Thanks in advance for any help/advice!