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.
|