none
userform to show the content of a cabinet with drawers RRS feed

  • Question

  • I have a floor plan I made on a spreadsheet by reducing the grids down to 5X5 squares. I laid out cabinets with the idea to click on the cabinet which will bring up a userform. In this userform I want the user to have the view to select a drawer and in a listbox in the same userform to display all the items from the spreadsheet database (a separate tab).

    I can not wrap my head around this. I have made other things from the floor plan that are simple one dimensional items. I create the item on the floor plan, then create a module for that item. this module passes the information to the userform which uses this to retrieve the data to display. Now I have the problem of two dimensional object. first is the cabinet then the drawers to get at the data needed to display in the listbox.

    I can not decide on what to use to display the choice of display for the drawers. It must first know how many drawers to display then show this, then give the user the ability to choose a drawer with a click(all from one box), then in a listbox to the right display the information.

    Can someone give me some ideas on the best box to use on this.


    Jim Neely

    Thursday, June 28, 2012 8:25 PM

Answers

  • My problem is to present a box in the top left hand cornor with what looks like the face of a cabinet with drawers. The drawers would be labled as 1 - 10 but I what it to reflect the exact number of drawers for that cabinet out of many cabinets. This box is where the user would pick a drawer

    What sort of control is this Cabinet? Does it have Event code attached to it when it is selected or how is the code activated?

    I suggest that you create a Userform that can be opened when you select the cabinet. The Userform should contain either Option Buttons or CheckBoxes. Each OptionButton/CheckBox to represent a drawer so you will need 10 of them. With Option buttons the user is limited to one selection. With CheckBoxes the User can select one or many.

    This Userform should have an Inititalize Event to position it relative to the Cabinet control and to make visible the required number of Option Buttons/CheckBoxes. On selection of the cabinet you should have a variable with the number of draws to display. I suggest that firstly you set all the controls Visible property to False and then use a loop to set the required controls to visible.

    If I understand correctly the floor paln is on a worksheet (Not a userform). Basically to set the form postion relative to the Cabinet control you get the top of the the Cabinet control to set the forms' top position and then get the left of the cabinet control + width of the cabinet control to position the form just to the right of the cabinet control.)

    You will also need a command button on the userform with OK or similar for after the selection is made to continue processing.

    If you intend using this Userform for more than one cabinet and the different cabinets have different number of drawers then you can set a Public variable to identify which cabinet control called the drawer userform. (Declare a Public variable at the top of a standard module and then it is available thoughout the workbook.)

    I am hoping that you can achieve most of this yourself with maybe a few more questions on the procedure for individual components of it.

     



    Regards, OssieMac

    • Marked as answer by STARFALLS Monday, July 2, 2012 10:28 AM
    Monday, July 2, 2012 3:08 AM

All replies

  • How are you storing the data (cabinets - drawers - contents of drawers)?

    Regards, Hans Vogelaar

    Thursday, June 28, 2012 8:40 PM
  • Hi Hans

    Here is the module

    Sub B700_Assembly_Station_T1_Click()
    Dim LabelA As String, labelB As String, labelC As String, labelD As String, labelE As String, labelF As String, labelG As Integer
         
        LabelA = "B700 Assembly - Table 1"
        labelB = "B700"
        labelC = "Assembly"
        labelE = "S"
        labelF = "T1"
        labelG = 3
         ' MUST load the form first!
        Load MR0009T1
         ' Send the variables over to the form
        Call MR0009T1.FillVars(LabelA, labelB, labelC, labelD, labelE, labelF, labelG)
        Call MR0009T1.s
         ' Now show the form
        MR0009T1.Show
    End Sub

    Here is the code i use



    'Author       : Jim Neely
    'Macro Purpose: To populate a listbox with data from
    '               a worksheet range

    ' Dim variables up here so that they are available to the entire form
    Dim str1 As String, str2 As String, str3 As String, str4 As String, str5 As String, str6 As String, str7 As Integer
    Dim lbtarget As MSForms.ListBox
    Dim lb2Cap As String
    Dim lb3Cap As String
    Dim BgnMo As String
    Dim EndMo As String
    Dim wsht As String
    Dim Res As Variant
    Dim rngSource As Range
    Sub FillVars(ByRef s1 As String, s2 As String, s3 As String, s4 As String, s5 As String, s6 As String, s7 As Integer)
    ' This is the sub that collects the variables from the calling module.
    ' Any form initialization that relies on external variables should be done here.
    Label1.Caption = s1
    ' s1 and s1 are not visible to other Subs in the form,
    str1 = s1 'Label1 Caption
    str2 = s2 'Line
    str3 = s3 'Dept
    str4 = s4 'Machine
    str5 = s5 'Line/Station
    str6 = s6 'Table
    str7 = s7 'Case lookup
    End Sub
    Sub s()
    'Label Caption setup
    Select Case str2
        Case "M200"
            'put M200 in worksheet
            Worksheets("Calibration Schedule").Range("P17").Value = 2 'vlookup on calibration sheet for the Line
            Worksheets("Calibration Schedule").Range("P18").Value = str3 'vlookup on calibration sheet for the Dept
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value 'Gets first Mo in the Year for calibration
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value 'Gets second Mo in the Year for calibration
        Case "Maintenance"
            'Worksheets("Calibration Schedule").Range("P17").Value = 2
            'Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = "      Aug "
            'EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "Tool Room"
            'Worksheets("Calibration Schedule").Range("P17").Value = 2
            'Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = "      Aug "
            'EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "QA"
            Worksheets("Calibration Schedule").Range("P17").Value = 2
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = "      Feb "
            'EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "QAI"
            Worksheets("Calibration Schedule").Range("P17").Value = 2
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = "      Feb "
            'EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "Press"
            Worksheets("Calibration Schedule").Range("P17").Value = 2
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = "       Jan "
            'EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "B800"
            Worksheets("Calibration Schedule").Range("P17").Value = 3
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "B700"
            Worksheets("Calibration Schedule").Range("P17").Value = 4
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "481K"
            Worksheets("Calibration Schedule").Range("P17").Value = 5
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "423K"
            Worksheets("Calibration Schedule").Range("P17").Value = 6
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case "143K"
            Worksheets("Calibration Schedule").Range("P17").Value = 7
            Worksheets("Calibration Schedule").Range("P18").Value = str3
            BgnMo = Worksheets("Calibration Schedule").Range("Q19").Value
            EndMo = Worksheets("Calibration Schedule").Range("Q20").Value
        Case Else
            'Sheets("Gages").Select
    End Select
    'Start of UserForm
    Application.ScreenUpdating = False
    'Clean Worksheet from prior Run
    Worksheets("ListBoxData").Range("A2:W500").Clear
    'Open Gages Database
    Worksheets("Gages").Select
    Application.ScreenUpdating = False
    Range("A1").Select
    Select Case str7
        Case 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1 'counts non-blank cells
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
        Case 2
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=9, Criteria1:=str2 'Line
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 3
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=9, Criteria1:=str2 'Line
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 4
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 5
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=9, Criteria1:=str2 'Line
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
        Case 6
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 7
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=9, Criterial:=srt2 'Line
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 8 'Special Run for B800/B700 Valve Body
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=9, Criteria1:="=B700" _
            , Operator:=xlOr, Criteria2:="=B800"                                         'Line
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=11, Criteria1:=str4 'Machine
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
        Case 9
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=4, Criteria1:="Active" 'Status
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=10, Criteria1:=str3 'Dept
            lb3Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=12, Criteria1:=str5 'Line/Station
            ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=13, Criteria1:=str6 'Table
    End Select
    'Sort the data ascending by Part Number
        ActiveWorkbook.Worksheets("Gages").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Gages").AutoFilter.Sort.SortFields.Add Key:=Range( _
            "A2:A9508"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Gages").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    '////////////////
    'Begin UserForm Info
    'top right hand corner
    Res = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
    lb2Cap = Application.WorksheetFunction.Subtotal(3, Range("$B:$B")) - 1
    If str2 = "B800" And str3 = "VB" Then
    Label3.Caption = Res & " of " & lb3Cap & " B800/B700 Gages"
    Else
    Label3.Caption = "      " & Res & " of " & lb3Cap & " Gages"
    End If
    If str3 = "Assembly" Then
    Label2.Caption = "Calibration Schedule" & vbCrLf & "       " & BgnMo & "   " & EndMo & "" & vbCrLf & " Torque Wrench are" & vbCrLf & "    on 4Mo Schedule" & vbCrLf & "    APR  AUG   DEC"
    Else
    Label2.Caption = "Calibration Schedule" & vbCrLf & "       " & BgnMo & "   " & EndMo
    'End of UserForm Info
    '/////////////////
    End If
    'Following line copies all visible data including the column headers
    'to another worksheet.
    Worksheets("Gages").AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Worksheets("ListBoxData").Range("A1")
    Selection.AutoFilter
    'Following assigns the data only (excluding the column headers) to rngSource
    With Worksheets("ListBoxData")
    'Set rngSource = .Range(.Cells(2, "A"), .Cells(.Rows.count, "W").End(xlUp))
    Set rngSource = .Range("A2:AA1450")
    End With
    ListBox1.Clear
    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 27
        'Set column widths
        .ColumnWidths = "100;65;125;0;0;35;30;30;0;0;0;0;0;0;0;0;40;40;40;40;0;0;0;0;0;20;20"
        'Use the above line for Development and the below line for Operations
        '.ColumnWidths = "100;65;125;30;30;30;40;40;40;0;0;0;0;0;0;100;20;0;0"
        'Insert the range of data supplied
        .ColumnHeads = True
        '.List = rngSource.Cells.Value
        .RowSource = rngSource.Address(External:=True)
    End With
    Worksheets("Gages").Select
    Range("A1").Select
    Sheets("Floor Plan").Select
    Application.ScreenUpdating = True
    End Sub

    here is the spreadsheet

    Part Number Serial Number Description Status Type Range Lower Upper Line Dept Machine Line/Station Table/Rack/Stand/Cart
    100P-35141-577ML-KK 100533TA Transmission Case Active 35141       M200 Case   S T6


    Jim Neely

    Friday, June 29, 2012 5:20 PM
  • Well again my mentor. I now know how t close a question. Something again I "thought I knew" LOL.

    Yes thats the one. My problem is to present a box in the top left hand cornor with what looks like the face of a cabinet with drawers. The drawers would be labled as 1 - 10 but I what it to reflect the exact number of drawers for that cabinet out of many cabinets. This box is where the user would pick a drawer (by clicking on the drawer). this would start code that would go out and pull information from 2 separate sheets. The sheets would be displayed in 2 separate boxes one above the other on the right side. The 2 box have different records to displayed, that is why I need 2 boxes.

    The code I posted is my whole code. Of course there are several modules but only one userform. So I must assume I will need a seperate Userform to do this. My problem is what kind of box to use for the user to select a drawer from. I am posting this over on my question where it should be.

    Jim Neely

    Sunday, July 1, 2012 7:26 AM
  • My problem is to present a box in the top left hand cornor with what looks like the face of a cabinet with drawers. The drawers would be labled as 1 - 10 but I what it to reflect the exact number of drawers for that cabinet out of many cabinets. This box is where the user would pick a drawer

    What sort of control is this Cabinet? Does it have Event code attached to it when it is selected or how is the code activated?

    I suggest that you create a Userform that can be opened when you select the cabinet. The Userform should contain either Option Buttons or CheckBoxes. Each OptionButton/CheckBox to represent a drawer so you will need 10 of them. With Option buttons the user is limited to one selection. With CheckBoxes the User can select one or many.

    This Userform should have an Inititalize Event to position it relative to the Cabinet control and to make visible the required number of Option Buttons/CheckBoxes. On selection of the cabinet you should have a variable with the number of draws to display. I suggest that firstly you set all the controls Visible property to False and then use a loop to set the required controls to visible.

    If I understand correctly the floor paln is on a worksheet (Not a userform). Basically to set the form postion relative to the Cabinet control you get the top of the the Cabinet control to set the forms' top position and then get the left of the cabinet control + width of the cabinet control to position the form just to the right of the cabinet control.)

    You will also need a command button on the userform with OK or similar for after the selection is made to continue processing.

    If you intend using this Userform for more than one cabinet and the different cabinets have different number of drawers then you can set a Public variable to identify which cabinet control called the drawer userform. (Declare a Public variable at the top of a standard module and then it is available thoughout the workbook.)

    I am hoping that you can achieve most of this yourself with maybe a few more questions on the procedure for individual components of it.

     



    Regards, OssieMac

    • Marked as answer by STARFALLS Monday, July 2, 2012 10:28 AM
    Monday, July 2, 2012 3:08 AM
  • OssieMac

    Again many thanks !!!!

    I have made a working code. I created a UserForm with a Frame and in that Frame I added a CommandButton for each drawer, I as well made 2 listboxes. Each CommandButton represents a drawer and holds code particular to that drawer. I then created a textbox on the floor plan (floor plan is on the Excel sheet). I assigned the textbox to the macro that calls that UserForm.

    I have in one listbox, data recordset for one item and a second listbox for another recordset on a seperate sheet. Again You Help has been wounderful!


    Jim Neely

    Monday, July 2, 2012 10:28 AM