grouping hidden worksheets

[bennyob]

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.

bennyob wrote:
>
> 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

[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


bennyob wrote:
> 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]

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 wrote:
>
> 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
>
> bennyob wrote:
> > 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

[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 wrote:
> 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 wrote:
>
>>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
>>
>>bennyob wrote:
>>
>>>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]

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>.)



Roger Govier wrote:
>
> 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 wrote:
> > 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 wrote:
> >
> >>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
> >>
> >>bennyob wrote:
> >>
> >>>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