Repeated code - how to write it just once 3

[eti3nne]

I have an Excel workbook with several sheets. Each sheet has a button on it to hide (all but the next 6) or show (all) sheets within the workbook. I have added code to each button to do this, but I thought I could simplify my maintenance if I stored the code just once (on the master sheet somewhere) and just referred to the code when needed.

How do I go about this?

Thanks for looking.

 

[HarleyQuinn]

You would put the code in the button Click in a Sub and then call that Sub in the buttons Click event.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[beacon5]

Assuming that your code isn't in a module, you can create a new module, add the code to the module, create a custom menu button on the toolbar, and then assign a macro to the button to execute the code.

Depending on how your code is written, you shouldn't have to modify it at all since there doesn't appear to be any conditions on which worksheet is open...they're either all open or they're all closed.

I can walk you through the steps to do this if you have trouble.

 

[combo]

You can call a procedure in a standard module. Depending on the controls you use (from 'Forms' or 'Control toolbox') you can:
- 'Forms' button:
Assign common procedure to each button, if it needs to be button-sensitive, use button mame as argument (returned by Application.Caller).
- 'Control toolbox' commandbutton:
Call the same procedure in each button's code (worksheet module), you can use Me to pass sheet as object to recognise calling sheet.

combo

 

[eti3nne]

Thanks HarleyQuinn and beacon5 ...

I've gone with HarleyQuinn suggestion and the code is running now, but I have the following limitation...

The button (CommandButton2)on each sheet is supposed to change from hide to unhide after the code has run (code has been tested OK previously). The button text does not change however, either from hide to unhide or vice versa and the if clause below always results in the unhide running.

Sub HideEmAndFindEm:

If CommandButton2.Caption = "Unhide" Then
code to unhide all the sheets and
CommandButton2.Caption = "hide"

else
code to hide all the sheets (bar next 6) and
CommandButton2.Caption = "Unhide"
end if

?This would seem to be as a result of putting the code behind the sub Sub HideEmAndFindEm?

Any ideas?

Thanks for your follow up.

 

[combo]

Each sheet is an object with button, you need to precede button with its code name, can be changed in (Name) entry. To simpliy management, you could consider to create an array of buttons (below assumed three buttons:

Private Sub Workbook_Open()
Set buttons(1) = sh1.CommandButton1
Set buttons(2) = sh2.CommandButton1
Set buttons(3) = sh3.CommandButton1
SheetsHidden = True
ButtonClicked
End Sub

Private Sub CommandButton1_Click()
Call ButtonClicked
End Sub

Public buttons(1 To 3) As MSForms.CommandButton
Public SheetsHidden As Boolean

Sub ButtonClicked()
Dim sButtonCaption As String
SheetsHidden = Not SheetsHidden
If SheetsHidden Then
    sButtonCaption = "unhide"
Else
    sButtonCaption = "hide"
End If
For Each b In buttons
    b.Caption = sButtonCaption
Next b
End Sub

combo