Userform updating

[Richard]

I have a userform with many controls on it (dropdown and
checkboxes).

I then have a total field on my first sheet which depends
on options ticked.
I have a text box which holds the running total, and is
updated by the following code attached to a command button:

Private Sub CommandButton10_Click()
Sheets("Summary").Select
Selection.AutoFilter Field:=3, Criteria1:="<>"
Sheets("Cost Analysis - Overall").Select
Range("A2").Select
Selection.AutoFilter Field:=2, Criteria1:="<>"
Range("Summary!d38").Copy
Sheets("summary").Select
Range("D50").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Sheets("summary").Calculate
TextBox45.Text = Sheets("summary").Range("D50").Value
Range("a1").Select
End Sub

Is there a way i can get the update to occur every time
any of the controls are clicked, without having to out
code against every control?

Thanks in advance

Richard

 

[Bob Phillips]

You will have to cut code but you can minimise it with

Private Sub UpdateTotals()
Sheets("Summary").AutoFilter Field:=3, Criteria1:="<>"
Sheets("Cost Analysis - Overall").Select
Range("A2").AutoFilter Field:=2, Criteria1:="<>"
Range("Summary!d38").Copy
Sheets("summary").Range("D50").Selection._
PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Sheets("summary").Calculate
TextBox45.Text = Sheets("summary").Range("D50").Value
End Sub

and then call this from each control, like

Private Sub CommandButton10_Click()
UpdateTotals
End Sub
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Richard]

Thanks Bob, i've nearly got it working except that i keep
getting

'Autofilter Method of Range class failed' as an error,
higlighting the first line.
any suggestions?

Thanks, Richard

 

[Richard]

Thanks Bob, i've nearly got it working except that i keep
getting

'Autofilter Method of Range class failed' as an error,
higlighting the first line.
any suggestions?

Thanks, Richard