Red Comment Indicator Location (1 Viewer)

[EzGoingKev]

Good morning.

When I add a comment to a cell it puts a red triangle in the upper right corner of the cell to denote there is a comment there.

Is there a way to change the location to the upper left corner?

 

[bob fitz]

Good morning.

When I add a comment to a cell it puts a red triangle in the upper right corner of the cell to denote there is a comment there.

Is there a way to change the location to the upper left corner?

I've never noticed this behavior in Access before. What version are you using and what exactly is the "cell" that you refer to.

 

[Minty]

@bob fitz - It's an Excel thing.

Short answer is no from here https://www.mrexcel.com/board/threads/move-comment-indicators-to-top-left-of-cell.392878/

 

[isladogs]

@EzGoingKev
Sorry. I'm not aware of any method you can use to move it to a different location

@bob fitz
The question refers to Excel

EDIT Minty beat me to it!

 

[bob fitz]

@isladogs and @Minty

Thank you gentlemen. I hadn't realised that the thread was in the Excel forum and I haven't used Excel for many years.

 

[EzGoingKev]

OK thanks.

I put together a spreadsheet that one department needs to populate the fields. I then import the sheets into Access and manipulate the data to build load sheets for a program.

The guy populating the data cannot keep the data formats consistent. I added notes to the field heading so he could see how the fields need to be populated. The problem is they keep the filters on and the filter indicator blocks the little note notifier. If he does not see the notifier how is he supposed to know there is one there? I told him to put the mouse on the field and if there is one there it will pop up. That is too much work.

 

[Micron]

The guy populating the data cannot keep the data formats consistent.

This may not be necessary to worry about. Not sure exactly what you mean by formats or how you're migrating the data to Access, but a common approach is to have a table in Access that is only for imported data when doing TransferSpreadsheet function. You don't worry about Text vs Number or such things. Then you run update/append queries to add/edit to an Access table that has the correct data properties in its fields. The data is automatically modified in the update/append. You can also use queries or code to validate/correct the raw data if need be.

 

[Isaac]

The problem is they keep the filters on and the filter indicator blocks the little note notifier. If he does not see the notifier how is he supposed to know there is one there? I told him to put the mouse on the field and if there is one there it will pop up. That is too much work.

I've been annoyed by this in the past too. But if you increase the height of that header row, it should solve your problem for the most part. In combination with this, it may be helpful to change the setting to always show comments (so that hover is not needed). But the row height increase is necessary to give the comment 'room'.
You might programmatically adjust this (if his input sheet has anything to do with an automated process), or, you might just
1) unlock all cells in Format Cells
2) lock the header row in Format Cells
3) protect the sheet (allowing for filtering and whatever he needs to be able to do - quite flexible these options)

 

[Isaac]

 

[CJ_London]

Another option is to move your headings from row 1 to row 2 and put the required format in the first row.

If you want a stronger solution....

A client used an excel spreadsheet as a data entry form and I had the same problem of users putting notes against a date, putting two id's in the same cell etc.

I wrote a vba routine which ran when the user changed row (within the designated 'data entry' area) which validated the data for the whole record (row). There were around 30 columns and checks including data was of the right type and format, was typically in range, had been completed if required, etc. Any errors found were added to the end column as a note and the A1 cell would flag up there were errors and the record would be ignored when submitted for upload to the main db.

The upload was done with a submit button - a vba routine copied all rows completed without an error (it would do a final 'sweep' to ensure all validation have been completed) to a new workbook, and emailed the new workbook to the db user. Once the file had been emailed, a backup of the original workbook was filed in an archive folder with a date/timestamp and all completed rows deleted ready for further input by the excel user.

 

[Isaac]

That is a great suggestion, CJ_London. Especially if the data entry person is only entering data in one cell at a time (i.e. not pasting into numerous), then a lot of Change event validation is possible (and a lot of fun!), either using an immediate validation with Application.EnableEvents=False > Application.Undo > Application.EnableEvents=True, or, as CJ suggested, validating based on rows or other buckets.

 

[CJ_London]

users would copy/paste a cell at a time as well as manual entry. Since some columns are dependant on others for validation, that is why I only validated when the user left the row

 

[EzGoingKev]

This may not be necessary to worry about. Not sure exactly what you mean by formats or how you're migrating the data to Access, but a common approach is to have a table in Access that is only for imported data when doing TransferSpreadsheet function. You don't worry about Text vs Number or such things. Then you run update/append queries to add/edit to an Access table that has the correct data properties in its fields. The data is automatically modified in the update/append. You can also use queries or code to validate/correct the raw data if need be.

The guy screws so much stuff up every sheet the tables to compensate would be three times the size of the original form.

I am going to add the notes on what has to be in the fields and just rejecting the forms if the data is not correct.