Applying the same code for different sheets-CodePudding

I'm completely new to VBA and I'm trying to learn a little bit about it! I am trying to create a catalog for different equipment, and it was made attributing macros to icons and creating a loop to generate the catalog based in what I'm clicking.

The problem is, as you can see, it's attributed to different shapes and groups inside Sheet1, and because of the size of the catalog, I'm trying to create more worksheets in which I can divide the information. Furthermore, when I change the sheets in the code, it still tries to apply to the first worksheet, because of all of the templates and icons put.

Is there a possible way in which I can assign the code in every module to a specific sheet? Or do I need to manually alter everything for every worksheet? Below follows a little part of the code to show how It's used around the modules.

Thanks in advance!

Dim CustRow As Long, ProdCol As Long, ProdRow As Long, LastProdRow As Long, LastResultRow As Long
Dim DataType As String
Dim ItemShp As Shape
Sub Customize_Open()
With Sheet1
'limpar grupos menos amostra
For Each ItemShp In .Shapes
    On Error Resume Next
    If InStr(ItemShp.Name, "Picture") <> Empty Then ItemShp.Delete
    If InStr(ItemShp.Name, "ItemGrp") <> Empty Then ItemShp.Delete
    On Error GoTo 0
Next ItemShp
On Error Resume Next
.Shapes("SampleGrp").Visible = msoCTrue
.Shapes("SampleGrp").Ungroup
On Error GoTo 0
'colocar em free floating
For Each ItemShp In .Shapes
 If InStr(ItemShp.Name, "Sample") > 0 Then
 ItemShp.Placement = xlFreeFloating
 ItemShp.Visible = msoCTrue
End If
Next ItemShp
.Shapes("CloseCustBtn").Visible = msoCTrue
.Shapes("ResetBtn").Visible = msoCTrue
.Shapes("LabelTemplate").Visible = msoCTrue
.Shapes("OpenCustBtn").Visible = msoFalse
.Range("C:F").EntireColumn.Hidden = False
End With
End Sub
Sub Customize_Close()
With Sheet1
    On Error Resume Next
     .Shapes("SampleGrp").Placement = xlFreeFloating
    .Shapes("SampleGrp").Visible = msoFalse
    On Error GoTo 0
    .Shapes("CloseCustBtn").Visible = msoFalse
    .Shapes("LabelTemplate").Visible = msoFalse
    .Shapes("ResetBtn").Visible = msoFalse
    .Shapes("OpenCustBtn").Visible = msoCTrue
       .Range("C:F").EntireColumn.Hidden = True
End With
End Sub

CodePudding user response：

You should loop through all sheets. Here's an example mixed with your code...

Sub Customize_Open()
Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    
        With ws
        'limpar grupos menos amostra
        For Each ItemShp In .Shapes
              'etc etc
        Next ItemShp
        'more etc
        End With
        
    Next ws

End Sub

Here's a more simplified illustration of looping through a workbook's worksheets. YOu could run this on any excel file.

Sub LoopAllSheetsExample()
Dim ws As Worksheet, zAnswer As Long

    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
         zAnswer = MsgBox("This is worksheet " & ws.Name & ". It has a usedRange of:" & ws.UsedRange.Address & _
            ". Continue looping through worksheets?", vbYesNo)
    
        If zAnswer <> vbYes Then Exit For
    
    Next ws

End Sub

CodePudding user response：

This sounds like code that you want to execute for each sheet so that they will have similar look & feel. You could put the code in a user module, and then call that module for each of the sheets that you want to customize. OR - if you want a user to click "reset" or "go" on each sheet - then you would call your subroutine and send it the "activesheet".

** The main trick I would use is to name your objects with the sheet index # and an underscore ... e.g. "1_SampleGrp" then you can do what I do in the code below - build a string to refer to the object, and reference the object using that string variable instead of with the string literal.


Public Sub DoStuff(ByVal shtToUpdate As Worksheet)
    
    strShapeToFloat = shtToUpdate.Index & "_SampleGrp" '<--this is the main trick
    strCloseButton = shtToUpdate.Index & "_CloseCustBtn"
    
    
    With shtToUpdate
        On Error Resume Next
         .Shapes(strShapeToFloat).Placement = xlFreeFloating
        .Shapes(strShapeToFloat).Visible = msoFalse
        On Error GoTo 0
        .Shapes(strCloseButton).Visible = msoFalse
        
    End With
End Sub

'to call the above, use something like this
Sub CallTheUpdates()
    Dim SheetToSend As Worksheet
    
    DoStuff ThisWorkbook.Sheets("Sheet1")
    DoStuff ThisWorkbook.Sheets("Sheet1")
End Sub

This ensures names will be resolved but requires work up front.

If you can abstract your requirements to something where you just want to show ALL buttons where the name includes "CloseButton" - then @pgSystemsTester's answer is great for looping through all shapes and you can throw in a "If Instr(itemShp.Name , "CloseButton")>0 then ItemShp.Show." But, if you have extensive customization, the naming trick I mentioned might be a good fit.