EXCEL VBA - Dependent FORM Drop Downs & Excel Tables

[JILBO]

Hi All,

A bit of a follow on from some previous work which you guys helped me out massively! Thank You!

Within the attached workbook i've put a more detailed description of what im after (Requirements) so if anyone could just point me in right direction it would be greatly appreciated!

Briefly when i select a Part from a FORMs list, dependant on what is selected i'd like to present addtional information to the user which is then appended to a 'Call Out' Shape...Like i say the workbook explains it better


Thanks

[SamT]

I would use a table layout like







Applied Torque (Nm)


Item No
Part Number
Description
Qty
Thread-Grade
FlatWasher
Special Washer


0
JC00123456
SCREW HEX HD M5X15-8.8
33
M5-8.8
6.5
6.9



It is easier to code for and allows the addition of more special washers (Nylon insulating, Fiber, brass, etc,) without messing up existing code

For example: given a part number and a washer type.

Function GetTorque(PartNumber, WasherType)As Double
Select Case WasherType
Case Flat: ColOffset = 4
'
'
GetTorque = BOMvTorque.Range("B:B"). Find(PartNumber).Offset(, ColOffset)

More washers only means add Cases to Select Case

[JILBO]

Thanks for looking at this SamT, and yes it does make more
sense to organise the table like that! Going to play first thing in the morning ��

[JILBO]

Ok, a bit lost with this at the moment....need to do some reading on Case Arguments !

[JILBO]

Hoping p45cal could have a look at this!!

[SamT]

Full Select Case Statement with two arguments

Select Case WasherType
Case Is = "Flat": ColOffset = 4
Case Is = "Special": ColOffset = 5
Case Else: MsgBox "Oopsies"
End Select
The colon is a "Next Line" marker

Otherwise

Select Case WasherType
Case Is = "Flat"
ColOffset = 4
Case Is = "Special"
ColOffset = 5
Case Else
MsgBox "Oopsies"
End Select


Think of a Select Case basically as a Massive If...Then... ElseIf...Then...ElseIF...Then... ... Else

[JILBO]

Sorry SAM T - I'm just struggling with how to put together what you've suggested..

Ok What I'm after is when an item is selected in the ListBox that has a Torque requirement then present the user with input box for Qty of item and then with a Combobox (or similar) to select the washer type and then append that given value back into Callout string...if the ListBox item does not have a Torque requirement then just add Qty.


Private Sub AddCall_Click()

'Input box


'Test Delete!
Torque.Show False


Dim QtyAdd As String


QtyAdd = InputBox("Add Qty to be used within this Step")

If StrPtr(QtyAdd) = 0 Then
MsgBox ("User canceled!")
ElseIf QtyAdd = vbNullString Then
MsgBox ("User didn't enter Qty!")
Else



'Adds ShapeLine CallOut & Posistion


ActiveSheet.Shapes.AddShape(msoShapeLineCallout3, 800, 130, 400, 20).Select
Selection.ShapeRange.Fill.Visible = msoFalse
With Selection.ShapeRange.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
.EndArrowheadLength = msoArrowheadLengthMedium
.EndArrowheadWidth = msoArrowheadWidthMedium
.EndArrowheadStyle = msoArrowheadTriangle

'Makes the font black

End With


With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With


'Fill Text Box Yellow


With Selection.ShapeRange.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
.Solid
End With

'Adjusts the line to align with the Text box


Selection.ShapeRange.Adjustments.Item(1) = 0.20878
Selection.ShapeRange.Adjustments.Item(2) = 0


'Adds Selected Text from ListBox1 into TextBox Shape and then adds Qty into the Step_BOM Table
With Me.BOMList

Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "(" & .Column(0) & ") " & .Column(1) & ", " & .Column(2) & " x " & QtyAdd




End With


End If


End Sub










Private Sub UserForm_Initialize()


'Refreshes the BOMList to the updated WI_BOM
Me.BOMList.RowSource = Sheets("BOM").ListObjects("BOM")




Me.Top = Application.Top + (Application.UsableHeight / 2) - (Me.Height / 2)
Me.Left = Application.Left + (Application.UsableWidth / 2) - (Me.Width / 2)




End Sub

[SamT]

I can see why you are waiting for p45cal, he and I have to very different coding style. For one thing, I never use ActiveSheet, or Selection, or even Sheet Names in my code, except where absolutely needed. For another, I keep all code that operates on a Sheet in that Sheet's Code Module, and as little as possible in the Form's code.

Instead of Sheet Names, I use Sheet CodeNames, (AKA Object Names.) For example change the CodeName of Sheet4 to something similar to the Sheet Name: "TorqueBOM" is similar to "BoM v Torque".

In the Code Page for TorqueBOM, I would have a Public Property Get WasherList(PartNumber As String) As Variant (google Property Get,Let,Set) that returned an Array(List) of those Washer Types that the PartNumber can use, thus allowing 0 to many washer types on the same List for any given PartNumber. Don't forget to have a "None" column Torque value.

Then, where "Washers" is a List box, the code for the BOM_List ComboBox could be

BOM_List_Change()
Me.Washers.list = TorqueBOM.WasherList(Me.BOM_List.Value) 'Object dot Property(Input Parameter)
'
'
End SubI would also put the quantity required input on the main Form with the PartNumber and Washers.