ExcelBanter - Userform updating

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Userform updating (https://www.excelbanter.com/excel-programming/299060-userform-updating.html)

Userform updating

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

Userform updating

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" wrote in message
...
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

Userform updating

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

-----Original Message-----
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" wrote in
message
...
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

.

Userform updating