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

 

[Jeffrey Mahoney]

Do you have a Worksheet_Change event for that cell address?

 

[bisel]

Do you have a Worksheet_Change event for that cell address?

Yes I do. Here is the change event ...

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
        Application.Run "change_display_years"
    Else
    End If
  
End Sub

You raised a good point, so I commented out the above change event code and the issue went away. Putting that change event back in and problem returns. So it would appear that this change event is a factor (if not the only factor).

I need the change event. Do you see a way that I can retain the ability to run a macro when the cell value changes?

Thanks,

Steve

 

[Jeffrey Mahoney]

What cell address is the range display_years? Is it your pulldown cell?

What is in this macro?
Application.Run "change_display_years"

 

[bisel]

Do you have a Worksheet_Change event for that cell address?

I tried this, but the problem is still there ...

I saw a thread that recommended turning off Application.EnableEvent before calling the macro. Tried that, but the problem still stays. Here is the code I tried to use ...

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

Set KeyCells = Range("display_years_homesheet")
    
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Call change_display_years
    Application.EnableEvents = True
Else
End If

Best regards,

 

[Jeffrey Mahoney]

Please post the code in change_display_years. I believe that is changing the cell to nothing

 

[bisel]

Please post the code in change_display_years. I believe that is changing the cell to nothing

Hi Jeffrey,

Here is the code to "change_display_years" ...

Sub change_display_years()
Application.ScreenUpdating = False

' Unprotect all sheets

Application.Run "unprotectsheets"
    
'    For Each Worksheet In ActiveWorkbook.Worksheets
'        Worksheet.Unprotect
'    Next

    
' Set display columns

    For Each c In Sheet2.Range("hidecols_calcsheet")
       c.EntireColumn.Hidden = (c.Value = 0)   'Hide columns with value of 0 in tested range
       Next c

    For Each c In Sheet8.Range("hidecols_fdsheet")
       c.EntireColumn.Hidden = (c.Value = 0)   'Hide columns with value of 0 in tested range
       Next c
     

    For Each c In Sheet18.Range("hiderows_incomesheet")
       c.EntireRow.Hidden = (c.Value = 0)   'Hide rows with value of 0 in tested range
       Next c
    

' Protect all worksheets in workbook and allow editing of objects
    For Each Worksheet In ActiveWorkbook.Worksheets
        Worksheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True
    Next

' After protecting all sheets, unprotect the Data Validation sheet to ensure no problems with protected cells
    Sheet5.Unprotect
       
' Set special protect to allow sorting / filtering on sheet1
    Sheet1.Protect AllowSorting:=True, AllowFiltering:=True
    Sheet1.EnableSelection = xlUnlockedCells

' On Sheet6 allow selection of drawing objects and allowformatting columns
    Sheet6.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True

' Customize sheet 16 (home sheet)to allow inserting photos on home page
    Sheet16.Unprotect
    Sheet16.Range("protectstatus").Value = "Protection ON"
    Sheet16.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True

Application.Run "protectsheets"


End Sub

Sheet16 is the sheet I am experiencing problems with, but I do not see anything that is causing the cell to loose the value entered.

Regards,

Steve

 

[Jeffrey Mahoney]

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.

 

[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

I did as you suggested, and in the process there was no step which caused the problem. But, in further investigation, I am finding that if I select a different sheet (any sheet) and then come back to the first sheet and try then try to change the value, the problem reappears. I have also found that if I move the cell to another place on the sheet, the problem goes away. This is driving me mad.

Steve

 

[Jeffrey Mahoney]

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