Results 1 to 9 of 9
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    formula help (Office XP)

    I have a column that has 10 numbers in it. I would like to be able to click on any one of those numbers and have it post to a different cell. Can I do this?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (Office XP)

    The code below, placed in the worksheet selection change event routine in the module behind the sheet where the numbers are locacted will do this. It assumes that the numbers are in A1:A10, and that the value in the cell selected is to be copied to cell B1.

    <pre>Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    Range("B1").Value = Target.Value
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: formula help (Office XP)

    ok I see what you are doing but help me out here. Where exactly do I put that? I think ...No.. I know you just went over my head.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula help (Office XP)

    Right click the worksheet tab.
    Select View Code from the popup menu.
    Select Worksheet from the dropdown list in the upper left of the module window that appears.
    Excel has already created the first and last lines of the event procedure for you.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    End Sub

    You only need to type or copy the two lines in between from Legare

  5. #5
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: formula help (Office XP)

    Thanks Hans
    I did that but the code appears in red, and it doesnt work.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (Office XP)

    The red indicates that you did not enter the code correctly. There is some kind of syntax error. If you copied the code from the message and pasted it, is it still on two lines? If you copy code from the lounge, the carriage returns between lines tend to get lost. You can copy the code lines, paste them into MS Word, then copy them from word and paste them into the module. Word seems to understand the HTML from the lounge, but the VBA editor does not. You can also find where the returns should have been and enter them manually.

    If you typed the code in, then check for typos.
    Legare Coleman

  7. #7
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: formula help (Office XP)

    ok did that and still not working. I am attaching the worksheet so you will be able to see what I am doing. The values in F32:F42 I want to appear in cell J32, when I click on them.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help (Office XP)

    The code was in a routine named "textbox1_Change", not "Worksheet_SelectionChange". Since it was in the module behind the worksheet, not the module behind a textbox, the code would be completely ignored. Try the attached worksheet.
    Legare Coleman

  9. #9
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: formula help (Office XP)

    Thank you very much. I see the mistake. That is exactly what I needed
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •