Add Shape on every worksheets

[Arsil Hadjar]

Dear Sirs,

May I have your help on the following
· I want to add show and hide ribbon button on every worksheets by using For – Next loop approach on my workbook
The problem
· The macro only work for the first sheet , not to other sheets
· How to make the macro work for other sheets

The following are the macro that I used in this workbooks

Sub AddShape_to_Allworksheets()


Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Hide_UnhideButton
Next ws

End Sub

Sub Hide_UnhideButton()
'
' Create_Hide_UnhideButton Macro
'
Range("G1").Select
ActiveSheet.Buttons.Add(288.75, 15, 46.5, 15.75).Select
Selection.OnAction = "PERSONAL.XLSB!HideRibbon"
Selection.Characters.Text = "Hide Ribbon"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'ActiveSheet.Shapes("Button 1").ScaleHeight 2.0952380952, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Buttons.Add(382.5, 18.75, 50.25, 26.25).Select
Selection.OnAction = "PERSONAL.XLSB!ShowRibbon"
Selection.Characters.Text = "Show Ribbon"
With Selection.Characters(Start:=1, Length:=11).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

Looking forward to having your suggestion in this regards

Many thanks and Best regards
Arsil Hadjar

 

[mrshl9898]

Looks like the challenge is your Hide_UnhideButton macro refers to ActiveSheet, yet you are not activating it as you loop through them

Maybe change ActiveSheet to ws or activate the sheet when you loop through it

 

[Trebor76]

Hi Arsil Hadjar,

Welcome to MrExcel!!

Try using the slightly modified AddShape_to_Allworksheets macro (note my comment as to the change):

Sub AddShape_to_Allworksheets()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Select 'As the "Hide_UnhideButton" macro is written using ActiveSheet the easiest thing to do is just select the sheet before running the macro
        Hide_UnhideButton
    Next ws

End Sub

Regards,

Robert

 

[Arsil Hadjar]

Dear Robert and mrshl 9898,

With the suggested modification.
The macro is work perfectly as expected,
Thanks for the help.

Best regards
Arsil Hadjar