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

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.

Trying to take something as unstructured as a person's name is not a good way to name sheets. Does the sheet tab name really have to be the employee name? I would just number them and create a link to each one on the Section_Data sheet. I have done this for employee training reports. Aside from special characters you also have to worry that once you get a clean name that's 31 characters it will be a duplicate.

Do you need help with it? Like I said, I have done something similar.