Cell Value reverts to blank after entering a value

[bisel]

I am seeing a very strange problem that I am unable to determine the cause. Hoping someone here may be able to help.

I have a cell on one of the pages of a workbook with simple data validation to allow one of three values (10, 20, or 30)

After selecting (or entering) the value, the cell briefly displays the value entered and then immediately blanks out. So the value of the cell is now a blank (i.e., no value at all). I have tried clearing data validation, but that does not change the behavior. If I move the cell to another spot on the worksheet, then the behavior reverts to normal.

Does anyone have an idea why this is occuring?

Thanks,

Steve

 

[bisel]

Yeah, I don't see anything in there either. Do you know how to debug a macro?

Put your cursor on the first code line and press F9 so that it shows like this. Change the value in that cell. The macro will stop and you can press F8 and the macro will highlight the next line its going to execute. Pressing F8 again continues the macro one line at a time. Keep doing this until you find a line that alters that cell.

Make sure to put the VBA window on another screen or out of the way of the workbook so you can see the cell and see when it changes.

View attachment 120113

Trying to rule out some things.

Modified my change event code to this ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


Set KeyCells = Range("display_years")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

MsgBox Range("display_years").Value

Else
End If

End Sub

So now when changing the value of the cell, I merely have a message box telling me the value. So far so good, works as expected.

But if I select another sheet and then come back to this sheet and try to change the value again, the value disappears.

I do have a macro than runs when I select the sheet. I cleaned up the code that runs when I select the sheet and things seem to be working OK now.

This is the code that was there

Private Sub worksheet_activate()
Dim shtprotect As Boolean

Application.ScreenUpdating = False


ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1


If Sheet16.ProtectContents = True Then
    shtprotect = True
    Sheet16.Unprotect
Else
    shtprotect = False
End If


'Hide column AH
Columns("AH").EntireColumn.Hidden = True



    Sheet16.Range("A1").Select


If shtprotect = True Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
  Else

End If
    
Range("A1").Select
ActiveWindow.ScrollRow = 1


Calculate
Application.Calculation = xlCalculationAutomatic

End Sub

And this is the cleaned up version ...

Private Sub worksheet_activate()
Dim shtprotect As Boolean

Application.ScreenUpdating = False

ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1


If Sheet16.ProtectContents = True Then
    shtprotect = True
    Sheet16.Unprotect
Else
    shtprotect = False
End If

If shtprotect = True Then
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
  Else

End If

Calculate
Application.Calculation = xlCalculationAutomatic

End Sub

I have no idea why the older code may have been causing an issue. Do you see anything other than that is a bit of mess?

Steve

 

[Jeffrey Mahoney]

OMG, that makes no sense. The original code didn't have anything there to change that cell.

 

[bisel]

There has to be another event causing this. Worksheet_Activate, Worksheet_Deactivate, Workbook_SheetActivate, Worksheet_SelectionChange. Search for any of these, or search for that cell address, or search for that cell Named Range

I spoke too soon. The problem is still there.

I checked all my code for that cell address. Cannot see anything that is causal factor. As I mentioned, if I move the cell to another spot of the sheet and try it it works fine. That is the change event gets triggered and runs the "change_display_years" macro. The problem only occurs when I select another sheet and then come back to this original sheet. I checked the code that runs when I select the sheet and do not see anything that may cause this.

Steve

 

[Jeffrey Mahoney]

Did you go into the ThisWorkbook module?

 

[Jeffrey Mahoney]

When you searched for that address, did you select 'Current Project' as the search range?

 

[Jeffrey Mahoney]

One suggestion, you should consider using Code names instead of sheet names or sheet numbers as reference.

In VBA, turn on Properties Winder under View. Then in your code you can reference a sheet like this:
Main.Range("display_years").Value = 0

Even if the sheet name changes on the tab or it changes its location in the tab lineup, you will never reference the wrong sheet.

 

[bisel]

There has to be another event causing this. Worksheet_Activate, Worksheet_Deactivate, Workbook_SheetActivate, Worksheet_SelectionChange. Search for any of these, or search for that cell address, or search for that cell Named Range

Hi Jeffrey,

I have modified everything to remove change events on the worksheet.

I created a group of option buttons and an array associated with the buttons

Clicking an option button will toggle the corresponding cell in the array beneath the option buttons True or False. Immediately to the right of the True/False cells is the corresponding value for Display Years that I want to use. The cell called "Display Years" is merely an INDEX MATCH formula ... =INDEX(AB7:AC9,MATCH(TRUE,AB7:AB9,0),2)

I do not have any event macros for the option buttons.

Everything works as expected until I select a different worksheet. When I return to this worksheet and then click one of the option buttons, the cell "display_years" blanks out. No formula, no value. Just blank. I confess that this occurrence is not entirely predictable. Sometimes this occurs as I have described and sometimes everything is left as it should be. If I attempt to enter any data in that cell, once I press Enter, the cell returns to blank. If I move the cell to another location on the worksheet and then move it back to the original position, then I can enter a value or formula.

Very strange.

Steve

My conclusion is this problem is not related to triggering a change event on that cell.

 

[Skyybot]

Not to interfere here, it seems like you guys are tracking down the problem. But when I run into an issue like this, I will add a Watch for that cell value and select the option to "Break When Value Changes".

 

[bisel]

Just updating this thread ...

I was able to get around whatever was causing this issue by doing three things ...

1. Instead of using an ActiveX object such as radio buttons or a list box, I used a simple cell entry with data validation to only allow the values of 10, 20 or 30. I gave this cell a range name of "display_years_homepage".
2. I created a cell with the formula = display_years_homepage. I gave this cell the range name of "dispyears".
3. I substituted all references to the range name, "display_years" using instead the range name of "dispyears".

I do not have any idea if any of the above was the silver bullet. But I believed that simplifying everything was the best approach to resolving this issue. I am leaning toward item 3 as the potential silver bullet. Methings that Excel took some issue with that range name.