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

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

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

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


.