Formula using VBA?

[Rana Gray]

Hey Everyone,

Sorry for the silly question but can someone help me with the VBA version of this formula: =IF($E13=TRUE,$B13,"")

Basically I don't want to put the formula directly in the cell where it can be overwritten, I also need it to apply to all rows (columns are fixed). All this does is if my checkbox is ticked the date in cell E13 gets copied into cell N13 etc. if it's not ticked then leave blank.

Thank you in advance!!

 

[dreid1011]

Something like this perhaps, though depending on where the code is, you may have to identify the specific worksheet name, and I am assumed you meant the date in cell B13 gets copied based on your formula:

Private Sub CheckDate()
Dim c, t As Range
Set t = Range("E13:E20")
For Each c In t
    If c.Value = True Then Range("N" & c.Row).Value = Range("B" & c.Row).Value
Next c
End Sub

 

[Rana Gray]

Something like this perhaps, though depending on where the code is, you may have to identify the specific worksheet name, and I am assumed you meant the date in cell B13 gets copied based on your formula:

Private Sub CheckDate()
Dim c, t As Range
Set t = Range("E13:E20")
For Each c In t
    If c.Value = True Then Range("N" & c.Row).Value = Range("B" & c.Row).Value
Next c
End Sub

This makes sense to me I just want to ask how to assign the "c" variable to "checkboxes" - I plugged this script in but nothing is happening. I placed it on "Sheet1". I'm certain I am missing something.

 

[dreid1011]

This makes sense to me I just want to ask how to assign the "c" variable to "checkboxes" - I plugged this script in but nothing is happening. I placed it on "Sheet1". I'm certain I am missing something.
View attachment 101353
View attachment 101354

That's on me. You did say checkboxes, but I wrote the code to check individual cells. Are the checkboxes Form Controls or ActiveX Controls?

 

[Rana Gray]

I also have a lot of checkboxes I know I can do an if then code but I want to avoid having to code each check box

That's on me. You did say checkboxes, but I wrote the code to check individual cells. Are the checkboxes Form Controls or ActiveX Controls?

Active X - I know I can use an if then statement to code each individual checkbox but there are so many and it would just be time consuming

 

[dreid1011]

I also have a lot of checkboxes I know I can do an if then code but I want to avoid having to code each check box

Active X - I know I can use an if then statement to code each individual checkbox but there are so many and it would just be time consuming

Are there more checkboxes beyond column E?

 

[Rana Gray]

Are there more checkboxes beyond column E?

No just E

 

[dreid1011]

Alright, so I don't think there is an easy way to identify which cell belongs to which checkbox without linking each one individually, or naming them in a way that we can use the name or part of the name to know which row it is on. Either way, that process is going to be tedious and time consuming at best.

There are some tricks you can use instead of check boxes if you are up for it.

 

[Rana Gray]

Alright, so I don't think there is an easy way to identify which cell belongs to which checkbox without linking each one individually, or naming them in a way that we can use the name or part of the name to know which row it is on. Either way, that process is going to be tedious and time consuming at best.

There are some tricks you can use instead of check boxes if you are up for it.

So I have already linked each checkbox to the cell it's being housed in if that helps so each cell in column E becomes true/false based on the checkbox already if that changes things

 

[dreid1011]

So I have already linked each checkbox to the cell it's being housed in if that helps so each cell in column E becomes true/false based on the checkbox already if that changes things

The code SHOULD have worked then. If all the checkboxes are linked to their respective cells in column E, then it would be checking the cell value. I can still adjust the code to check the check boxes though instead of the cells.

I suppose one thing to test would be identifying the sheet name in the code as well:

Private Sub CheckDate()
Dim c, t As Range
Dim ws As Worksheet
Set ws = Sheets("Monthly Tracker")
Set t = ws.Range("E13:E112")
For Each c In t
    If c.Value = True Then ws.Range("N" & c.Row).Value = ws.Range("B" & c.Row).Value
Next c
End Sub