Adding Dynamic Comments based on the Target Cell Content..using a MACRO..

[e4excel]

Adding Dynamic Comments based on the Target Cell Content..using a MACRO..

Dear Forum,

I have partially achieved getting comments based on the value in the Target cell however this value is like a Unique No and needs to fetch data from different sheets however using the VLOOKUP function I have able to get this value and store it in a variable but passing it to a comment text for different target content is posing a problem..

For Representation , I have a File with three Sheets :
I - which contains the data concerning with Insurance and thus the name "I" short form for Insurance and
M - which contains the data concerning the Mutual Fund Investments and thus "M" the name for Mutual Funds
TRY - this is the sheet I wand to get the comments in the column B starting from Row = 2 till specified..

Now right now, what I am doing is that I have a Command Button in the TRY sheet which puts the Insurance and Mutual Fund Nos in the Column B from Row-2 till 16 randomly..

What I need is that based on the values in the cells B2 till B16 I need to get the details displayed in the comments for the same values as these values act as the unique Ids for the VLOOKUP function..

Now for a single value im able to do this but for a mixture between the Insurance and the Mutual Funds im facing difficulty..

Please find the code for the Command Button..

Please Login or Register  to view this content.

Now this Sheet is actually just a representation, however in my actual Sheet this data would be Auto -Generated in the column b..

Please Login or Register  to view this content.

I am not able to go beyond the Error 91..

I would also prefer to use a Select Case Statement if possible if thats more apt..

Thanks in advance..

Warm Regards
e4excel

[Artik_PL]

Please Login or Register  to view this content.

Artik

[e4excel]

Wow, Amazing Artik thanks a lot for the code..it works absolutely the way I wanted it..
But can you please also help me with a Select Case Statement instead of the IF-ELSE-END IF as there would be more sheets that would be referred for this comment text and if its easier than a IF-ELSE...

As I mentioned in my first post that this was a Dummy Workbook in my actual file the data which Im populating randomly will be produced on the click of a command button then in that case, is that possible to run the entire Macro for the Comments on the Button itself instead of resorting to the WorkSheet_Change event..?

SO basically, what I meant when I click the Button to display the random values in the COlumn B there itself I need to get the comments without needing to trigger this WorkSheet_Change event..

My intention is to have the details at my finger-tips by embedding them in the comments, can this also work if the same text would just go in the TOOL-TIPS text as that comes without any red-indicator corner arrow..?

If the need be I can start a new thread for the TOOL-TIP query..

Thanks in advance and zillion thanks for the solution till now..

Warm regards
e4excel

[e4excel]

I have tried this code its working amazing but can this be entered using a SELECT CASE and is this possible to have the same functionality in a TOOL_TIP?

[e4excel]

Can this be made into a SUb Routine by any chance where I can use it in mutliple sheets for different columns and starting at different Rows..
Now in the File I provide I had the comments displayed from B2 but what if I want it to be in some other columns in a Fixed Range lets say column F from F10 till Fx where x is Dynamic End Row..

Like for example I want to use this in such a way that I call this as a Sub Routine as my in my actual file I would want to use this two sheets so I can just call it..in both the sheets for different Ranges and also it can work as a part of other Macros..

Thanks in advance..

[e4excel]

This query is partially solved just need to know how do I use it as a Sub-Routine with different Ranges in Different Sheets with the same VLOOKUP - LOOKUP value..

Please this is really holding me up as this is beyond me...

Thanks in advance..

[e4excel]

Dear Forum,

This line is the one where the Range is provided in the Worksheet_Change Event ..

Please Login or Register  to view this content.

Now can this be provided where I can just pass different Columns to the code, I am not an expert and hence Im not able to do that on my own..please can someone please help me..

Thanks in advance..

Warm Regards
e4excel

[e4excel]

Dear Someone,

This is working well but can someone please help me on making this useful for a different range and not just a Fixed Range as I need to use the same Functionality in two different sheets and at different places though the comment data remains the same..

Please please I am not so good in VBA so not able to come up with something regarding the Intersect and Range coding..

Thanks in advance

Warm Regards
e4excel