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" wrote in message
...
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.