macro event

  • Thread starter Thread starter ADE
  • Start date Start date
A

ADE

Hi

I am trying to solve a problem and i think i need to write some code i
VBA to build an event change macro.

I have built a drop down list that contains references to variou
financial instruments(stocks & currencies).

When i select an item from the list i want a macro to run so that th
appropriate cells in the spreadsheet are shown to either three or fou
decimal places depending upon the item selected from the drop dow
list.

Currencies to four decimal places and stocks shown with three decima
places.

Below are examples of items in my drop down list:-



GBP/USD
EUR/USD
JPY/USD
HSBC
VODAFONE
SHELL
USD/CHF

I guess that the code could be written so that it refers to the exac
cell reference of the different instruments within the drop down list.

Would it be possible to differentiate between a stock and a currenc
because one contains a / (slash) GBP/USD for example.


Thanks for your help

Ad
 
Is the drop down list a DATA VALIDATION list?

What you are trying to accomplish should not be that hard at all. Yo
need to add some code to the Workbook_SheetChange Event and use an I
statement to determine what value was selected from your list.
Depending on the value selected from your list of choices, you ca
execute the remainder of your code which would format the cells exactl
how you would like. Try something like this where cell B1 contains th
drop down list of values:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)

If InStr(1, Range("B1").Value, "/") > 0 Then
Range("C2").NumberFormat = "0.0000"
Else
Range("C2").NumberFormat = "0.000"
End If


End Sub




Rolli
 
Back
Top