Auto update Cell : =%F19%
- Thread starter ianb
- Start date
ianb
Member
This is one Idea I had yet it invles a click button.
I was interested in an update cell and also an auto update pivot table.
Range("B21").Select
ActiveCell.FormulaR1C1 = "=""%""&R[-2]C[4]&""%"""
Range("D26").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("D26").Select
I was interested in an update cell and also an auto update pivot table.
Range("B21").Select
ActiveCell.FormulaR1C1 = "=""%""&R[-2]C[4]&""%"""
Range("D26").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("D26").Select
Colin Legg
Active Member
Hi Ian,
When you change a cell, the worksheet's Change event is triggered. So you can use the Worksheet_Change() event handler to do this. In Excel, if you right click on the sheet's tab (at the bottom) and choose 'View Code', the VBA IDE will open with the sheet's class module active. Just above the code pane there are two dropdown boxes. Choose Worksheet in the left-hand dropdown box. Then click on the right hand box and you will see all of the event handlers which are available: you want the Change event handler so choose that one. When you pick it, the following code will automatically populate in the code module:
[pre]
[/pre]
So that code works and is quite robust, but I think we can improve the logic. The question is, why do we need to add that formula into B21 each time? If you put it in there manually once, then it should stay in there, right? If we can avoid that line of code then we can simplify the whole thing to:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21"
.Calculate
PivotTables("PivotTable3".PivotCache.Refresh
End If
End Sub[/code][/pre]
Hope that helps...
Colin
When you change a cell, the worksheet's Change event is triggered. So you can use the Worksheet_Change() event handler to do this. In Excel, if you right click on the sheet's tab (at the bottom) and choose 'View Code', the VBA IDE will open with the sheet's class module active. Just above the code pane there are two dropdown boxes. Choose Worksheet in the left-hand dropdown box. Then click on the right hand box and you will see all of the event handlers which are available: you want the Change event handler so choose that one. When you pick it, the following code will automatically populate in the code module:
[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
You can put in here whatever it is that you want to happen. Target is a range object and will hold a reference to the range which was changed. You need to add a check to see if the changed cell was F19 and, if it was, perform the appropriate changes.
[pre][code]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$19" Then
'do some stuff
End If
End Sub
When it comes to the changes you want to make, you have to be very careful. If the change you want to make is to update a cell then you have a problem. That's because changing a cell causes the Worksheet_Change() event handler to be called again, so you could find yourself in a situation where code in the event handler is causing it to call itself: not very desirable! To prevent this, you can temporarily disable application events by using the Application.EnableEvents property. However, you again have to be careful: you must be certain to re-enable events at the end of the procedure otherwise it won't be called again when a change on the worksheet is made. To guarantee that Application.EnableEvents is set back to True you really have to include some error handling, so your starting point is this:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Address = "$F$19" Then
'if we want to change a cell then we must set
'application.enableevents to false to prevent recursion
Application.EnableEvents = False
Range("B21").FormulaR1C1 = "=""%""&R[-2]C[4]&""%"""
PivotTables("PivotTable3").PivotCache.Refresh
End If
ErrorExit:
On Error Resume Next
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox CStr(Err.Number) & vbNewLine & Err.Description
Resume ErrorExit
End SubSo that code works and is quite robust, but I think we can improve the logic. The question is, why do we need to add that formula into B21 each time? If you put it in there manually once, then it should stay in there, right? If we can avoid that line of code then we can simplify the whole thing to:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21"
.CalculatePivotTables("PivotTable3"
.PivotCache.RefreshEnd If
End Sub[/code][/pre]
Hope that helps...
Colin
ianb
Member
Genius. Works for me. Many thanks again..
If I want two search boxes the logic is the same ?
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21").Calculate
PivotTables("PivotTable3").PivotCache.Refresh
End If
If Target.Address = "$F$31" Then
Range("B33").Calculate
PivotTables("PivotTable1").PivotCache.Refresh
End If
If I want two search boxes the logic is the same ?
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21").Calculate
PivotTables("PivotTable3").PivotCache.Refresh
End If
If Target.Address = "$F$31" Then
Range("B33").Calculate
PivotTables("PivotTable1").PivotCache.Refresh
End If
Colin Legg
Active Member
Yes, but since the address can't be both F19 and F31, you can combine those two If...End If blocks into a single If...ElseIf...End If block like this:
[pre]
[/pre]
[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21").Calculate
PivotTables("PivotTable3").PivotCache.Refresh
ElseIf Target.Address = "$F$31" Then
Range("B33").Calculate
PivotTables("PivotTable1").PivotCache.Refresh
End If
End Subianb
Member
Perfect All working.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21").Calculate
PivotTables("PivotTable3").PivotCache.Refresh
Else
If Target.Address = "$F$31" Then
Range("B33").Calculate
PivotTables("PivotTable1").PivotCache.Refresh
End If
End If
ErrorExit:
On Error Resume Next
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox CStr(Err.Number) & vbNewLine & Err.Description
Resume ErrorExit
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Address = "$F$19" Then
'ensure B21 calculates event if calcs set to manual
Range("B21").Calculate
PivotTables("PivotTable3").PivotCache.Refresh
Else
If Target.Address = "$F$31" Then
Range("B33").Calculate
PivotTables("PivotTable1").PivotCache.Refresh
End If
End If
ErrorExit:
On Error Resume Next
Application.EnableEvents = True
Exit Sub
ErrorHandler:
MsgBox CStr(Err.Number) & vbNewLine & Err.Description
Resume ErrorExit
End Sub
ianb
Member
Instructions
Open your file in Excel 2003. Click the "Data" tab near the top of the Excel window.
Click the "Connections" icon right below the "Data" tab. Click the "Properties" button on the right side of the "Workbook Connections" form.
Click the "Definition" tab on the "Connection Properties" form. View the "Command text" box at the bottom of the form to see the data source for the Excel file.
****Parameters to do the : Get Value from Follwing Cell in my Query.********
Open your file in Excel 2003. Click the "Data" tab near the top of the Excel window.
Click the "Connections" icon right below the "Data" tab. Click the "Properties" button on the right side of the "Workbook Connections" form.
Click the "Definition" tab on the "Connection Properties" form. View the "Command text" box at the bottom of the form to see the data source for the Excel file.
****Parameters to do the : Get Value from Follwing Cell in my Query.********
ianb
Member
Instructions
Open your file in Excel 2003. Click the "Data" tab near the top of the Excel window.
Click the "Connections" icon right below the "Data" tab. Click the "Properties" button on the right side of the "Workbook Connections" form.
Click the "Definition" tab on the "Connection Properties" form. View the "Command text" box at the bottom of the form to see the data source for the Excel file.
****Parameters to do the : Get Value from Follwing Cell in my Query.********
Open your file in Excel 2003. Click the "Data" tab near the top of the Excel window.
Click the "Connections" icon right below the "Data" tab. Click the "Properties" button on the right side of the "Workbook Connections" form.
Click the "Definition" tab on the "Connection Properties" form. View the "Command text" box at the bottom of the form to see the data source for the Excel file.
****Parameters to do the : Get Value from Follwing Cell in my Query.********