grouping hidden worksheets

[Guest]

I have budgets in 2 languages on separate worksheets (with the same data),
and users make changes in the different language sheets.
At present I have a macro that groups the sheets so that data entered in one
language is reflected in the data of the other.
I would like to hide the worksheet of the language not in use but still have
it grouped so changes are reflected. Is this possible?

 

[Dave Peterson]

Grouping the sheets is selecting them. To select them, they have to be
visible.

So I think you'll have to find another approach.

 

[Roger Govier]

Hi

Dave was quite right that you cannot do it manually, but I just tried a
simple macro and it appears to work.
Sheet2 remained grouped but not visible and accepted all alteration made on
Sheet1 and Sheet3

Adapt to suit.

Sub Macro4()
'
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet2").Visible = False
Sheets("Sheet1").Activate

End Sub

Regards

Roger Govier

 

[Dave Peterson]

Good example.

But it would scare me to trust that the worksheets are still grouped at any
given point in time.

Even a worksheet event would scare me off (too many things can go wrong):

Option Explicit
Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Me.Activate
Application.ScreenUpdating = True

End Sub

Just rightclicking on the worksheet tab and selecting "ungroup sheets" could
cause trouble.

 

[Roger Govier]

Hi Dave

I did say "appears to work".
I'm sure its not foolproof, but it seemed to offer a possible solution to
the OP.
You are quite right to put the caveats in and maybe "scare the living
daylights" out of the OP<vbg>, but he did say he already ran macros to group
the worksheets.


Regards

Roger Govier

 

[Dave Peterson]

I was agreeing with you that it could be done. But just warning the OP that I
wouldn't use it.

(I think we're in violent agreement <bg>.)