| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Executing a command when clicking on a tab
I need to write a macro that will automatically start whenever a user clicks
on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
Hi Bob
There is activate event for every worksheet Copy this in a sheet module Private Sub Worksheet_Activate() End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Bob" wrote in message ... I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
Hi Ron,
Thanks for the info! Bob "Ron de Bruin" wrote: Hi Bob There is activate event for every worksheet Copy this in a sheet module Private Sub Worksheet_Activate() End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Bob" wrote in message ... I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
right click on the sheet tab, click on "View Code". Then paste this:
Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.PivotCache.Refresh Next pvt End Sub -- Hope that helps. Vergel Adriano "Bob" wrote: I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
Vergel,
Thanks for the help! Since there is only one PivotTable involved here, can you tell me how to simplify your macro (since it appears that it covers multiple PivotTables)? Thanks again, Bob "Vergel Adriano" wrote: right click on the sheet tab, click on "View Code". Then paste this: Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.PivotCache.Refresh Next pvt End Sub -- Hope that helps. Vergel Adriano "Bob" wrote: I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
Bob,
You can replace the For Loop block with this one line: Me.PivotTables(1).PivotCache.Refresh -- Hope that helps. Vergel Adriano "Bob" wrote: Vergel, Thanks for the help! Since there is only one PivotTable involved here, can you tell me how to simplify your macro (since it appears that it covers multiple PivotTables)? Thanks again, Bob "Vergel Adriano" wrote: right click on the sheet tab, click on "View Code". Then paste this: Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.PivotCache.Refresh Next pvt End Sub -- Hope that helps. Vergel Adriano "Bob" wrote: I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Executing a command when clicking on a tab
Vergel,
Perfect! Thanks again for all your help. Bob "Vergel Adriano" wrote: Bob, You can replace the For Loop block with this one line: Me.PivotTables(1).PivotCache.Refresh -- Hope that helps. Vergel Adriano "Bob" wrote: Vergel, Thanks for the help! Since there is only one PivotTable involved here, can you tell me how to simplify your macro (since it appears that it covers multiple PivotTables)? Thanks again, Bob "Vergel Adriano" wrote: right click on the sheet tab, click on "View Code". Then paste this: Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.PivotCache.Refresh Next pvt End Sub -- Hope that helps. Vergel Adriano "Bob" wrote: I need to write a macro that will automatically start whenever a user clicks on (i.e., switches to) a certain tab (Sheet2). And all the macro needs to do execute the PivotTable "Refresh Data" command. Can someone kindly tell me how to do this? Thanks in advance for any help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Store data JUST entered in Activecell after clicking command button on worksheet | Excel Programming | |||
| executing an add in | Excel Programming | |||
| can I set up a sort command to happen by clicking on a table headi | Excel Programming | |||
| Error when executing 'Charts.Add' command | Excel Programming | |||
| Keyboard command to replace double clicking the fill handle | Excel Discussion (Misc queries) | |||
Hybrid Mode
