supergarebear
New Member
- Joined
- Nov 18, 2015
- Messages
- 5
I'm trying to create a function that can be applied to multiple worksheets based on the worksheet name. I know what function I want to apply, but I can't seem to figure out how to organize the process
Layout: You have 3 shapes in each worksheet (M1,M2,M3). M1 links to Sheet A1B1, M2 links to A2B1, M3 links to A3B1, this is the same to all worksheets.
I want a button to format the workbook by hiding the shapes (M1-3) based on if the linked sheets (A1B1,A2B1,A3B1) are hidden or not. The worksheets this applies to depends on the values I put into the array
Example:
ArrayA=1,2,3
ArrayB=1,3
Worksheet A2B1 is hidden
Target shape to hide: M2
In this case, because A2B1 is hidden, the macro would hide shape M2 to worksheets A1B1, A1B3, A2B1, A2B3, A3B1, A3B3, leaving all the B2 sheets alone.
I tried to create a macro first just to apply it to all pages, but even that is not working. I know it's a complicated request but it would make my work a lot easier than typing out every combination of actions
Workbook layout:
<tbody>
</tbody>
Layout: You have 3 shapes in each worksheet (M1,M2,M3). M1 links to Sheet A1B1, M2 links to A2B1, M3 links to A3B1, this is the same to all worksheets.
I want a button to format the workbook by hiding the shapes (M1-3) based on if the linked sheets (A1B1,A2B1,A3B1) are hidden or not. The worksheets this applies to depends on the values I put into the array
Example:
ArrayA=1,2,3
ArrayB=1,3
Worksheet A2B1 is hidden
Target shape to hide: M2
In this case, because A2B1 is hidden, the macro would hide shape M2 to worksheets A1B1, A1B3, A2B1, A2B3, A3B1, A3B3, leaving all the B2 sheets alone.
I tried to create a macro first just to apply it to all pages, but even that is not working. I know it's a complicated request but it would make my work a lot easier than typing out every combination of actions
Code:
Sub testhide()
Dim ws as worksheet
Dim numA as long
Dim numB as long
Dim shape as string
Set ws = sheets("A" & numA & "B" & numB)
Set shape = "M2"
For numA = 1 to 3
For numB = 1 to 3
For each ws in worksheets
If Worksheets("A2B1").Visible = False Then
ws.Shapes.Range(shape).Visible = False
End If
Next ws
Next numA
Next numB
End Sub
Workbook layout:
Worksheet Name | Shape Names in Worksheet |
A1B1 | M1 M2 M3 |
A1B2 | M1 M2 M3 |
A1B3 | M1 M2 M3 |
A2B1 | M1 M2 M3 |
A2B2 | M1 M2 M3 |
A2B3 | M1 M2 M3 |
A3B1 | M1 M2 M3 |
A3B2 | M1 M2 M3 |
A3B3 | M1 M2 M3 |
<tbody>
</tbody>