running code

[mark]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[JulieD]

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]

Thanks this works a treat.
soz about the duplicate posting.