VBA to give msg box upon opening the workbook if cell is not blank

[bh24524]

Hi, I am looking to have a message pop-up as soon as a workbook is opened if the cell J34 which is a merged cell if that matters is not blank. The tab that the cell is on is called "Bump Sheet". I tried a few things but am not having any luck. Please and thank you.

 

[Joe4]

You would use a Workbook_Open event procedure, which is VBA code that runs as soon as the workbook is opened, as long as VBA/Macros are enabled.

However, you mention that J34 is part of a merged cell. Which cells exactly are being merged (that includes cell J34)?
Do you need to have the merge, or can you remove that? The "Center Across Selection" formatting option is often a better option, if certain circumstances.

 

[bh24524]

So J34 is merged with J35 and unfortunately they do need to be merged. I know that it needs to be the workbook open event and placed in ThisWorkbook, but I'm not sure of the exact coding. What I tried didn't work unfortunately. If this doesn't work with merged cells, that's not a problem. I don't NEED to have this feature in the workbook - it's more just a quality of life giving people a reminder type of deal.

Basically it would kinda be a copy of this code which is located in Sheet19Bump sheet) and it happens as soon as they type something into J34:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J34")) Is Nothing Then Exit Sub
    MsgBox "REMINDER: After filling out info for this row, click the Archive and Reset Sheet button", vbOKOnly
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbRed
    Application.Wait (Now + TimeValue("0:00:01"))
    Shapes("Archive_Reset").Fill.ForeColor.RGB = vbBlue
    Application.Wait (Now + TimeValue("0:00:01"))
   
End Sub

Basically someone needs to run a different macro after this cell is filled in to reset the sheet and save a copy. If they don't run the macro, I just want to message to appear every time the workbook is opened so it serves to remind them hey this needs done.

 

[Joe4]

OK, as long as cell J34 is the first cell in your merge range, this will work (I tested it out first):

Private Sub Workbook_Open()
    If Sheets("Bump Sheet").Range("J34") <> "" Then
        MsgBox "Cell J34 on Bump Sheet has something in it!", vbOKOnly
    End If
End Sub

Note: In order for this code to work automatically, this code MUST be placed in the "ThisWorkbook" module in VBA.
If you place it in any other module, it will not run automatically!

 

[bh24524]

Awesome Joe! I see what I did wrong now. I tried doing If Sheets("Bump Sheet").Range("J34") is nothing Then exit sub or something along those lines, but yours works, so thanks for that

 

[Joe4]

You are welcome.
Glad I was able to help!