Page 1 of 4 123 ... LastLast
Results 1 to 15 of 57
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hyperlink parents. (excel 2000)

    I have two columns, Parent P/N and BOM P/N(child). What I need to do is find every relative that is associated with either the parent or child and Highlight them. I have attached a sample of the situation. Please not the one in red and follow it. It will show exactly what I mean.

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

    Re: hyperlink parents. (excel 2000)

    Why not the one in red? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Try the following code. The first procedure is the macro to run.

    Private lngMaxRow As Long

    Sub Hilite()
    If ActiveCell.Column > 1 Then
    MsgBox "Please select a cell in column A.", vbExclamation
    Exit Sub
    End If
    lngMaxRow = Range("A65536").End(xlUp).Row
    If ActiveCell.Row = 1 Or ActiveCell.Row > lngMaxRow Then
    MsgBox " Please select a cell within the range.", vbExclamation
    Exit Sub
    End If
    Range("A2:B" & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
    HiliteMeAndKids ActiveCell
    End Sub

    Sub HiliteMeAndKids(oCell As Range)
    Dim i As Long
    oCell.Resize(1, 2).Font.ColorIndex = 3
    For i = 2 To lngMaxRow
    If Range("A" & i) = oCell.Offset(0, 1) Then
    HiliteMeAndKids Range("A" & i)
    End If
    Next i
    End Sub

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    What do you mean why not the one in red? the PR-05400 is the child I'm trying to follow. But he can also be a parent as well. If he is a parent I need to know any and all other children.

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

    Re: hyperlink parents. (excel 2000)

    You wrote "Please not the one in red". Do you have a special aversion to red? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>

  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    My deepest apologies, I'm not awake yet. That was supposed to read " Please Note the one in red" so that somebody could foolow if fron the BOM (Child) column to the parent column.

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

    Re: hyperlink parents. (excel 2000)

    Still not quite awake?

    Try the following:

    Private lngMaxRow As Long

    Sub Hilite()
    If ActiveCell.Column <> 2 Then
    MsgBox "Please select a cell in column B.", vbExclamation
    Exit Sub
    End If
    lngMaxRow = Range("A65536").End(xlUp).Row
    If ActiveCell.Row = 1 Or ActiveCell.Row > lngMaxRow Then
    MsgBox " Please select a cell within the range.", vbExclamation
    Exit Sub
    End If
    Range("A2:B" & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
    HiliteMeAndSiblings ActiveCell
    End Sub

    Sub HiliteMeAndSiblings(oCell As Range)
    Dim i As Long
    oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 3
    For i = 2 To lngMaxRow
    Debug.Print i
    If Range("A" & i) = oCell Then
    HiliteMeAndSiblings Range("B" & i)
    End If
    Next i
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    Ok, I'm back among the living. Hans, My apologies for yesterday I really was not with it at all. Please let me explain again what I'm trying to do here.

    The two columns are Column "H" and "I" what I'm trying to do is if the user clicks on a child in column "I", I would like for them to be able to able to see any and all items that are associated with it. whether it be a parent or child. The same goes for if the user clicks on a cell in the parent column. Is there a way to have the user Hyperlink to the next listing of the same item or to have a msgbox appear that shows all the items both parent and child as well as the Job number COLUMN "F" show.

    My main objective here is to make the user aware of any and all items associated with that particular Item as well as the job numbers.

  8. #8
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    Hello, one and all.

    I stated before that I did not understand what the instructions implied, Mainly because I did not understand what was expected.

    I informed my teacher at school and he informed me that I need to review the book a little more, well I did and still don't. Sometimes it just takes a while to click I guess.

    What is expected: Column "I" is the part needed to create the Part in Column "H" but the part in column "H" could also be waiting on another part to be created in Column "I". When all parts are in or have been created the Job can be released which means that the cell in column "B" turns Green.
    if only a partial order has been met (Not all parts are there or they still need to be created) then cell in column "B" is yellow. Otherwise cell in column "B" is blue. I also need to know if a PO number Column "w" is holding up the process as well. last thing I'm trying to accomplish is a message box that tells the user what is keeping the job from being created.

    Not that easy HUH?

    There it is folks. and no I do not want anybody to complete this for me. What I'm asking for a a little guidance. because if you complete it for me I will not be learning. Correct. Although I have been trying to break down what some the the programmers here are writing. Sometimes I understand it sometimes I dont. Thats when I ask for a simplier understanding of what a certain program does.

    thank you Gentleman for Understanding and helping.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    Ok with that any idea's On how to even start this? Anybody?

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

    Re: hyperlink parents. (excel 2000)

    > When all parts are in or have been created

    How can we know that a part is in or has been created? Is this specified somewhere in the spreadsheet? (Please give a clear and complete answer)

    > .. a PO number Column "w" ...

    What does that mean? (Please give a clear and complete answer)

  11. #11
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    I will try Hans, and thank you SOOOO much for getting back with me.

    It works like this. Column "H" has all the part names within it. (A total of 233 parts). While Column "I" has Parts in it as well. Now lets say that we are trying to make a part number Called for the sake of Argument "PART D". Part "D" would be found in Column H - but to build part D we need Parts A,B & C. Which would be found in Column I.

    Now we Need to Make Part E, which also is in column H. But Part E is made up of parts A,B,C & D. which are found in column I and so on.

    I need to be able to locate every reference of the Part number I'm looking for whether it be in column H or column I. If I click on a part number I should be able to notice all the other parts that are waiting on the part I need and to be able to see if a PO Number is holding it up. Thats exactly what the teacher said.

    You see the main part that is found in column H can also be a smaller part (Column I) used to make another main part in column H.

    Then I have to check Column W (the same row that the part is found on) to ensure it is blanks if not then I need to have a Popup message that states " Part is waiting on PO Number (then the number that was found in that cell).

    This one is very very Hard, It's not easy going back and fourth from excel to vb to excel/vb together. I hope that helps - copied it down word for word.

    My problem is I don't really know the shortcuts in VB/Excel yet and keep wanting to use IF statements.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: hyperlink parents. (excel 2000)

    But your example has none of the info required. Could you provide an example of what you have and what you need?

    how do you know whether the parts are available or not?

    What is in all those "new" columns that you are referring to?

    Steve

  13. #13
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    Here is the spreedsheet info we were given.

    If the parts are not available there would be a PO number in Column W.

    What we are trying to accomplish is given the info in column H, I and W. The user needs to know what the part is being held up by - whether its another part thats waiting on something else or is that part waiting on something that was purchased (PO Number). And they need to be able to see it clearly. So I suggested a Message box and he said that was ok.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: hyperlink parents. (excel 2000)

    If this is class assignment shouldn't you be doing this on your own? If we tell you how to do this, how does this help you learn? I suppose we can offer some suggestions and let you do the work.

    It is still not clear to me what you want and need. The last few descriptions have dealt with H, I, and W. How do A, B, C, D, E, and X work into it? Also why are some of the items in col A blank? and why do none of the columns have descriptive labels?

    Can you give some examples, based on this sample, of what you want the workbook to do?


    Steve

  15. #15
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink parents. (excel 2000)

    That's exactly what I'm looking for. Thats the reason why I have not been spelling it out directly to everybody so that I can monkey around with what you think I should do and try to figure it out on my own. As for the other columns They are being used by other data as well It's just that that data does not matter in the equation.

    Right now I have it set up so that if a part in column "I" is selected any parents it belongs that are associated with it in any way are highlighted. (Thanks to Hans, and a little tweaking). I'm hoping that some how I can set it up to include the PO Number (COLUMN W) within the equation and the give a message to the user stating " The part you are looking for is waiting on part number......whatever, or a PO Number ... But I'm not sure how to go about doing that.

    Please review the attachment and look at column "I" row 21 you will see number PR-05399
    If you now look at Column "H" row 18 you will see it's parent. but that parent is waiting on another part CNB42.079X6-G.
    I would also need to check Column W (Po #) to see if it's waiting on a purchased part or not.

    I have my code listed below but it works on every column. By that I mean If I click on a cell in column b anything in column A that resembles it would be hilited as well. I only want this to work on columns H and I.

    Plus How do I go about creatinmg the pop up message if the main parent is waiting on a PO # or another part.

    I'm Lost totally... And the teacher has already said he will not assist. This has to be done by the end of spring break in two weeks.


    Private lngMaxRow As Long



    Private Sub EXEC_BTN_Click()
    lngMaxRow = Range("I65536").End(xlUp).Row
    Range("H2:I" & lngMaxRow).Font.ColorIndex = xlColorIndexAutomatic
    HiliteMeAndSiblings ActiveCell
    End Sub

    Sub HiliteMeAndSiblings(oCell As Range)
    Dim i As Long
    oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
    For i = 2 To lngMaxRow
    Debug.Print i
    If Range("H" & i) = oCell Then
    HiliteMeAndSiblings Range("I" & i)
    End If
    Next i
    End Sub

Page 1 of 4 123 ... LastLast

Posting Permissions

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