Excel Read Text or String Length and Special Character And Then Correct The Runtime Error

[kkng]

Hi guys,

I am improving my VBA function for creating employee appraisal form.

The main function is working but facing problem when run the actual dataset.

Main reason is some employee names too long more than 31 characters while some with special character.

Is there any solution can implement inside my existing VBA to solve this issue?

Target to Achieve:
1) The main dataset (Section_Data) employee name in column must remain the same.
2) When auto creating employee name sheet can be shorten to 30 characters but the employee name form cell value C5 must have exactly same name as main dataset (Section_Data) column A.
3) The main dataset (Section_Data) column J10:J formula =IFERROR(INDIRECT("'"&$A10&"'!J51"),"") able to run if the employee name sheet shorten.

Thanks for advise.