After using Find, typed values do not display until switching sheets.

[rossi789]

Hi,

I have been passed a problem by a colleague. They use Find to locate a cell, then enter a value alongside this cell but these values do not display in the cell. They do display in the formula bar.

The values appear when switching to another sheet and back again.

This only seems to occur after using Find. After switching sheets values can be typed and appear immediately but only until Find is used again.

The problem is limited to a single workbook. Any suggestions?

Thanks in advance.

 

[Joe4]

They use Find to locate a cell, then enter a value alongside this cell but these values do not display in the cell. They do display in the formula bar.

Not quite clear what you are saying they are doing after "Finding" the cell.
Can you provide more details, maybe an example?

Also, did you check to make sure that Automatic Calculation is set?

 

[rossi789]

They use Find to search a product code which for example returns cell A100, they then enter quantity data in cells C100 and D100. These cells appear blank but the values are visible in the formula bar. Values appear after switching sheet and back again.

This only occurs after using Find.

Automatic Calculation is turned on and I have cleared conditional formatting.

 

[Joe4]

I cannot reproduce that behavior.

Which version of Excel are you using?
Which version of Excel was the workbook created in (and what is the file extension)?
Are you up-to-date on all patches?

Do you have any VBA code in the Sheet module for that sheet? If so, please post the code here.

 

[rossi789]

Excel 2016 - via Office 365

The workbook had been .xls for many years but in March was changed to .xlsm. This behaviour has occured in the last 3 or 4 weeks but also occurs in archived versions of the file, both .xls and .xlsm.

Patches are up to date and the behaviour has been tested on multiple machines and multiple user accounts. It occurs on all of them.

Code found in the sheet below.

Private Sub CommandButton1_Click()

    Sheets("Blank").Select
    Sheets("Blank").Name = "NEW"
    Sheets("NEW").Select
    Sheets("NEW").Copy Before:=Sheets("data")
    Sheets("NEW (2)").Select
    Sheets("NEW (2)").Name = "BLANK"
End Sub

There are 2 Modules with the following code. I am not familiar with VBA so I am unsure if it is relevant.

Sub Macro1()'
' Macro1 Macro
' COPY BLANK
'


'
    Sheets("Blank (7)").Select
    Sheets("Blank (7)").Copy Before:=Sheets(8)
End Sub

Sub Macro3()'
' Macro3 Macro
'


'
    Application.Run "'WIP BOOK LIVE.xls'!Macro1"
    ActiveCell.Offset(5, 4).Range("A1").Select
End Sub

 

[Joe4]

Hmmm... i wonder if something did not convert over quite right.
If you try the same thing on a brand new workbook, does it exhibit the same behavior?

 

[rossi789]

I thought this as well, so I tested archived versions of the workbook prior to conversion. The same behaviour occurs which I guess eliminates a conversion issue.

No other workbooks exhibit the behaviour. Brand new or old.

I would just start a brand new workbook but this one contains hundreds of columns and rows, I don't particularly fancy replicating all the formulas and formatting!

 

[Joe4]

There could be a number of things affecting it (Formatting, VBA,etc).
Without having access to the file to try to evaluate it, I don't know if there is much else we can do.

 

[rossi789]

And I believe that is against forum rules?

 

[Joe4]

And I believe that is against forum rules?

It is not that it is against forum rules, it is that you are unable to attach files here.
What many people do is upload their file to a file-sharing site (like DropBox), and provide a link to it.
However, many people cannot/will not download files for security reasons (especially files with VBA code), but some do.
I, myself, cannot download files at work, but can at home.