If/Then Combo Boxes

M

Mackey00

New Member
Joined
Jan 10, 2016
Messages
22
Hi,

I can't seem to find a previous post close enough to what I'm trying to accomplish. Also, I don't know if this is possible with Excel formulas or if VBA must be used as well. Below I have an API that shows stock prices, volume, etc.

Columns J and K have Combo Boxes and column N has Check Boxes.

Available choices for J are (Price, Volume, Bid Price, Ask Price)
Available choices for K are (>= or <=)

I need to select the type of alert in column J then greater or less than in K and finally the numerical value in column L. Once an alert is triggered a sound is played (name and destination of wav file in column M). Finally, column N are check boxes with will activate or deactivate the alerts.

This one is very difficult for me b/c I think some VBA is going to need to be used and I'm just starting to learn that. Any help or direction would be greatly appreciated.


BCDEFGHIJKLMN
HPQ:US#N/A#N/A#N/A#N/A#N/A#N/A#N/APrice>=play sound
GE:US#N/A#N/A#N/A#N/A#N/A#N/A#N/AVolume>=play sound
DE:US#N/A#N/A#N/A#N/A#N/A#N/A#N/ABid Price<=play sound
HD:US#N/A#N/A#N/A#N/A#N/A#N/A#N/AAsk Price>=play sound

<tbody>
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="bgcolor: #CCFFCC"]Symbol[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Price[/TD]
[TD="bgcolor: #CCFFCC, align: center"]VWAP[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Bid[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Ask[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Change[/TD]
[TD="bgcolor: #CCFFCC, align: center"]% Change[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Volume[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Alerts[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Condition[/TD]
[TD="bgcolor: #CCFFCC, align: center"]Value[/TD]
[TD="bgcolor: #CCFFCC"][/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"] 25.00[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"] 1,000,000.00[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"] 34.00[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"] 34.50[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C5=RTD("quotestream.rtdserver",,B5,"LAST")
D5=RTD("quotestream.rtdserver",,B5,"VWAP")
E5=RTD("quotestream.rtdserver",,C5,"BID")
F5=RTD("quotestream.rtdserver",,D5,"ASK")
G5=RTD("quotestream.rtdserver",,B5,"Change")
H5=RTD("quotestream.rtdserver",,B5,"Percent Change")/100
I5=RTD("quotestream.rtdserver",,C5,"Volume")/100
C6=RTD("quotestream.rtdserver",,B6,"LAST")
D6=RTD("quotestream.rtdserver",,B6,"VWAP")
E6=RTD("quotestream.rtdserver",,C6,"BID")
F6=RTD("quotestream.rtdserver",,D6,"ASK")
G6=RTD("quotestream.rtdserver",,B6,"Change")
H6=RTD("quotestream.rtdserver",,B6,"Percent Change")/100
I6=RTD("quotestream.rtdserver",,C6,"Volume")/100
C7=RTD("quotestream.rtdserver",,B7,"LAST")
D7=RTD("quotestream.rtdserver",,B7,"VWAP")
E7=RTD("quotestream.rtdserver",,C7,"BID")
F7=RTD("quotestream.rtdserver",,D7,"ASK")
G7=RTD("quotestream.rtdserver",,B7,"Change")
H7=RTD("quotestream.rtdserver",,B7,"Percent Change")/100
I7=RTD("quotestream.rtdserver",,C7,"Volume")/100
C8=RTD("quotestream.rtdserver",,B8,"LAST")
D8=RTD("quotestream.rtdserver",,B8,"VWAP")
E8=RTD("quotestream.rtdserver",,C8,"BID")
F8=RTD("quotestream.rtdserver",,D8,"ASK")
G8=RTD("quotestream.rtdserver",,B8,"Change")
H8=RTD("quotestream.rtdserver",,B8,"Percent Change")/100
I8=RTD("quotestream.rtdserver",,C8,"Volume")/100

<tbody>
</tbody>

<tbody>
</tbody>
 
Sort by date Sort by votes
I have been able to get the combo boxes options working correctly. Now I can't seem to get audio alerts to play if an alert is triggered. Also, I have check boxes which I would like to use to turn the alerts on or off.

I have four alerts in I3 (price, volume, bid price, ask price) and two in J3 (>=, <=). When an alert is triggered L3 will state "True" or "False". If alerts are triggered in column L I would like to play wav or midi files located in the C:\windows\media folder. I might set different sounds per stock or per alert.

Finally, In column N i have check boxes which aren't showing here, but they would turn the alert(s) on/off.

Any guidance or direction would be greatly appreciated.

Sheet1

*ABCDEFGHIJKLMN
CCTCPrice>=falsesound file*

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ccffcc"]Symbol[/TD]
[TD="bgcolor: #ccffcc, align: center"]Price[/TD]
[TD="bgcolor: #ccffcc, align: center"]VWAP[/TD]
[TD="bgcolor: #ccffcc, align: center"]Bid[/TD]
[TD="bgcolor: #ccffcc, align: center"]Ask[/TD]
[TD="bgcolor: #ccffcc, align: center"]Change[/TD]
[TD="bgcolor: #ccffcc, align: center"]% Change[/TD]
[TD="bgcolor: #ccffcc, align: center"]Volume[/TD]
[TD="bgcolor: #ccffcc, align: center"]Alerts[/TD]
[TD="bgcolor: #ccffcc, align: center"]Condition[/TD]
[TD="bgcolor: #ccffcc, align: center"]Value[/TD]
[TD="bgcolor: #ccffcc, align: center"]To be hidden[/TD]
[TD="bgcolor: #ccffcc, align: center"]Sound[/TD]
[TD="bgcolor: #ccffcc"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]0.186550[/TD]
[TD="align: right"]0.192307[/TD]
[TD="align: right"]0.187000[/TD]
[TD="align: right"]0.195000[/TD]
[TD="align: right"]0.0125[/TD]
[TD="align: right"]7.04%[/TD]
[TD="align: right"]6,855[/TD]

[TD="align: right"] * * * * * * *0.1900[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B3=RTD("quotestream.rtdserver",,A3,"LAST")
C3=RTD("quotestream.rtdserver",,A3,"VWAP")
D3=RTD("quotestream.rtdserver",,A3,"BID")
E3=RTD("quotestream.rtdserver",,A3,"ASK")
F3=RTD("quotestream.rtdserver",,A3,"Change")
G3=RTD("quotestream.rtdserver",,A3,"Percent Change")/100
H3=RTD("quotestream.rtdserver",,A3,"Volume")/100
L3=IF(AND(I3="Price",XOR(AND(J3=">=",B3>=K3),AND(J3="<=",B3<=K3))),"true",IF(AND(I3="volume",XOR(AND(J3=">=",H3>=K3),AND(J3="<=",H3<=K3))),"true",IF(AND(I3="bid price",XOR(AND(J3=">=",D3>=K3),AND(J3="<=",D3<=K3))),"true",IF(AND(I3="ask price",XOR(AND(J3=">=",E3>=K3),AND(J3="<=",E3<=K3))),"true","false"))))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
I have attached a macro below the spreadsheet formula that I found online that I feel with work with the spreadsheet with a few tweaks. I'm just starting to learn VBA so it is still very foreign. I would like the macro to play "tada.wav located in windows/media folder if any cells L3:L24 text is "Alert". Each time any of the cells update to "Alert" the wav file should play. Thank you in advance for any comments.

Sheet1

*ABCDEFGHIJKLMN
CCTC#N/A#N/A#N/A#N/A#N/A#N/A#N/A***#REF!sound file*

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ccffcc"]Symbol[/TD]
[TD="bgcolor: #ccffcc, align: center"]Price[/TD]
[TD="bgcolor: #ccffcc, align: center"]VWAP[/TD]
[TD="bgcolor: #ccffcc, align: center"]Bid[/TD]
[TD="bgcolor: #ccffcc, align: center"]Ask[/TD]
[TD="bgcolor: #ccffcc, align: center"]Change[/TD]
[TD="bgcolor: #ccffcc, align: center"]% Change[/TD]
[TD="bgcolor: #ccffcc, align: center"]Volume[/TD]
[TD="bgcolor: #ccffcc, align: center"]Alerts[/TD]
[TD="bgcolor: #ccffcc, align: center"]Conditions[/TD]
[TD="bgcolor: #ccffcc, align: center"]Value[/TD]
[TD="bgcolor: #ccffcc, align: center"]To be hidden[/TD]
[TD="bgcolor: #ccffcc, align: center"]Sound[/TD]
[TD="bgcolor: #ccffcc"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B3=RTD("quotestream.rtdserver",,A3,"LAST")
C3=RTD("quotestream.rtdserver",,A3,"VWAP")
D3=RTD("quotestream.rtdserver",,A3,"BID")
E3=RTD("quotestream.rtdserver",,A3,"ASK")
F3=RTD("quotestream.rtdserver",,A3,"Change")
G3=RTD("quotestream.rtdserver",,A3,"Percent Change")/100
H3=RTD("quotestream.rtdserver",,A3,"Volume")/100
L3=IF(AND(I3="Price",XOR(AND(#REF!=">=",B3>=K3),AND(#REF!="<=",B3<=K3))),"Alert",IF(AND(I3="volume",XOR(AND(#REF!=">=",H3>=K3),AND(#REF!="<=",H3<=K3))),"Alert",IF(AND(I3="bid price",XOR(AND(#REF!=">=",D3>=K3),AND(#REF!="<=",D3<=K3))),"Alert",IF(AND(I3="ask price",XOR(AND(#REF!=">=",E3>=K3),AND(#REF!="<=",E3<=K3))),"Alert",""))))

<tbody>
</tbody>

<tbody>
</tbody>
VBA MACRO

Code: 
Private Declare Function PlaySound Lib "winmm.dll" _
  Alias "PlaySoundA" (ByVal lpszName As String, _
  ByVal hModule As Long, ByVal dwFlags As Long) As Long


    Const SND_SYNC = &H0
    Const SND_ASYNC = &H1
    Const SND_FILENAME = &H20000
    
Sub PlayWAV()
    WAVFile = "C:\windows\media\tada.wav"
    Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("L3:L24").Text = "Alert" Then PlayWAV
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA to generate report
2
Replies
15
Views
297
dsubash
D
T
  • Question Question
How to sum with multiple criteria in 2 different columns? - census data
Replies
1
Views
230
hagia_sofia
hagia_sofia
E
  • Solved
SUMPRODUCT assistance
Replies
8
Views
811
excel_caveman
E
S
  • Solved
Line Chart Issue
Replies
3
Views
477
Fluff
Fluff
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
393
Tetra201
Tetra201

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top