How to do the following task

[Dave]

Hi,

I have a spreadsheet that conatains a comapny code in cell A3 to A150.
This company code uniquely identifies a comapny. These codes ones
assigned are not transferred to another company. What I want to do is
when a user clicks in a cell with the company code I want to reference
its company name. This way the user is not running around looking up
company names that are stored an another excel file . The company
codes are as follows:

0010-BBBB
0010-CCCC
0013-0000
0015-0100
0041-WOKS

So if a user clicks on cell a1 containing 0010-BBBB I want to return
Big Bass Outfitters and if they click in the cell containg 0010-CCCC
I want to display Canada Water and so on and so forth

I was thinking about some how adding automatic comments by looking up
the company codes against the company anme and inserting comments. But
I am not sure if its a good solution and if it can be done.

All suggestions are welcome.

Please reply here or reply to may email,

thanks,

dave

 

[David Heaton]

Hi,

I have a spreadsheet that conatains a comapny code in cell A3 to A150.
This company code uniquely identifies a comapny. These codes ones
assigned are not transferred to another company. What I want to do is
when a user clicks in a cell with the company code I want to reference
its company name. This way the user is not running around looking up
company names that are stored an another excel file . The company
codes are as follows:

0010-BBBB
0010-CCCC
0013-0000
0015-0100
0041-WOKS

So if a user clicks on cell a1 containing 0010-BBBB I want to return
Big Bass Outfitters and if they click in the cell containg 0010-CCCC
I want to display Canada Water and so on and so forth

I was thinking about some how adding automatic comments by looking up
the company codes against the company anme and inserting comments. But
I am not sure if its a good solution and if it can be done.

All suggestions are welcome.

Please reply here or reply to may email,

thanks,

dave

Dave,

If i make the assumption that the company names and the corresponding
code
are stored elsewhere in the workbook. Lets say Sheet 2 contains the
company code in Col A and the Company Name in Col B

Get into the VB editor (press ALT+ F11) and paste this code below


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CompanyNameRow As Range

'not clicked on company code
If Intersect(Target, Range("a3:a500")) Is Nothing Then Exit Sub
'clear existing comments
ActiveSheet.Range("a3:a500").ClearComments
'get row number of matching company name
Set CompanyNameRow = Worksheets("Sheet2").Range("A3:A150").Find
(Target.Text) ' change to your sheet name here
'add comment to selected cell
ActiveSheet.Cells(Target.Row, Target.Column).AddComment
ActiveSheet.Cells(Target.Row, Target.Column).Comment.Text CStr
(Worksheets("Sheet2").Cells(CompanyNameRow.Row, "b")) ' change to
your sheet name here


End Sub

You will need to change the sheet names to suit but this will add a
comment to the cell selected

hth

regards

David

 

[Dave]

Dave,

If i make the assumption that the company names and the corresponding
code
are stored elsewhere in the workbook.  Lets say Sheet 2 contains the
company code in Col A and the Company Name in Col B

Get into the VB editor (press ALT+ F11) and paste this code below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CompanyNameRow As Range

'not clicked on company code
If Intersect(Target, Range("a3:a500")) Is Nothing Then Exit Sub
'clear existing comments
ActiveSheet.Range("a3:a500").ClearComments
'get row number of matching company name
Set CompanyNameRow = Worksheets("Sheet2").Range("A3:A150").Find
(Target.Text)  ' change to your sheet name here
'add comment to selected cell
ActiveSheet.Cells(Target.Row, Target.Column).AddComment
ActiveSheet.Cells(Target.Row, Target.Column).Comment.Text CStr
(Worksheets("Sheet2").Cells(CompanyNameRow.Row, "b"))  ' change to
your sheet name here

End Sub

You will need to change the sheet names to suit but this will add a
comment to the cell selected

hth

regards

David- Hide quoted text -

- Show quoted text -

David ,

thanks Here is another thing I forgot to add to my original post, my
spreadsheet has tabs for the 12 months named as MAY09, JUNE09 and so
on. They are created at the end of the month so end of May I will
have May09.So how will I address that in your macro . Could I prompt
the user for a tab name in you macro?

My sheet where the lookup values are stored is called DND.

thanks in advance for all your help and of course the macro.

 

[David Heaton]

David ,

thanks Here is another thing I forgot to add to my original post, my
spreadsheet has tabs for the 12 months named as MAY09, JUNE09 and so
on. They are created at the end of the month  so end of May I will
have May09.So how will I address that in your macro . Could I prompt
the user for a tab name in you macro?

My sheet where the lookup values are stored is called DND.

thanks in advance for all your help and of course the macro.- Hide quotedtext -

- Show quoted text -

Dave,

I'm not completely sure what you are asking.

If you want to know how to use the macro in all the sheets then you
will just need to copy the macro to the
Worksheet_SelectionChange of each worksheet.

If you meant something else , please let me know

regards

David