Popup Comments

Original work is based on VolMan. This is 05/02/2017

Need

  • Table – tblComments_Name
  • Form – frmComments_Name
  • Module – mComments_Name
  • Sheet Before Double-Click – on column in DataRange
  • Top Panel modifications

Additional:

  • View to include the Latest Comment.
  • ExpandColumnToAutofit (on DoubleClick header)

In Access,

Create a new table

  • Copy paste tblComments (Structure only) and rename.

Create a view

  • Copy paste the SQL from qryLatestComment to a new query.
  • Change the table names from tblComments to tblComments_Name
  • Save as qryLatestComment_Name

Add the Latest Comment to the Main Query

  • Remember to give the new column the correct Alias.
  • Gotcha: If adding the Latest Comment causes a ‘doubling up’ of records, it’s because you have Date instead of Data and Time.

In Excel,

Create a new form

  • To duplicate the existing frmComments, change its name to frmComments_Name.
  • Export it.
  • Change the name of frmComments back to its original name.
  • Import frmComments_Name back.
  • Add _Name in relevant places. See Save button and Initialise events.

Create a new module

  • To duplicate the existing module mComments, create a new module and name it mComments_Name.
  • Copy and paste all the code in module mComments to mComments_Name.
  • Add _Name in relevant places.
  • Test the function GetCommentsAsAString(). First insert some dummy data in tblComments_Name.

Create Sheet Event – DoubleClick

  • Copy and paste the Double Click event.
  • Adjust the column number.
  • Change the form name to frmComments_Name.

Top Panel

  • Change column DirtyType to ‘Not Dirty’.
  • Change DataType to 203. Length to 0.