Capture Check Box values in a continuous list

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
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:

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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
What information would you want from the checkboxes and where would it go when you add an entry to the receipt list?
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
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
 

Attachments

  • Exercise.png
    Exercise.png
    162.6 KB · Views: 3
  • ReceiptList.jpg
    ReceiptList.jpg
    89.5 KB · Views: 3

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Paul

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

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

Norie,

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
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
 

Forum statistics

Threads
1,141,130
Messages
5,704,454
Members
421,350
Latest member
jake9951

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top