hi again,
yes you can do that too.
Add a text box and name it TB1. this will hold the number
of rows down form row 11 that you want the calculation to
occur. sorry cant use row number.
in CB1, replace the code i gave you yesterday and replace
it with this.
Private Sub CB1_Change()
If CB1 = "None" Then
Range("I10").Offset(TB1, 0).Value = 0
CB2 = 0
Else
If CB1 = "Standard" Then
Range("I10").Offset(TB1, 0).Value _
= Range("G10").Offset(TB1, 0) / 1.175
CB2 = 17.5
Else
If CB1 = "Custom" Then
Range("I10").Offset(TB1, 0).Value _
= Range("G10").Offset(TB1, 0) / CB2
End If
End If
End If
End Sub
-----Original Message-----
much appreciated you are an anoymous genius one final
thing can i get CB2 to
show value 0 when CB1 is "None" and Show value 17.5 when
CB1 is "Standard"
Also how can i repeat this all the way to row 39
" wrote:
hi again
it is possible.
from the toolbox, put a combo box at e10'
right click the combo box, click properties
rename it CB1.
Set ListFillRange to IS1:IS3
in cell IS1 put None
in cell IS2 put standart
in cell IS3 put Custom
You can put the fill range anywhere you want, this is
just
how i set it up. just make sure that where ever to put
none, standard, and custom matches your fill range in
the
combo box.
kill the property box.
right click the combo box,click view code
paste this code in it
Private Sub CB1_Change()
If CB1 = "None" Then
Range("I10").Value = 0
Else
If CB1 = "Standard" Then
Range("I10").Value = Range("G10") / 1.175
Else
If CB1 = "Custom" Then
Range("I10").Value = Range("G10") / CB2
End If
End If
End If
End Sub
from the toolbox, put a combo box at F10'
right click the combo box, click properties
rename it CB2.
Set ListFillRange to IT1:IT20
Set ListRows to 20
in cell IT1 put 1
in cell IT2 put 2
in cell IT3 put 3
ect on down.
Unclick design mode.
you are ready to rock and roll
it works on my machine.
-----Original Message-----
2nd combo should be in F10 i want f10 to react to
selection in E10 with
either 0, 17.5 or combo 1-20
If this is not possible how would you suggest i set up
the sheet
i need to be able to enter the gross amount, select
whether taxable and if
tax is not standard rate i need to be able to select
rate
so that sheet can
show me net totals for any expenditure.
" wrote:
cant put 1st combo AND 2nd combo in e10.
-----Original Message-----
I will create a tax rate column in F10 therefore if
user
selects none in E10
value should be 0, standard in E10 value should be
17.5,
custom in E10 value
should be drop down menu listing values 1 to 20
Any ideas
" wrote:
hi,
where is the list the user would choose from
going
to
be.
and how will it get selected? i would suggest
another
combo box but where to put it?
-----Original Message-----
Im trying to create an exspenses account sheet
that
self
calculates tax.
Cells are E10 TAX y/n , G10 Gross, I10 total tax
I need to-
Create a drop down menu for cell E10 offering
three
options,
None,
Standard 17.5%,
Custom.
If custom is selected the user must be able to
select
the
tax rate from a
list between 1% and 20%
Cell i10 must be 0 if E10 is none, must be G10
divided
by
1.175 if E10 is
standard and finally G10 divided by 1.user
defined
value
if custom is
selected ( eg if user selected 5%would be
G10/1.05 ).
Please somebody help me
.
.
.
.
|