Need Help with event

[auto998]

Hello,
I have been looking into this for awhile. I have a very basic knowledge with excel 2003.
I am trying to create a macro based on events. The event should be triggered as soon as the user leaves the cell by clicking on a different cell or by using the arrows on the keyboard. So if the user type SONY in cell A1, A2-A10 will be populated right on the spot automatically based on the word Sony, for example A2 will have electronic populated automatically, A3 will have <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Japan</st1lace></st1:country-region>...and so on <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If the user enter GMC instead in cell A1, A2-A10 will be populated right on the spot automatically based on the word GMC. A2 will have Automaker populated automatically, A3 will have <st1:country-region w:st="on"><st1lace w:st="on">USA</st1lace></st1:country-region>, ...and so on.
After I created a small VB sample it looks like I need to press the execute button every time to run the program in VB instead of triggering an event automatically.
Can you please help? Here is a small sample I am using to test:
<o></o>
Sub test()<o></o>
<o></o>
<o></o>
If Range("a1").Value = 10 Then<o></o>
<o></o>
Range("c1").Value = "Yes"<o></o>
Else<o></o>
Range("c1").Value = "No"<o></o>
End If<o></o>
<o></o>
<o></o>
End Sub

 

[VoG]

Hello and welcome to MrExcel.

You might want to look into VLOOKUP to do this sort of thing - see http://www.contextures.com/xlFunctions02.html

If you want to use code try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Select Case LCase(Target.Value)
        Case "sony"
            Target.Offset(1).Value = "electronic"
            Target.Offset(2).Value = "Japan"
        Case "gmc"
            Target.Offset(1).Value = "automaker"
            Target.Offset(2).Value = "USA"
    End Select
    Application.EnableEvents = True
End If
End Sub

Then press ALT + Q to return to your sheet. Enter Sony in A1 - does the code do what you want?

 

[auto998]

VoG,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks for taking the time to give me some help. VLOOKUP would not work because at some point I need to save the file as Tab Delimited and upload it into another system that would expect a certain format.<o></o>
As I mentioned I am not experienced in this area. The code did not work. I created a new excel file and I called it test then I pasted your code into VB editor...Then press ALT + Q to return to the sheet. When I entered Sony in A1...nothing happened. Anything I did wrong?<o></o>

 

[lenze]

Peter's code does work. Did you follow his instructions on where to place it? Also, what difference does it make for future exporting, how the data arrives in the cells? I don't see that VLOOKUP would cause you a problem and, it is probably the way to go.

lenze
Edit: For a discussion of Events in Excel, see Chip's article here

 

[auto998]

lenze,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks. It worked now. I did not right click on the sheet, instead I was going to Tools-Macro-VB editor, my fault.<o></o>
As far as VLOOKUP. The user will enter the data manually (type). When the file is ready, it will be saved as Tab Delimited and uploaded into the system that expects a certain format. Having the "VLOOKUP table" in the Tab Delimited file will cause an error....unless there is a way not to show the "VLOOKUP table" in the Tab Delimited file.............Thanks again for both of you <o></o>

 

[auto998]

One more question. I have an excel (2003) file that have over 15,000 links to pics. Every cell has one link to a pic (www.pic.com). Is there an easy way to make those links to become pics. In other word every cell that has a link will replace the link with a pic?

 

[auto998]

Hello, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Disregard my previous post, I am going to post it as a new thread, Sorry about that. Going back to my main issue, If I want to apply the event on the entire row 1. For example, if I type Sony anywhere in Row 1, could I have the output on the same column below it. Thanks

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then
    Application.EnableEvents = False
    Select Case LCase(Target.Value)
        Case "sony"
            Target.Offset(1).Value = "electronic"
            Target.Offset(2).Value = "Japan"
        Case "gmc"
            Target.Offset(1).Value = "automaker"
            Target.Offset(2).Value = "USA"
    End Select
    Application.EnableEvents = True
End If
End Sub

 

[auto998]

Thanks VoG. Is there a way to avoid using references and instead use the column or row name? Also, can I store the names of the columns or rows into variables and use them later instead. For example, Store column A name in a variable, Manufacture = A. So in the code instead of referencing to A, I’ll be using manufacture. Is this doable? Thanks again.

 

[VoG]

You could create named ranges and refer to them in the code. However I'm really not sure why you would want to do this.