Code Optimization for Inserting Comment for Mouseover Using VLookup

Hi,
I am very new to programming in VBA for excel. I have created a working piece of code that looks up a value in a different sheet using Vlookup and then places that value into a comment box, and lastly resizes the comment box to fit the text.

This allows the comment to be shown while the mouse is hovering over the cell for quick reference.

The code also erases the comment when the information in the cell is erased or a piece of text is used in place of a number in the cell, and places a pop-up when a number value is not on the lookup list. This is because the column that I am searching in is all numbers, and if someone is placing a letter in the cell, then the user has not defined the project number yet, so it is okay as a place holder till a project number is assigned, but I want to warn the user if they place a number that is not on the list.

The code also tries to capture as much error checking as possible.
Since I am new to VBA, I would appreciate some feedback on how to optimize this code (ie. run faster, smoother, eliminate other possible errors, etc...).

Thanks for the feedback in advance.

Code:

Please Login or Register  to view this content.

Interesting Project

Welcome to the forum

However you will get few responses as you have breached one of the forum rules. Sorry.




Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Your code looks good to me

You do however fave two redundant lines

You also omitted to reset default error handling.


My edited Version:

Please Login or Register  to view this content.

You Version with ammendments:-

Please Login or Register  to view this content.