Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting up a Lookup Table (2002)

    Hi,

    I am trying to set up a Section/Part Number/Dimension table as shown in the attached example worksheet. This table that will be accessible for a number of users that will be able to enter information as needed. The users should enter the part numbers using the appropriate buttons (ie, "insert into Level 1"), which should enable the user to jump to a new row in this category, where information will be entered. I also want to have a lookup table where I would enter the Section and Part Number (showing only those for the corresponding section, and preferably sorted) and will get the dimension.

    I started to create this template (see attached example) but I am not sure if I am using the best approach to this problem, that is, I don't know if the tools that I am using in Excel are best for my case, since I have very little knowledge of programming. Could someone let me know if I going about it in the right direction? Also, is there an example spreadsheet in this discussion forum or elsewhere in the Internet that I will be able to use as a starting place.

    Thanks for your help,
    Hanan.

  2. #2
    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: Setting up a Lookup Table (2002)

    I am not sure what you want to happen when you press any of the 3 buttons.

    Your pulldown lists also seems to have lots of blanks in it, what do you want to accomplish.

    Some links (if I understand a little):
    John Walkenbach has some code for Filling a ListBox With Unique Items and Debra Dalgleish's site has some info (if you want to use validation rather than coding) on creating Dependent Lists using datavalidation.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Steve,

    I check Debra Dalgleish's site with regard to data validation, but it is not quite what I am looking for. Firstly, I would like the 3 buttons will to create a new row below each of my sections. For example, if I would press the first button, I would go to row 19 or any row above "Level 2". If there are no empty rows above "Level 2", then It would insert a row. I don't really know how to do this in a macro in Excel.

    Secondly, I created comboboxes to list the sections (Level 1, Level2, etc.), and Part Numbers. I want to get rid of the blanks in the pulldown list, but don't know how to do this. Debra Dalgleish's site shows ways to obtain the list that I am looking for, as long as my table is organized in a certain way. Since others will use this spreadsheet to enter information, I would prefer to see the format as shown in my previous attachment. This would make the use of data validation difficult (right?).

    What is your thought on this.

    Thanks,
    Hanan.

  4. #4
    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: Setting up a Lookup Table (2002)

    John Walkenbach sites shows how to get a unique list from a range. If your list is just going to have names (unique) or blanks, you just have to read thru the list and add the items to the combobox if they are not blank

    You can do it with named ranges and as long as you insert the row within the range you will be fine (the name will expand).

    Otherwise you will have to determine where you need to insert the new row by searching through the range (find the item, then do an end-down to get to the next item, etc).

    I don't have time to work up anything tonite, but perhaps later this week if no one else responds with some code...

    Steve

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Thanks Steve for your response. Could you explain your answer in more detail. Now I am just confused.

    Hanan.

  6. #6
    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: Setting up a Lookup Table (2002)

    I had a little time to play with your workbook.

    Here is an edited version with a "first pass" at what I think you are after...

    If it is what you are after, I can explain anyg=thing you might not understand.

    Steve

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Thank you very much Steve. Excellent! This is what I want.

    Since I am novice to programming in Excel, I looked at your code and got pretty confused. I got a few questions for you:
    1. When you insert a new line when the command button is clicked (for example "Insert into Level 1"), how does it know to insert a line into the appropriate section?
    2. Could you insert a condition that will check if for the existance of two empty rows, for example between Level 1 and Level 2, or Level 2 and Level 3)? If there are already two empty rows, then go into the 1st row for user input. This will serve as a restrictions on the number of empty rows that the users could enter (by mistake). The users that are going to be using this spreadsheet are not familiar with Excel functions/capabilities.
    3. Could you explain to me the comboboxes list and links that you used? Also, how did you associate the Dimension with the Part Number?
    4. Is there a way to sort the list (by Part Number for each Section) every time a user enters a new data?
    5. Could a condition be could be set up that will check the Part Number and Dimension array (that already exists) against those of new user input values? The user will then be warned via pop-up messege that this entry already exists. The user will have the option to modify the existing Dimension (for the corresponding Part Number), or add a new Part Number and Dimension.

    Thank you again for your time. I would like to give you credit for the work that you have done on this spreadsheet. Could you please send me an email with your reference information.

    Hanan.

  8. #8
    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: Setting up a Lookup Table (2002)

    Glad I could help. To answer some of the questions:

    1. If you look thru the code in the sub InsertIntoLevel you should be able to follow along. The buttons each call the same routine, they each pass a separate parameter. Button1 passes "Level 1", Button2, "Level 2", and Button3 "Level 3". This parameter is what you search for in col A for the level.

    The code first determines the last row in col B that has data and adds 1 to it. This is the farthest the range should extend. It is one past the last row for level3 (in your dataset it is 26). This is used later..

    It defines a range (rSection) that starts at A14 and ends in col A at the last row of data in col A (in ex A14:A24)

    It looks thru this range of cells until it finds one that equals the "Level number" that was passed to it (1,2, or 3). This is held in the variable named sLevel.

    After it is found, it uses "end-down" to find the "next level" and subtracts 1 so this is the last row in that level (in ex: level 1 = 19, level 2 = 23, level 3 has no end so it goes all the way to the end and the lastrow is 65535. [This "problem' is fixed later]

    If the last row in b (19) is less than the lastrow, the lastrow is the lastrow in B [This fixes the issue with no level 4 to stop level 3.]

    It inserts in this last row, and selects that cell.

    2. Yes. I have added some code that looks at the row above and if it is blank, deletes that row and then selects the upper blank row. This should prevent pressing insert and adding rows without data.

    Change the lines:
    <pre> Cells(lLastRow, 2).Select
    Set rSection = Nothing</pre>



    To:
    <pre> Cells(lLastRow, 2).Select
    <font color=red> If Cells(lLastRow - 1, 2) = "" Then
    Rows(lLastRow - 1).Delete
    Cells(lLastRow - 1, 2).Select
    End If</font color=red>
    Set rSection = Nothing</pre>



    3. The section cbo has a listfillrange that is A4:A6. If you look in those cells you will see the values Level 1, Level 2 and level 3, respectively. I have them "hidden" by changing he Fontcolor to white.

    The Part# cbo list fill is determined and set by the code. When the section cbo is changed the part cbo is cleared (see the code for the cboSection_Change)

    When the part# gets the "focus", (see the code for cboPartNumber_Got Focus) The range is determined. It uses almost identical for determining the range that is used to find the row to insert. In this case it finds the Level selected by cboSection.value (the item selected in the section cbo). Like the other code it determines the last row. This also determines the first row. The range is the cols B and C from the first to the last row. The address of this range is entered as the listfillrange.

    I also changed the "BoundColumn" to 2 so that when you select the item in the list (col [img]/forums/images/smilies/cool.gif[/img], it sends the value from the 2nd col (Col C) to the LinkedCell (C9)

    4/5. Yes sorting can be done. And the list can be validated to check for duplicates

    I have the list validated when you make a change in Col B and I have the list sorted when you make a change to a cell in col C (at this point I assume, you have entered into B and then to C). I have code here that should be copied into the sheet1 codesheet (where all the buttons and object codes are)

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim x As Long

    If Not Intersect(Target, Range("B14:B65536")) Is Nothing Then
    Set rng = Intersect(Target.CurrentRegion, _
    Range("B14:B65536"))
    On Error Resume Next
    x = Application.WorksheetFunction.CountIf(rng, Target)
    On Error GoTo 0
    If x = 1 Then
    Target.Offset(0, 1).Select
    Else
    Application.EnableEvents = False
    MsgBox "'" & Target.Value & "' is already in the list" & _
    vbCrLf & "Please try something else"
    Target.ClearContents
    Application.EnableEvents = True
    Target.Select
    End If
    End If

    If Not Intersect(Target, Range("c14:C65536")) Is Nothing Then
    Set rng = Intersect(Target.CurrentRegion, _
    Range("A14:C65536"))
    Application.EnableEvents = False
    rng.Sort _
    Key1:=Range("A1"), Order1:=xlAscending, _
    Key2:=Range("B1"), Order2:=xlAscending, _
    Key3:=Range("C1"), Order2:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    Application.EnableEvents = True
    End If

    Set rng = Nothing
    End Sub</pre>


    If a change is made to B it checks the current region to see how many values it has (using countif). If it is 1 (the value that the user just entered) it selects col C, if not 1 it pops up a message and deletes the value.

    After changing in col C, the current region is sorted.

    If you want to credit or reference anyone, just reference Woody's Office Portal (www.wopr.com). I do not need or want any other personal reference.

    Steve

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Thank you Steve.

    I followed your instructions with regard to inserting the code for answer 4/5. however I did not get any messege showing up when I entered a duplicate part number. Do you know why?

    Hanan.

  10. #10
    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: Setting up a Lookup Table (2002)

    Where did you put the code?

    It needs to go in the worksheet object (Sheet1, where the GotFocus and Change code are).

    Steve

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Steve,

    I put the code in the right place. I did notice though that when I get a code error (I don't know why), and then close the spreadsheet without saving, the code does not seem to work. I attached an example. It seems to me that the code does not "refresh" itself when I startup the spreadsheet. Also, when this happens, the list does not get sorted and the part number combox does not display entries. Any ideas?

    Hanan.

  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: Setting up a Lookup Table (2002)

    If you get an error after the line of code which turns off event handling: (Application.EnableEvents = False) and before it turns it back on (Application.EnableEvents = true) and you don't manually enable it or restart, it won't be triggered again.

    you might need an error handling routine to ensure that the line is run, but first I would track down the error. What line gives you the error, what conditions cause the error.

    Before you give this to people to use, you should debug it and make sure that it will work under a variety of conditions. We at the lounge are providing code examples, but not full-blown applications. You must ensure that it meets your needs. That might also entail worksheet protection and other validation techniques as required...

    Steve

  13. #13
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Steve,

    The error messege that I get occurs at the following line:

    MsgBox "'" & Target.Value & "' is already in the list" & _
    vbCrLf & "Please try something else"

    I got this error by opening the spreadhseet that I attached in my previous email and deleting the last 3 lines in Level 1 section.

    Hanan.

  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: Setting up a Lookup Table (2002)

    The problem occurs since you are changing 3 cells at once, and the code was built assuming only 1 cell would change at a time. If they are going to change more than 1 cell at a time, the code would have to be modified to loop thru the target.

    One option (depending on what your goals are is to prevent working in COl B with more than 1 cell:
    Change
    <pre> If Not Intersect(Target, Range("B14:B65536")) Is Nothing Then
    Set rng = Intersect(Target.CurrentRegion, _
    Range("B14:B65536"))</pre>


    To:
    <pre> If Not Intersect(Target, Range("B14:B65536")) Is Nothing Then
    <font color=red>If Target.Cells.Count > 1 Then
    MsgBox "change only 1 cell at a time"
    With Application
    .EnableEvents = False
    .Undo
    .EnableEvents = True
    End With
    Exit Sub
    End If</font color=red>
    Set rng = Intersect(Target.CurrentRegion, _
    Range("B14:B65536"))</pre>


    Also even fixing that and they clear one at a time, you would now have too many blank rows in Level 1 and they should be deleted or the code will not work "properly".

    The code is written based on that line with the Level is blank in B and C and the row above it is completely blank. If this setup is not maintained, you may get "odd results".

    If you delete the 3 rows, you will not get the error message. You might want to create code to allow the user to delete/clear an entry (via a button or something) to avoid this issue.

    It could have code like:
    <pre>Selection.EntireRow.Delete</pre>


    [though you might want to confirm it with a message box first].

    If you want to control more and more aspects of excel, you might want to look into using a userform. You can maintain more control here and then just have the userform code put the data where it needs to go. Be aware excel does not relinquish control easily and it gets tougher and tougher to stop users from doing everything, so you have to adapt your code to monitor and watch and perhaps validate more things.

    As the "developer" you need to be aware of what the users might try and do, and be able to prevent those actions from causing coding problems. If the setup of the data will not be maintained as you have it listed in your example, you will have to adapt the code accordingly. The current example code will only work with this setup...

    Steve

  15. #15
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting up a Lookup Table (2002)

    Thank you very much Steve for all your help.

    Hanan.

Posting Permissions

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