Standard notes wizard

[keelaboosa]

I would like to set up a Standard Notes wizard for our drafters.

For my example, we make three different types of drawings: Assembly, Welding, and Sheet Metal. I would set up a table with notes in the rows and drawing types in the headers.

NOTE	ASSY	WELD	SHTMTL
INTERPRET DRAWING PER ASME Y14.5	Y	Y	Y
WELD PER ASW D1.1	N	Y	N
BAG AND TAG COMPONENTS	N	Y	Y
ASSEMBLE PER INSTRUCTION XXXX	Y	N	N

<tbody>
</tbody>

I would then have a cell with data validation to select the drawing type. From the value in that cell, I want a table with the appropriate note for the drawing type.

How would I go about this one?

 

[Yongle]

Re: Standard notes wizard help

I think this is better suited to VBA anyway.

Here is code - deliberately wordy to make it easy to follow (away from PC for a few days)

Code is triggered when value in Cell A1 changes

Code goes in sheet module of Results sheet (right-click on sheet tab \ View Code \ paste in window on right)

Private Sub Worksheet_Change(ByVal Target As Range)
    Const shName = "[COLOR=#ff0000]Hidden"[/COLOR]                                 '[COLOR=#ff0000]AMEND SHEET NAME[/COLOR]
    Dim ws As Worksheet
    Dim myTable As Range                                    'lookup range
    Dim ColumnNo As Long, RowNo As Long                     'in table of values
    Dim myCount As Long                                     'from 3 to 6 ie cells A3:A6

If Target.Address(0, 0) = "A1" Then
    On Error GoTo EventEnable
    Application.EnableEvents = False
    Me.Range("A3:A6").ClearContents                         'clear old values
    Set ws = Sheets(shName)
    Set myTable = ws.Range("A1").CurrentRegion              'contiguous range starts at cell A1
    With WorksheetFunction
        ColumnNo = .Match(Target, myTable.Rows(1), 0)       'look for match for A1 value
    End With
    myCount = 2
    For RowNo = 2 To myTable.Rows.Count
        If myTable(RowNo, ColumnNo) = "Y" Then
            myCount = myCount + 1                           'increases by 1 when "Y" is found
            Me.Cells(myCount, "A") = myTable(RowNo, "A")    'place value for next "Y" in A3:A6
        End If
    Next RowNo
    
End If
EventEnable:
Application.EnableEvents = True
End Sub

Sub ee()
'(attached to a shortcut) this can be very helpful if Events are inadvertently disabled
Application.EnableEvents = True
End Sub

Sheet "Hidden" looks like this - cells A1:D5

NOTE	ASSY	WELD	SHTMTL
INTERPRET DRAWING PER ASME Y14.5	Y	Y	Y
WELD PER ASW D1.1	N	Y	N
BAG AND TAG COMPONENTS	N	Y	Y
ASSEMBLE PER INSTRUCTION XXXX	Y	N	N

<tbody>
</tbody>

Results sheet columnA

Weld

INTERPRET DRAWING PER ASME Y14.5

WELD PER ASW D1.1

BAG AND TAG COMPONENTS

<tbody>
</tbody>