Home |
Search |
Today's Posts |
#1
|
|||
|
|||
running code
I have the following code in my workbook, how can I use a
command button to run this. Private Sub Workbook_Open() Dim wb As Workbook 'copy all sheets Worksheets.Copy Set wb = ActiveWorkbook Application.DisplayAlerts = False 'delete the sheets you want wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth Qtr")).Delete Application.DisplayAlerts = True End Sub any help please Mark |
#2
|
|||
|
|||
Mark
Put it in a standard code module and assign the macro to a command button by selecting one from the forms toolbar, right clicking and selecting 'Assign macro'. The way you have it set up currently it is in the 'ThisWorkbook' class module and will run by the Open event (Each time the workbook is first opened) For explanation of the different modules, check here http://www.nickhodge.co.uk/vba/vbaimplement.htm -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "mark" wrote in message ... I have the following code in my workbook, how can I use a command button to run this. Private Sub Workbook_Open() Dim wb As Workbook 'copy all sheets Worksheets.Copy Set wb = ActiveWorkbook Application.DisplayAlerts = False 'delete the sheets you want wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth Qtr")).Delete Application.DisplayAlerts = True End Sub any help please Mark |
#3
|
|||
|
|||
Hi
if you have it under Workbook_Open in the ThisWorkbook module the code will run when you open the workbook - no button needed (ensure that you have your security settings set to medium - tools / macros / security) if you want to run it via a button, cut the code from here and keep it in memory switch back to the excel worksheet - display the control toolbox toolbar (view / toolbars), click on the command button, click on your worksheet where you want it, right mouse click on the command button and choose view code - now paste your code in there excluding the Private Sub and End Sub lines then switch back to the excel worksheet - click on the exit design mode icon on the control toolbox toolbar and test out your button. Cheers JulieD "mark" wrote in message ... I have the following code in my workbook, how can I use a command button to run this. Private Sub Workbook_Open() Dim wb As Workbook 'copy all sheets Worksheets.Copy Set wb = ActiveWorkbook Application.DisplayAlerts = False 'delete the sheets you want wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth Qtr")).Delete Application.DisplayAlerts = True End Sub any help please Mark |
#4
|
|||
|
|||
Thanks this works a treat.
soz about the duplicate posting. -----Original Message----- Hi if you have it under Workbook_Open in the ThisWorkbook module the code will run when you open the workbook - no button needed (ensure that you have your security settings set to medium - tools / macros / security) if you want to run it via a button, cut the code from here and keep it in memory switch back to the excel worksheet - display the control toolbox toolbar (view / toolbars), click on the command button, click on your worksheet where you want it, right mouse click on the command button and choose view code - now paste your code in there excluding the Private Sub and End Sub lines then switch back to the excel worksheet - click on the exit design mode icon on the control toolbox toolbar and test out your button. Cheers JulieD "mark" wrote in message ... I have the following code in my workbook, how can I use a command button to run this. Private Sub Workbook_Open() Dim wb As Workbook 'copy all sheets Worksheets.Copy Set wb = ActiveWorkbook Application.DisplayAlerts = False 'delete the sheets you want wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth Qtr")).Delete Application.DisplayAlerts = True End Sub any help please Mark . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Command Button VBA code | Excel Discussion (Misc queries) |