Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generalising macro 2 cycle thru checkboxes in frames/pages on User
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.LblSelecte dAudit.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.LblSelecte dAudit.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.Captio n = 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec) | Excel Discussion (Misc queries) | |||
how can I print pages without text frames on it? | Setting up and Configuration of Excel | |||
Cycle through non-hidden pages only | Excel Discussion (Misc queries) | |||
user form and frames | Excel Programming | |||
Generalising Deleting Rows after AUTOFILTER | Excel Programming |