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]

See if this works. Checking the check boxes and then referencing their linked cells to copy the dates.

Private Sub CheckDate()
Dim cb As OLEObject
Dim ws As Worksheet
Set ws = Sheets("Monthly Tracker")
For Each cb In ws.OLEObjects
    If cb.Object.Value = True Then ws.Range("N" & Right(cb.LinkedCell, Len(cb.LinkedCell) - 3)).Value = ws.Range("B" & Right(cb.LinkedCell, Len(cb.LinkedCell) - 3)).Value
Next cb
End Sub

 

[Rana Gray]

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

It still does nothing unfortunately - and I pasted the code into "Sheet1" and I tried after in "Workbook" but still nothing. I may have to do this the annoying way haha I appreciate you so much for trying. Maybe I'm just doing something wrong.

 

[Rana Gray]

See if this works. Checking the check boxes and then referencing their linked cells to copy the dates.

Private Sub CheckDate()
Dim cb As OLEObject
Dim ws As Worksheet
Set ws = Sheets("Monthly Tracker")
For Each cb In ws.OLEObjects
    If cb.Object.Value = True Then ws.Range("N" & Right(cb.LinkedCell, Len(cb.LinkedCell) - 3)).Value = ws.Range("B" & Right(cb.LinkedCell, Len(cb.LinkedCell) - 3)).Value
Next cb
End Sub

still didn't work

 

[dreid1011]

still didn't work

Where are you putting the code, and how exactly is it not working?

 

[Rana Gray]

Where are you putting the code, and how exactly is it not working?

I put it on the coding page for sheet1 I also tired it in the coding page for the workbook itself - and it does nothing at all.

this way works but it's going t take way longer:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Range("B13").Value = Date
Else: Range("B13").Value = ""
End If

If CheckBox1 = True Then
Range("N13").Value = Date
Else: Range("N13").Value = ""
End If
End Sub

I'm happy to share my workbook with you if you want to see for yourself

 

[dreid1011]

Yes, it seems that might be the best way to handle it at this point.

 

[Rana Gray]

Yes, it seems that might be the best way to handle it at this point.

Thank you so much for everything!!!

 

[dreid1011]

Thank you so much for everything!!!

Sorry, I meant sharing the workbook would be good so I can get my eyes on why it might not be working.

 

[Rana Gray]

Sorry, I meant sharing the workbook would be good so I can get my eyes on why it might not be working.

oh my bad! here it is:

SBCLD Task Tracker (F2024) - Alexandra Gauthier-Dupont.xlsm

Shared with Dropbox

www.dropbox.com

 

[dreid1011]

Okay, I have tried and tried to get this to work, and I just can't. The only thing I managed to get working was the original code I provided, but I had to trigger it manually. Worksheet_Change was not working, and other code that ran just fine on my test sheet didn't want to even start. You will probably be best served using your CheckBox_Click codes if those are doing what you want.