Capture Check Box values in a continuous list

[paulfitz54]

Hi,

I have a VBA code that copies account information from a receipt worksheet and appends it to a Receipt list worksheet. This code works perfectly.
I also have 9 control check boxes in the receipt worksheet that can have any number of boxes ticked for each receipt.

My question is, can I capture the checkbox information along with account information and append it to the Receipt List worksheet?

Copy of the VBA code:

Sub PostReceipt()

'Post Receipt details into Receipt List

ActiveSheet.Unprotect Password:="ssap"
Application.ScreenUpdating = False
    Sheets("Receipt").Select
    Rows("32:34").Select
    Selection.EntireRow.Hidden = False
    ActiveSheet.Range("B33:E33").Select
    Selection.Copy
    Sheets("Receipt List").Select
    ActiveSheet.Range("a2000").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Range("A7").Select
    Sheets("Receipt").Select
    Rows("32:34").Select
    Selection.EntireRow.Hidden = True
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "Select Name"
    Range("E8:F8").Select
    ActiveCell.FormulaR1C1 = "Enter Date"
    Range("E12:F12").Select
    ActiveCell.FormulaR1C1 = "Enter Amount"
    Range("F6").Select
    Range("F10").Value = Range("F10").Value + 1
    Range("F6").Select
ActiveSheet.Protect Password:="ssap"

End Sub

Thanks for all your help in the past and the future
Paul

 

[Norie]

What information would you want from the checkboxes and where would it go when you add an entry to the receipt list?

 

[paulfitz54]

Hi Norie,

If you look at the two images below;
In the Exercise worksheet, I have nine checkboxes representing nine exercises, if the first three exercise are checked I would like a mark or number to appear under the abbreviated headings in the receipt list when I post the receipt.
I would add conditional formatting to add icon sets to cells with an entry

Does this make sense?

Thanks,
Paul

 

[Norie]

Paul

What are the checkbox names and are the ActiveX or Forms controls?

 

[paulfitz54]

Norie,

The names are checkbox1, checkbox2, etc and there Form Controls

 

[Norie]

Paul

Not sure what you mean about conditional formatting but something like this might help, it should add a 'X' in the appropriate column for each checked exercise.

Sub PostReceipt()
Dim chk As Object
Dim arrExercises(1 To 9) As String
Dim idx As Long

'Post Receipt details into Receipt List

    ActiveSheet.Unprotect Password:="ssap"
    Application.ScreenUpdating = False
    Sheets("Receipt").Select
    
    ' get exercises
    For idx = 1 To 9
        Set chk = Sheets("Receipt").Shapes("Check Box " & idx)
        
        If chk.OLEFormat.Object.Value = 1 Then
            arrExercises(idx) = "X"
        End If
        
    Next idx
    
    Rows("32:34").Select
    Selection.EntireRow.Hidden = False
    ActiveSheet.Range("B33:E33").Select
    Selection.Copy
    Sheets("Receipt List").Select
    ActiveSheet.Range("a2000").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    ' add X for each selected exercise
    ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(, 4).Resize(, 9).Value = arrExercises
            
    Range("A7").Select
    Sheets("Receipt").Select
    Rows("32:34").Select
    Selection.EntireRow.Hidden = True
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "Select Name"
    Range("E8:F8").Select
    ActiveCell.FormulaR1C1 = "Enter Date"
    Range("E12:F12").Select
    ActiveCell.FormulaR1C1 = "Enter Amount"
    Range("F6").Select
    Range("F10").Value = Range("F10").Value + 1
    Range("F6").Select

    ActiveSheet.Protect Password:="ssap"

End Sub

 

[paulfitz54]

Hi Norie,

I just ran that now and it works a treat, I must try and find the time to learn VBA

Thanks a million
Paul