Jul 13 2020 08:38 PM
Jul 13 2020 08:38 PM
I have over 10 columns of data that each has over 50 rows. These Numbers are going up on daily bases. I have to create a checkbox list for every single Column and link it to a cell next to it. I realized if I want to do this one by one by the time I finish it, I will be homeless. I also tried some VBA codes but they mostly work for only one column, not multi-column. like this one below; Is there any solution to this problem that somebody can help or I should start applying for a new job?
Jul 13 2020 09:09 PM
@msm66 Inspired by what I found here:
Try this code:
Sub LinkChecks() For Each cb In ActiveSheet.CheckBoxes With cb .LinkedCell = _ .TopLeftCell.Offset(0, 1).Address End With Next cb End Sub
Jul 13 2020 10:34 PM - edited Jul 13 2020 10:54 PMSolution
Select your cells and try running this macro to add checkboxes and link it to the cell behind the checkbox.
If that's not what you want then you can change the linked cell to
cb.LinkedCell = cell.Offset(0,1).Address
to link it to the cell on the same row and one column to the right. Also, you would need to change the cell reference for the custom number format to match the linked cell (I'm assuming you want the linked cell to appear blank for a cleaner look - if not, then you can delete that line).
cell.Offset(0,1).NumberFormat = ";;;"
Sub AddCheckBoxes() Const height As Double = 19.2 Const width As Double = 17.4 Dim left As Double Dim top As Double Dim area As Range Dim cell As Range Dim cb As Object For Each area In Selection.Areas For Each cell In area.Cells left = cell.left + ((cell.width - width) / 2) top = cell.top + ((cell.height - height) / 2) Set cb = ActiveSheet.CheckBoxes.Add(left, top, width, height) cb.LinkedCell = cell.Address cb.Caption = vbNullString cb.Value = False cell.NumberFormat = ";;;" Next cell Next area Set cb = Nothing Set area = Nothing Set cell = Nothing End Sub
Jul 14 2020 06:33 PM
Jul 14 2020 06:41 PM
Aug 15 2020 05:49 PM
It's me again. since you helped with my last problem regarding checkboxes I thought you might be able to solve my recent frustration.
I attached an excel file. I am looking for a formula or function that it will adjust my calendar or schedule.
Let's say if I pick Monday only task 1, Tuesday task 1 and task 2 and Friday task 2. so now I am looking for a solution that all I can see in the new table will be look like or similar to the "green table" (basically modify a table to new table excluded the days and tasks that has not been selected (or ideally I am not sure if there is any way that just by selecting days and type of task can directly generate the table)
is this doable through excel?
looking forward to hearing your feedback.
Aug 16 2020 10:51 AM
So, you want the green table to pull from the first table depending on whether or not that day/task is checked?
I believe it is doable (see attached).
Aug 16 2020 01:43 PM
Well thank you, I would never figure this out by myself.
it is working perfectly. thank you.
only one question that might happen in the future., let's say we have multi-columns under each day. for example, underneath the Monday we have 10 columns and 4 rows. if we use this formula then we will see "off" in each cell and it won't look good.
is there any trick that the formula merges all the cells and just we see one "off" instead of multi off?