selectable paragraphs

[advent]

Hi, my first time with you so please be gentle!
I am trying to create a document that offers the reader a list of 15 selectable paragraphs of text. By using individual selection buttons (tick box) the chosen text paragraph(s) needs to automatically appear on sheet 2 in a list format???
Thank you for your time and help

 

[pbornemeier]

Create a user form (named frmSafety) with at least the following components: 1 Task Textbox (named: txtTaskDescription) 15 checkboxes (named: chkRisk01, ..., chkRisk15) and 15 associated descriptions (lblRisk01Desc, ..., lblRisk15Desc) a Cancel button (btnCancel), Clear Form button (btnClear) and a Create Task Form button (btnCreateTaskForm)

Place the paragraph text in the label caption fields.

Place the following code in the UserForm code page:

Option Explicit
 
Private Sub btnCancel_Click()
    Me.Hide
End Sub
 
Private Sub btnClear_Click()
    Dim ctl As Control
 
    For Each ctl In frmSafety.Controls
        If Left(ctl.Name, 3) = "chk" Then ctl.Value = False
    Next
End Sub
 
Private Sub btnCreateTaskForm_Click()
    Dim iCheckCount As Integer
    Dim ctl As Control
    Dim lNextWriteRow As Long
    Dim lX As Long
 
    If Len(txtTaskDescription) = 0 Then
        MsgBox "Ensure there is a description in the 'Task Desription' field."
        GoTo End_Sub
    End If
    For Each ctl In frmSafety.Controls
        If Left(ctl.Name, 3) = "chk" Then
            If ctl.Value Then iCheckCount = iCheckCount + 1
        End If
    Next
    If iCheckCount = 0 Then
        MsgBox "At least one checkbox must be checked."
        GoTo End_Sub
    End If
 
    'Insert new worksheet
    Worksheets.Add(after:=Sheets(Sheets.Count)).Name = Format(Now(), "yyyymmdd hhmmss")
 
    With ActiveSheet
        .Range("A1").Value = Me.txtTaskDescription
        lNextWriteRow = 3
        For lX = 1 To 15
            If frmSafety.Controls("chkRisk" & Right("0" & lX, 2)) = True Then
                .Cells(lNextWriteRow, 2) = frmSafety.Controls("lblRisk" & Right("0" & lX, 2) & "Desc").Caption
                lNextWriteRow = lNextWriteRow + 1
            End If
        Next
    End With
End_Sub:
End Sub

Insert a standard module and add this code to it:

Option Explicit
Sub ShowSafetyForm()
    frmSafety.Show
 
End Sub

[/code]

 

[advent]

Hi Phil, sorry it has taken me some time to reply, just that old stuff called too much work!!!!
Anyway, I have done as you said and I only have one small problem???
When you click on 'create Form' an error message shows. Each time it highlights the line that begins .Cell. Any ideas my friend??

David

 

[pbornemeier]

What is the text and number of the error?

 

[advent]

Hi my good friend;
Sorry about that I should have provided more info!
I am running Ecel 2007 and the message I receive when pushing the 'create form' button is:
Run-tine error '438'
'Object doesn't support this property or method'
When I hit the 'debug' button on message bar the following line is highlighted :
.Cells(lNextWriteRow, 2) = frmSafety.Controls("lblRisk" & Right("0" & lX, 2) & "Desc").Caption
It shows the above in one single line
Sorry for causing you problems

David

 

[pbornemeier]

What is the value of lX when the code is halted? Assuming the value of lX when the code is halted is 1, I would guess that the control that should be named lblRisk01Desc is not named that.

Since the error occured at the point you indicated, I expect that:
1) A new worksheet was inserted and was given a name based on the date and time'
2) Text appeared in cell A1 of the active worksheet.

If you PM me an email address, I can send you the worksheet (including the form) that I created for this problem. I do not get that error when I run the code.