VBA Question

[dmissel23]

Hello!

I have a spreadsheet with 64 tables set up on it. 4 tables in a row and 16 rows of tables. Each table has a different assortment of cells that have values and cells that do not have values. Every now and then we will need to add a value to a red cell in a pre-existing table or we will create a whole new row of tables. I was wondering if there is a VBA code that would automatically notify a user with a message box only when they try to add a value to an empty red cell that basically confirms if they'd like to enter a value in that empty cell? If there is code for this, would it apply to cells that end up having their values removed, which become empty red cells, or to tables that get added to the sheet in the future?

Thanks for any and all assistance!

dmissel23

 

[HighAndWilder]

I have developed some code for you but before I post it on here just a few questions and points.

Is the cell colored red in conditional formatting? If not then how?

My code caters for three scenario's

1. Empty to a value.
2. Value to another value.
3. Value to empty.

After a change of cell value is confirmed by :
1. Pressing the Enter key.
2. Pressing the Tab key.
3. Selecting another cell
4. Pressing the Del key.

A different appropriate prompt is presented to the user asking if they want to continue or retain the previous value of the cell.

The check is only made if the cell being changed is in any table on the worksheet.

If new tables are created then it will still work.

Does this sound like what you are wanting?

 

[dmissel23]

I have developed some code for you but before I post it on here just a few questions and points.

Is the cell colored red in conditional formatting? If not then how?

My code caters for three scenario's

1. Empty to a value.
2. Value to another value.
3. Value to empty.

After a change of cell value is confirmed by :
1. Pressing the Enter key.
2. Pressing the Tab key.
3. Selecting another cell
4. Pressing the Del key.

A different appropriate prompt is presented to the user asking if they want to continue or retain the previous value of the cell.

The check is only made if the cell being changed is in any table on the worksheet.

If new tables are created then it will still work.

Does this sound like what you are wanting?

Thanks for the quick response!

I can't thank you enough for your help! To answer your questions, I inherited this spreadsheet when I recently got hired and to my knowledge the red cells were manually filled using the fill color tool and didn't use any conditional formatting to make them red.

That actually does sound like the type of code I am looking for!

 

[HighAndWilder]

So when somebody deletes an entry in a cell do you want cell to be colored red?

What determines whether a cell is yellow or green?

It would be better if conditional formatting was used.

 

[dmissel23]

Thanks for suggesting the conditional formatting idea, that works perfectly! The code would just be a prompt to the user whenever clicking on only a red cell without any values in it that would confirm if they are actually meaning to add a value to that red cell.

 

[HighAndWilder]

Paste this code into the Worksheet Code Module. It has to go into each and every worksheet

It is triggered when the value in the cell is changed rather than when the cell is selected.
Cells may be selected when there is no intention of changing the value in the cell.

I can enhance it by not letting a user select a non-empty cell and then revert back to the last selected cell.

Let me know if you need any changes.

The warning messages can be changed to say whatever you like but unfortunately the size of the font cannot be increased.

Option Explicit

Public varCellValue As Variant
Public rngLastCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim blnInATable As Boolean
Dim strLastValue As String
Dim strMsg As String

    Application.EnableEvents = False
    
    If fncIsCellInTable(rngLastCell) Then
        
        ' Previous cell value is blank.
        If varCellValue = "" Then
            strMsg = "Enter value of " & Chr(39) & rngLastCell.Value & Chr(39) & " to empty cell"
        End If
        
        ' Previous cell value and current cell value are not blanl.
        If Trim(rngLastCell.Value) <> "" And varCellValue <> "" Then
            strMsg = "Do you want to change the value from " & Chr(39) & varCellValue & Chr(39) & " to " & Chr(39) & rngLastCell.Value & Chr(39)
        End If
        
        ' Previous cell value was not blank was current one is.
        If Trim(rngLastCell.Value) = "" And varCellValue <> "" Then
            strMsg = "Do you want to delete the value of " & Chr(39) & varCellValue & Chr(39) & " from this cell?"
        End If
        
        If MsgBox(strMsg, vbYesNo, "Warning!!") = vbNo Then
            rngLastCell = varCellValue
        End If
        
    End If
    
    Application.EnableEvents = True

End Sub

Private Function fncIsCellInTable(rngCell As Range) As Boolean
Dim rngActiveCell As Range

  ' Function returns TRUE if active cell is in a table and FALSE if it isn't.

    Set rngActiveCell = rngCell

    On Error Resume Next

    fncIsCellInTable = (rngActiveCell.ListObject.Name <> "")

    On Error GoTo 0

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' varCellValue is a public variable that is set to the value of a cll when a cell is selected.
    varCellValue = ActiveCell.Value

    ' rngLastCell is the range of the curtent active cell.
    Set rngLastCell = ActiveCell

End Sub

 

[dmissel23]

Paste this code into the Worksheet Code Module. It has to go into each and every worksheet

It is triggered when the value in the cell is changed rather than when the cell is selected.
Cells may be selected when there is no intention of changing the value in the cell.

I can enhance it by not letting a user select a non-empty cell and then revert back to the last selected cell.

Let me know if you need any changes.

The warning messages can be changed to say whatever you like but unfortunately the size of the font cannot be increased.

Option Explicit

Public varCellValue As Variant
Public rngLastCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim blnInATable As Boolean
Dim strLastValue As String
Dim strMsg As String

    Application.EnableEvents = False
   
    If fncIsCellInTable(rngLastCell) Then
       
        ' Previous cell value is blank.
        If varCellValue = "" Then
            strMsg = "Enter value of " & Chr(39) & rngLastCell.Value & Chr(39) & " to empty cell"
        End If
       
        ' Previous cell value and current cell value are not blanl.
        If Trim(rngLastCell.Value) <> "" And varCellValue <> "" Then
            strMsg = "Do you want to change the value from " & Chr(39) & varCellValue & Chr(39) & " to " & Chr(39) & rngLastCell.Value & Chr(39)
        End If
       
        ' Previous cell value was not blank was current one is.
        If Trim(rngLastCell.Value) = "" And varCellValue <> "" Then
            strMsg = "Do you want to delete the value of " & Chr(39) & varCellValue & Chr(39) & " from this cell?"
        End If
       
        If MsgBox(strMsg, vbYesNo, "Warning!!") = vbNo Then
            rngLastCell = varCellValue
        End If
       
    End If
   
    Application.EnableEvents = True

End Sub

Private Function fncIsCellInTable(rngCell As Range) As Boolean
Dim rngActiveCell As Range

  ' Function returns TRUE if active cell is in a table and FALSE if it isn't.

    Set rngActiveCell = rngCell

    On Error Resume Next

    fncIsCellInTable = (rngActiveCell.ListObject.Name <> "")

    On Error GoTo 0

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' varCellValue is a public variable that is set to the value of a cll when a cell is selected.
    varCellValue = ActiveCell.Value

    ' rngLastCell is the range of the curtent active cell.
    Set rngLastCell = ActiveCell

End Sub

Thank you so much for you help! I appreciate you taking the time to write that code! When I paste the code into the VBA editor and run it however, I get the below error to return.

 

[HighAndWilder]

When you say 'run it', how are you doing this?

 

[dmissel23]

When you say 'run it', how are you doing this?

I am clicking on the green play button at the top of VBA editor window. My apologies as I am quite new when it comes to VBA Macros.

 

[HighAndWilder]

Close the VBE editor. The code is run when changes are made in a table.

When you change a value in the table a pop up box will appear asking you what you want to do.

Just look at how it works on the worksheet and feedback any changes you need. The basics are in place, it may just need fine tuning.

I'm not sure where you are but maybe behind Greenwich Mean Time (GMT) so I'm logging off now but I'll pick up messages
in about 10 hours time.