Hi all,

I'm requiring the ability to do something that appears rather usual but is
likely to be impossible. Hopefully not...

I want to allow users to choose from a range of buttons which in turn
display small sections of other worksheets (from the same spreadsheet) at
the bottom of the current one. Rather like having a window to other areas
of the same spreadsheet sitting on the current worksheet.

Specifically (using arbitrary examples):

- user clicks on button A whilst on worksheet "Sheet1"
- at the bottom of "Sheet1" code displays a range (say a 10x15 grid of data)
that exists on "Sheet3" in its own window
- user clicks a generic "Close window" button and the window at the bottom
of "Sheet1" goes away

This 'grid' of data needs to be updated as if you were typing into "Sheet3".

This spreadsheet drives a daily meeting and contains a long sequence of
grouped parameters. Each grouping requires a simultaneous view of a small
grid of data from elsewhere in the spreadsheet and the whole sequence so
decisions can be made. Each group requires a different grid, hence the need
to show a range of buttons which each choose a different 'grid' of data.

So there it is - perhaps I'm just going mad, but it would appear to me not
an unnaturally weird thing to want to do.

Any help will be greatly appreciated.

Many regards,
David (Sydney, Aust)

I don't have time to test various situations but I created a macro to give
some coding that might help. I simply open a new window to view Sheet3 and
resize Sheet1 so that Sheet3 is visible at the bottom. Would probably require
some experimenting in order to get the grid of Sheet3 in view but seems like
simplest way to have both seen at same time.

put this macro in Module1
Sub Macro1()

ActiveWindow.WindowState = xlNormal
With ActiveWindow
.Top = 1
.Left = -3.5
.Width = 757.5
.Height = 310.5
End With
ActiveWindow.NewWindow
With ActiveWindow
.Top = 309.25
.Left = 7.75
End With
With ActiveWindow
.Width = 757.5
.Height = 138
End With
Sheets("Sheet3").Select
With ActiveWindow
.Top = 298.75
.Left = -2
End With
Range("A4").Select
Windows("Book2:1").Activate

Range("A2").Select

End Sub

also put this macro in module1

Sub Macro3()

Windows("Book2:2").Activate
ActiveWindow.Close
With ActiveWindow
.Width = 757.5
.Height = 444
End With
End Sub

put this macro on Sheet1 for the checkbox

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Macro1
Else
Macro3
End If
End Sub

had to put the macros in a module so that the program could switch between
sheets. It did not work if all the coding was on Sheet1.

Hope that leads a way to handle your situation



"Dave E" wrote:

> Hi all,
>
> I'm requiring the ability to do something that appears rather usual but is
> likely to be impossible. Hopefully not...
>
> I want to allow users to choose from a range of buttons which in turn
> display small sections of other worksheets (from the same spreadsheet) at
> the bottom of the current one. Rather like having a window to other areas
> of the same spreadsheet sitting on the current worksheet.
>
> Specifically (using arbitrary examples):
>
> - user clicks on button A whilst on worksheet "Sheet1"
> - at the bottom of "Sheet1" code displays a range (say a 10x15 grid of data)
> that exists on "Sheet3" in its own window
> - user clicks a generic "Close window" button and the window at the bottom
> of "Sheet1" goes away
>
> This 'grid' of data needs to be updated as if you were typing into "Sheet3".
>
> This spreadsheet drives a daily meeting and contains a long sequence of
> grouped parameters. Each grouping requires a simultaneous view of a small
> grid of data from elsewhere in the spreadsheet and the whole sequence so
> decisions can be made. Each group requires a different grid, hence the need
> to show a range of buttons which each choose a different 'grid' of data.
>
> So there it is - perhaps I'm just going mad, but it would appear to me not
> an unnaturally weird thing to want to do.
>
> Any help will be greatly appreciated.
>
> Many regards,
> David (Sydney, Aust)
>
>
>

You'll probably have various workbook names so should have put some added
code in there.

>
> put this macro in Module1

Dim WKBK as String '<- added line

> Sub Macro1()
>
WKBK = Activeworkbook.name '<-added line
ActiveWindow.WindowState = xlNormal
> With ActiveWindow
> .Top = 1
> .Left = -3.5
> .Width = 757.5
> .Height = 310.5
> End With
> ActiveWindow.NewWindow
> With ActiveWindow
> .Top = 309.25
> .Left = 7.75
> End With
> With ActiveWindow
> .Width = 757.5
> .Height = 138
> End With
> Sheets("Sheet3").Select
> With ActiveWindow
> .Top = 298.75
> .Left = -2
> End With
> Range("A4").Select

> Windows(WKBK & ":1").Activate '<- changed line
>
> Range("A2").Select
>
> End Sub
>
> also put this macro in module1
>
> Sub Macro3()
> WKBK = Activeworkbook.Name '<-added line
> Windows(WKBK & ":2").Activate '<-changed line
> ActiveWindow.Close
> With ActiveWindow
> .Width = 757.5
> .Height = 444
> End With
> End Sub
>
> put this macro on Sheet1 for the checkbox
>
> Private Sub CheckBox1_Click()
> If CheckBox1.Value = True Then
> Macro1
> Else
> Macro3
> End If
> End Sub
>
> had to put the macros in a module so that the program could switch between
> sheets. It did not work if all the coding was on Sheet1.
>
> Hope that leads a way to handle your situation
>
>
>
> "Dave E" wrote:
>
> > Hi all,
> >
> > I'm requiring the ability to do something that appears rather usual but is
> > likely to be impossible. Hopefully not...
> >
> > I want to allow users to choose from a range of buttons which in turn
> > display small sections of other worksheets (from the same spreadsheet) at
> > the bottom of the current one. Rather like having a window to other areas
> > of the same spreadsheet sitting on the current worksheet.
> >
> > Specifically (using arbitrary examples):
> >
> > - user clicks on button A whilst on worksheet "Sheet1"
> > - at the bottom of "Sheet1" code displays a range (say a 10x15 grid of data)
> > that exists on "Sheet3" in its own window
> > - user clicks a generic "Close window" button and the window at the bottom
> > of "Sheet1" goes away
> >
> > This 'grid' of data needs to be updated as if you were typing into "Sheet3".
> >
> > This spreadsheet drives a daily meeting and contains a long sequence of
> > grouped parameters. Each grouping requires a simultaneous view of a small
> > grid of data from elsewhere in the spreadsheet and the whole sequence so
> > decisions can be made. Each group requires a different grid, hence the need
> > to show a range of buttons which each choose a different 'grid' of data.
> >
> > So there it is - perhaps I'm just going mad, but it would appear to me not
> > an unnaturally weird thing to want to do.
> >
> > Any help will be greatly appreciated.
> >
> > Many regards,
> > David (Sydney, Aust)
> >
> >
> >

Hi Rich,

goodness me - I am extremely grateful to you for your time. Many thanks for
this - I'll throw it into the system and give it a go.

You have doubtless saved me a *lot* of time - very grateful to you indeed!

Regards,
David E (Sydney, Aust)


"Rich J" <(E-Mail Removed)> wrote in message
news:48DE2FF9-F76B-4136-A6E3-(E-Mail Removed)...
> You'll probably have various workbook names so should have put some added
> code in there.
>
>>
>> put this macro in Module1
>
> Dim WKBK as String '<- added line
>
>> Sub Macro1()
>>
> WKBK = Activeworkbook.name '<-added line
> ActiveWindow.WindowState = xlNormal
>> With ActiveWindow
>> .Top = 1
>> .Left = -3.5
>> .Width = 757.5
>> .Height = 310.5
>> End With
>> ActiveWindow.NewWindow
>> With ActiveWindow
>> .Top = 309.25
>> .Left = 7.75
>> End With
>> With ActiveWindow
>> .Width = 757.5
>> .Height = 138
>> End With
>> Sheets("Sheet3").Select
>> With ActiveWindow
>> .Top = 298.75
>> .Left = -2
>> End With
>> Range("A4").Select
>
>> Windows(WKBK & ":1").Activate '<- changed line
>>
>> Range("A2").Select
>>
>> End Sub
>>
>> also put this macro in module1
>>
>> Sub Macro3()
>> WKBK = Activeworkbook.Name '<-added line
>> Windows(WKBK & ":2").Activate '<-changed line
>> ActiveWindow.Close
>> With ActiveWindow
>> .Width = 757.5
>> .Height = 444
>> End With
>> End Sub
>>
>> put this macro on Sheet1 for the checkbox
>>
>> Private Sub CheckBox1_Click()
>> If CheckBox1.Value = True Then
>> Macro1
>> Else
>> Macro3
>> End If
>> End Sub
>>
>> had to put the macros in a module so that the program could switch
>> between
>> sheets. It did not work if all the coding was on Sheet1.
>>
>> Hope that leads a way to handle your situation
>>
>>
>>
>> "Dave E" wrote:
>>
>> > Hi all,
>> >
>> > I'm requiring the ability to do something that appears rather usual but
>> > is
>> > likely to be impossible. Hopefully not...
>> >
>> > I want to allow users to choose from a range of buttons which in turn
>> > display small sections of other worksheets (from the same spreadsheet)
>> > at
>> > the bottom of the current one. Rather like having a window to other
>> > areas
>> > of the same spreadsheet sitting on the current worksheet.
>> >
>> > Specifically (using arbitrary examples):
>> >
>> > - user clicks on button A whilst on worksheet "Sheet1"
>> > - at the bottom of "Sheet1" code displays a range (say a 10x15 grid of
>> > data)
>> > that exists on "Sheet3" in its own window
>> > - user clicks a generic "Close window" button and the window at the
>> > bottom
>> > of "Sheet1" goes away
>> >
>> > This 'grid' of data needs to be updated as if you were typing into
>> > "Sheet3".
>> >
>> > This spreadsheet drives a daily meeting and contains a long sequence of
>> > grouped parameters. Each grouping requires a simultaneous view of a
>> > small
>> > grid of data from elsewhere in the spreadsheet and the whole sequence
>> > so
>> > decisions can be made. Each group requires a different grid, hence the
>> > need
>> > to show a range of buttons which each choose a different 'grid' of
>> > data.
>> >
>> > So there it is - perhaps I'm just going mad, but it would appear to me
>> > not
>> > an unnaturally weird thing to want to do.
>> >
>> > Any help will be greatly appreciated.
>> >
>> > Many regards,
>> > David (Sydney, Aust)
>> >
>> >
>> >

Glad I could help.
Let me know how it goes.