Saving value to 0 and white, but WS change happens

[Aaron DOJ]

My worksheet (WS) has examples in many of the field to assist user with what to put in each field, but part of my issue that I am now finding is when I put in numerical values. At first I had to change to white when print or save and visually that is great until you realize that those numbers were being added into the total numbers still. Clearly I could not have my examples numbers being added to a users numbers so I put in .Value = "0" ( see underlined below), but now when I save I can't get the code to run "change text to white" when I save the first time, it works if I hit save for a second time though. Is there any thoughts on how to zero out the cells I need without it triggering the Change WS? or let it trigger, but still change to white the first time anyone hits save?

Private Sub Workbook_BeforeSave(Cancel As Boolean)

If IsEmpty(Cells(35, 15)) Then 'if empty, put in "Value" and make value change text to white.
    Cells(35, 15).Value = "3.411"
    Cells(35, 15).Font.ColorIndex = 2
ElseIf Sheet1.Cells(35, 15).Value = "3.411" Then 'if equal to "Value", change text to white.
     [U]Cells(35, 15).Value = "0"[/U]
     Cells(35, 15).Font.ColorIndex = 2
ElseIf Sheet1.Cells(35, 15).Value <> "3.411" Then 'if text other than "Value", change text to black.
    Cells(35, 15).Font.ColorIndex = 1
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(35, 15)) Then 'if empty, put in "Value" and make value change text to teal.
    Cells(35, 15).Value = "3.411"
    Cells(35, 15).Font.ColorIndex = 14
ElseIf Sheet1.Cells(35, 15).Value = "3.411" Then 'if equal to "Value", change text to teal.
    Cells(35, 15).Font.ColorIndex = 14
ElseIf Sheet1.Cells(35, 15).Value <> "3.411" Then 'if text other than "Value", change text to black.
    Cells(35, 15).Font.ColorIndex = 1
End If


End Sub

-Aaron

 

[Aaron DOJ]

Bump for review please

 

[Aaron DOJ]

I am shocked, nearly 24hrs and still no masterminds with any kind of input (good, bad or indifferent)

 

[Joe4]

Quite frankly, I find it a bit confusing, and it is hard to say what is going on without seeing what your data looks like.

One thing that sticks out to me is references like this, which look a little funky to me:

Cells(35, 15).Value = "3.411"

".Value" is usually used with numbers, but putting quotes around characters indicates literal text, not numbers.
So even though it may work, it seems a little contradictory to me, and make me question what is really in that cell?
Is it the text entry "3.411" or a numeric entry of 3.411?

Also, for readability-sake, I do not like a lot of "ELSE" statements in my VBA code. I think it makes it hard to read and follow.
I think CASE statements typically are easier to read (see: https://www.techonthenet.com/excel/formulas/case.php).

I would recommend posting a simple example that is not working the way you want that we can follow to recreate on our end.
So, we would need to know the values in the pertinent cells, and anything else that you might have done to them.
In other, tell us exactly how we can recreate the problem on our end so we can see happening exactly what you do.

 

[Aaron DOJ]

Thank you Joe,

Please help me, to help you, to help me Part of not knowing, is why we are here begging for your help and with that means we may not always use the right terminology.

If it would help you I could send you a copy of the workbook?
The code you reference, I want it to be 3.411, the cell is formatted as "Accounting" to one decimal places. The reason I have it set to 3.411 instead of 3.4 is the fact that it would be possible at some point that a user could put in 3.4 into that space and if they did my code would change it to 0 and then white it out, I didn't want that to possibly happen so I added two 1's to my code even though the example text will still show 3.4.

Thank you for informing me of CASE, I did not know of that.

I see now I had not copied over the code correctly for BeforeSave the first time...I can't seem to locate where the edit button is on the forum to edit my post. Anyways the code is updated below, as requested I can duplicate the issue using this small code sample:

Sheet1-

Private Sub Worksheet_Change(ByVal Target As Range)


If IsEmpty(Cells(35, 8)) Then 'if empty, put in "Value" and make value change text to teal.
        Cells(35, 8).Value = "e.g Hotel to Training"
        Cells(35, 8).Font.ColorIndex = 14
    ElseIf Sheet1.Cells(35, 8).Value = "e.g Hotel to Training" Then 'if equal to "Value", change text to teal.
        Cells(35, 8).Font.ColorIndex = 14
    ElseIf Sheet1.Cells(35, 8).Value <> "e.g Hotel to Training" Then 'if text other than "Value", change text to black.
        Cells(35, 8).Font.ColorIndex = 1
End If


If IsEmpty(Cells(35, 15)) Then 'if empty, put in "Value" and make value change text to teal.
        Cells(35, 15).Value = "3.411"
        Cells(35, 15).Font.ColorIndex = 14
    ElseIf Sheet1.Cells(35, 15).Value = "3.411" Then 'if equal to "Value", change text to teal.
        Cells(35, 15).Font.ColorIndex = 14
    ElseIf Sheet1.Cells(35, 15).Value <> "3.411" Then 'if text other than "Value", change text to black.
        Cells(35, 15).Font.ColorIndex = 1
End If


End Sub

ThisWorkbook-

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Cells(35, 8)) Then 'if empty, put in "Value" and make value change text to white.
        Cells(35, 8).Value = "e.g Hotel to Training"
        Cells(35, 8).Font.ColorIndex = 2
    ElseIf Sheet1.Cells(35, 8).Value = "e.g Hotel to Training" Then 'if equal to "Value", change text to white.
        Cells(35, 8).Font.ColorIndex = 2
    ElseIf Sheet1.Cells(35, 8).Value <> "e.g Hotel to Training" Then 'if text other than "Value", change text to black.
        Cells(35, 8).Font.ColorIndex = 1
End If


If IsEmpty(Cells(35, 15)) Then 'if empty, put in "Value" and make value change text to white.
        Cells(35, 15).Value = "3.411"
        Cells(35, 15).Font.ColorIndex = 2
    ElseIf Sheet1.Cells(35, 15).Value = "3.411" Then 'if equal to "Value", change text to white.
         Cells(35, 15).Value = "0"
         Cells(35, 15).Font.ColorIndex = 2
    ElseIf Sheet1.Cells(35, 15).Value <> "3.411" Then 'if text other than "Value", change text to black.
        Cells(35, 15).Font.ColorIndex = 1
End If


End Sub

Once that is in put 3.411 in O35 (format cell to accounting 1 decimal if you want, I don't think this matters as I tried many different formats all the same result)
And e.g Hotel to Training in H35

Now go save and only the number will change to zero and white out, e.g Hotel to Training will still be there, unless you save again. I have another code when you open the book it will change those zeros back to what number I want them to be, let me know if you need that.

Thank you Joe for pointing out where you needed help to try and help me, I truly hope the above makes sense now.
-Aaron

 

[shg]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error GoTo Oops
  Application.EnableEvents = False

  With Target
    Select Case .Address
      Case "$H$35"
        Select Case .Value
          Case "", "e.g Hotel to Training"
            .Value = "e.g Hotel to Training"
            .Interior.Color = RGB(0, 128, 128)
          Case Else
            .Interior.Color = vbBlack
        End Select
      
      Case "$O$35"
        Select Case .Value
          Case "", "e.g 3.4"
            .Value = "e.g 3.4"
            .Interior.Color = RGB(0, 128, 128)
          Case Else
            .Interior.Color = vbBlack
        End Select
    End Select
Oops:
    Application.EnableEvents = True
  End With
End Sub

 

[Joe4]

OK, you have a few things going on here that I think are interfering with each other.

First, with "Worksheet_Change" procedures, usually you program it to run the code contained within it only if certains cells (or range of cells) are updated.
Otherwise, any change to anywhere on the sheet will trigger the code to run, and you usually do not want that to happen.
The "Target" range is the range that is updated that triggers the procedure to run. So we use that to do that check.

For example, if you only want that code to run if cell O35 is updated, you could put something like this at the top of the Worksheet_Change code:

If Target.Address(0,0) <> "O35" Then Exit Sub

What that says if the cell just updated is not cell O35, then exit the procedure without doing anything else.

Second, if your Worksheet_Change procedure makes changes/updates to certain cells, you may need to temporarily enable events while it does that, or else it will keep calling itself (and you could get caught in an infinite loop). Think about a simple example. If we had a Worksheet_Change procedure that anytime someone added a number to cell A1, we want to add 1 to it. So what happens? We enter something in, and then the procedure runs and adds 1 to it, but that (in and of itself) is another change to cell A1, so it calls the procedure again! And then you are caught in an infinite loop and your computer locks up or crashes!

So, when changing values directly in a Worksheet_Change procedure, you often want to surround those changes in a block like this:

Application.EnableEvents = False
' code that does changes
Application.EnableEvents = True

Worksheet_Change is an Event procedure. So by temporarily disabling events while we make our changes, it prevents the procedure from calling itself by the changes it just made.

Note too that your Workbook_BeforeSave event procedure is also making changes to the sheet. So, those changes are going to then call your Worksheet_Change procedure and run that too. If you do not want your Workbook_BeforeSave event to trigger the Worksheet_Change procedure to run, you will need to temporarily disable events from running there too while it makes its changes.

 

[Joe4]

One other note.

Sometimes when you have these automated procedures interacting with one another, it can be difficult to figure out what exactly is happening. One way to help figure that out is to add breakpoints near the top of the two event procedures you posted, then test making your changes or saves. That will stop the macro at the breakpoint, and you can press F8 to go through it line-by-line. Then you will be able to see exactly what parts of the code it is hitting, and where it is jumping too (and if it goes into the code a second time).

It is a very handy debugging tool for stuff like this.

 

[Aaron DOJ]

Joe,

You truly are a genius, adding that simple Application.EnableEvents = False & Application.EnableEvents = True around that section worked like a charm. Thank you so much for putting in all of that effort in explaining things and teaching me some new things. I believe coding wise I am just about done with my project, it has been quite the learning experience. Should I need anymore help I will for sure be back THANK YOU!!!!

SHG,

Thank you for your suggestion as well

-Aaron

 

[Joe4]

You are most welcome.
Come back anytime!