Creating Interactive Spreadsheets with Activex Controls

[Lloydie]

I am trying to create a spreadsheet to calculate travel claims and want
users to enter data such as mileage travelled

I also want them to be able to select a tick box which is relevant to
there engine size and thus depending the box selected a different
calculation will be run on the mileage to calculate the tax.

e.g selecting box one would perform the calculation
{cell with mileage}*.1*7/47

while box two would perhaps be
{cell with mileage}*.12*7/47

Any advice much appreciated

Regards

Lloyd

 

[Bob Phillips]

Lloyd,

Here is one way. It assumes that the choices are columns B, choice 1,2,
and 3. To use it, use a formula such as

=cell_with_mileage*(IF(B2="a",.1,IF(C2="a",.12,IF(D2="a",.14,0)))*7/47


Code here


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Columns("B")) Is Nothing Then
With Target
Me.Cells(.Row, "B").Value = ""
Me.Cells(.Row, "C").Value = ""
Me.Cells(.Row, "D").Value = ""
.Value = "a"
.Font.Name = "Marlett"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)