CODEWITHSUNDEEP
excelvba
user9078057
user9078057

Reputation: 269

How to incorporate this drop-down list into my program code VBA

I have the following drop-down/combo box list that has two options, namely Internal and External. My drop-down list looks like this: enter image description here

And then I have a macro that runs through the following button: enter image description here

My question does not revolve around the actual content of the code but rather the structure of the code to include the drop-down list, so I have simplified it a lot for the sake of getting to the point. My (simplified) code initially made a rudimentary calculation.

Sub InsertEquitiesBonds(Dim x as Double, Dim y as Double)
Dim ws as Worksheet
Set ws = Worksheets("PnL")
ws.Range("C4").Value = x + y
End Sub

I would like to create a decision logic in this code after clicking populate, so like:

If DropDown6_Change.Value = "Internal" Then
ws.Range("C4").Value = x + y 
Else 
ws.Range("C4").Value = x - y
End If

What do I need to use for the above code to be realized?

Upvotes: 0

Views: 79

Answers (1)

Davesexcel
Davesexcel

Reputation: 6982

Almost there.

Sub DropDown1_Change()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    x = 10
    y = 5
    With ThisWorkbook.Sheets("Sheet1").Shapes("Drop Down 1").ControlFormat
        Select Case .List(.Value)
        Case "Internal": ws.Range("C4").Value = x + y
        Case "External": ws.Range("C4").Value = x - y
        End Select
    End With
End Sub

Upvotes: 1

Related Questions