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
3SymbolPriceVWAPBidAskChange% ChangeVolumeAlertsConditionValue
4
5HPQ:US#N/A#N/A#N/A#N/A#N/A#N/A#N/APrice>= 25.00play sound
6GE:US#N/A#N/A#N/A#N/A#N/A#N/A#N/AVolume>= 1,000,000.00play sound
7DE:US#N/A#N/A#N/A#N/A#N/A#N/A#N/ABid Price<= 34.00play sound
8HD:US#N/A#N/A#N/A#N/A#N/A#N/A#N/AAsk Price>= 34.50play sound

<tbody>
</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>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
2SymbolPriceVWAPBidAskChange% ChangeVolumeAlertsConditionValueTo be hiddenSound*
3CCTC0.1865500.1923070.1870000.1950000.01257.04%6,855Price>= * * * * * * *0.1900falsesound file*

<tbody>
</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
2SymbolPriceVWAPBidAskChange% ChangeVolumeAlertsConditionsValueTo be hiddenSound*
3CCTC#N/A#N/A#N/A#N/A#N/A#N/A#N/A***#REF!sound file*

<tbody>
</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

E
  • Solved
SUMPRODUCT assistance
Replies
8
Views
569
excel_caveman
E
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
163
Tetra201
Tetra201
S
  • Solved
Line Chart Issue
Replies
3
Views
319
Fluff
Fluff
C
  • Solved
Formula for calculating Stock Option rolls
Replies
4
Views
663
Toadstool
Toadstool
N
  • Solved
Opening of excel file takes to much time
Replies
6
Views
193
Fluff
Fluff

Forum statistics

Threads
1,214,630
Messages
6,120,634
Members
448,973
Latest member
ChristineC

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