View Single Post
  #2   Report Post  
Jim Cone
 
Posts: n/a
Default

Lisa,

The following uses a Combobox from the forms toolbar.
It assumes that Combobox has been assigned a single
column list range where the formulas are shown.
The first item in that list should be a title, such as "Formulas",
while the rest of the list should be the actual formulas without
the leading equal sign "=".
The linked cell for the combobox must also have been assigned.

'--------------------------------
Sub FormulaToActiveCell()
'Places selection from combobox in the active cell.
Dim rngFormulas As Excel.Range

'The combobox is named 'Drop Down 1"
With ActiveSheet.Shapes("Drop Down 1").ControlFormat
Set rngFormulas = ActiveSheet.Range(.ListFillRange)
'So you won't overwrite your own formulas.
If Application.Intersect(rngFormulas, ActiveCell) Is Nothing Then
ActiveCell.Formula = "=" & rngFormulas(Range(.LinkedCell).Text)
End If
'Reset display value in the combobox
Range(.LinkedCell).Value = 1
End With

Set rngFormulas = Nothing
End Sub
'---------------------------------

Regards,
Jim Cone
San Francisco, USA


"Lisa H via OfficeKB.com"
wrote in message
...
I was wondering if it is possible to have a list of formulas in a drop down
box so that when I pick a formula, that is the one that is applied to that
cell. Any help would be great.