Excel VBA Code Help

Question

Hello, I'm new to VBA....

Aside from recording my own macros I'm not confident writing my own code from scratch. I have a Macro where I hide and unhide information, charts, drop downs, check boxes etc. on one sheet but I need to copy the same sheet 19 more times and know there's a way to code the Macros once and work for all sheets individually. Could anyone help me!! Below is a two of my macros I need to duplicate on all other sheets. Any help would be greatly appreciated!!!

Sub HIDEINJECTION()

'

' HIDEINJECTION Macro

' Hides only Injection Information

'

' Keyboard Shortcut: Ctrl+i

'

    Rows("41:189").Select

    Selection.EntireRow.Hidden = True

    ActiveSheet.Shapes.Range(Array("Check Box 444")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 438")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 212")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 209")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 201")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 198")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 197")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 139")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 137")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 136")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 92")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Group Box 55")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Select

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 65")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Drop Down 65")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 60")).Visible = msoFalse

    ActiveSheet.Shapes.Range(Array("Check Box 449")).Visible = msoFalse

    Application.CommandBars("Selection").Visible = False

    ActiveWindow.SmallScroll Down:=-20

    ActiveSheet.Shapes.Range(Array("Option Button 174")).Select

    Range("C3").Select

    ActiveWindow.SmallScroll Down:=-8

End Sub

Sub SHOWINJECTION()

'

' SHOWINJECTION Macro

' Shows only injection info

'

'

    Rows("40:190").Select

    Selection.EntireRow.Hidden = False

    ActiveWindow.SmallScroll Down:=24

    ActiveSheet.Shapes.Range(Array("Check Box 444")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 438")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 212")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 209")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 449")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 201")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Check Box 198")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 197")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 160")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 141")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 139")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 137")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 136")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 92")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Drop Down 60")).Visible = msoTrue

    ActiveSheet.Shapes.Range(Array("Group Box 55")).Visible = msoTrue

    Application.CommandBars("Selection").Visible = False

    Range("B41").Select

    ActiveWindow.SmallScroll Down:=4

    ActiveWindow.Zoom = 85

End Sub

Answer 1

This is easy and as a bonus, it is possible to use the same routine to show / hide the same objects. (Your code shows a difference in the HIDE and SHOW routines, therefor maybe you have to apply only Step 1 as described below to your routines).

Step 1:

Add a Worksheet variable to the header of the sub and use this variable inside instead of ActiveSheet.

Step 2:

Add a Boolean variable to the header and use this variable inside instead of True / False

And here is the sample, I took your routine HIDEINJECTION and and now it looks like this:

Sub HIDE_SHOW_INJECTION(ByVal Ws As Worksheet, ByVal HideState As Boolean)
  With Ws
    .Rows("41:189").EntireRow.Hidden = HideState
    With .Shapes
      .Item("Check Box 444").Visible = Not HideState
      .Item("Check Box 438").Visible = Not HideState
      .Item("Check Box 212").Visible = Not HideState
      .Item("Check Box 209").Visible = Not HideState
      .Item("Check Box 201").Visible = Not HideState
      .Item("Check Box 198").Visible = Not HideState
      .Item("Drop Down 197").Visible = Not HideState
      .Item("Drop Down 160").Visible = Not HideState
      .Item("Drop Down 141").Visible = Not HideState
      .Item("Drop Down 139").Visible = Not HideState
      .Item("Drop Down 137").Visible = Not HideState
      .Item("Drop Down 136").Visible = Not HideState
      .Item("Drop Down 92").Visible = Not HideState
      .Item("Group Box 55").Visible = Not HideState
      .Item("Drop Down 160").Visible = HideState
      .Item("Drop Down 160").Visible = Not HideState
      .Item("Drop Down 141").Visible = HideState
      .Item("Drop Down 141").Visible = Not HideState
      .Item("Drop Down 65").Visible = Not HideState
      .Item("Drop Down 65").Visible = HideState
      .Item("Drop Down 60").Visible = Not HideState
      .Item("Check Box 449").Visible = Not HideState
    End With
  End With
End Sub

As you see there is no SELECTION, SELECT anymore and I've also removed all other unnecessary code.

For a first test your new 2 main routines are this:

Sub HIDEINJECTION()
  HIDE_SHOW_INJECTION ActiveSheet, True
End Sub

Sub SHOWINJECTION()
  HIDE_SHOW_INJECTION ActiveSheet, False
End Sub

Try them first to be sure they work on your sheet. If everything goes well, the step to run the code in all sheets is very simple:

Sub HIDEINJECTION_AllWorksheets()
  Dim Ws As Worksheet
  For Each Ws In Worksheets
    HIDE_SHOW_INJECTION Ws, True
  Next
End Sub

Sub SHOWINJECTION_AllWorksheets()
  Dim Ws As Worksheet
  For Each Ws In Worksheets
    HIDE_SHOW_INJECTION Ws, False
  Next
End Sub

Does everything work as desired?

Andreas.

Answer 2

I entered the following but it errors our at the bolded line where the first sheet.shapes is

Sub SHOWINJECTION()

'

' SHOWINJECTION Macro

' Shows only injection info

'

'Dim sheet as Worksheet

For Each Sheet In ActiveWorkbook.Worksheets

Rows("40:190").Select

Selection.EntireRow.Hidden = False

ActiveWindow.SmallScroll Down:=24

Sheet.Shapes.Range(Array("Check Box 444")).Visible = msoTrue

Sheet.Shapes.Range(Array("Check Box 438")).Visible = msoTrue

Sheet.Shapes.Range(Array("Check Box 212")).Visible = msoTrue

Seet.Shapes.Range(Array("Check Box 209")).Visible = msoTrue

Sheet.Shapes.Range(Array("Check Box 449")).Visible = msoTrue

Sheet.Shapes.Range(Array("Check Box 201")).Visible = msoTrue

Sheet.Shapes.Range(Array("Check Box 198")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 197")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 160")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 141")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 139")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 137")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 136")).Visible = msoTrue

Sheet.Shapes.Range(Array("Drop Down 92")).Visible = msoTrue

ActiveSheet.Shapes.Range(Array("Drop Down 60")).Visible = msoTrue

Sheet.Shapes.Range(Array("Group Box 55")).Visible = msoTrue

Application.CommandBars("Selection").Visible = False

Range("B41").Select

ActiveWindow.SmallScroll Down:=4

ActiveWindow.Zoom = 85

Next Sheet

End Sub

Answer 3

Try the following

For each of your macros add the following

At beginning:

Dim sheet as Worksheet
For each sheet in ActiveWorkbook.worksheets

Change Activesheet to sheet in each statement.

at end just before End Sub

Next sheet

That should run your code in each of the worksheets.

Answer 4

Okay, to better explain, obviously the code shows information then hides it. I have assigned a check box so that when I select the check box, the Macro activates. Copying the sheet multiple times doesn't work because the Macro is for the ActiveSheet only.

How would I write it so that I can activate the Macro via check box on each copied sheet?

If I sound confused please help, but I believe I have the jist of it.

Answer 5

Actually, you need not copy this. The code must be in a module and the code is executed for Active Sheet only.

Hence, if you click in another sheet, that sheet becomes active. Hence, when you run this, the code will be running for that sheet only.

Or let me know that you want to run this code once and this should be run for all sheets one by one.