vba to center sheet on range...

[RobbieC]

Hi there, I have a macro button in my custom Ribbon Menuto go to a worksheet (Project) which has the following code:

Sub ProjectInfo_button(control As IRibbonControl)  On Error Resume Next
  With Worksheets("Project")
    .Activate
  End With
End Sub

This works fine.

What I would like is for the vba code to center in on a range (H2:K2) so that this page is always in the center of the screen, no matter what size the users monitor/window is. I have provided enough 'empty columns' either side of my range so that it can be 'scrolled' into position...

Is this possible?

Thanks

 

[pgc01]

Hi

In your example you have a range in row 2, which means you can only center it horizontally.

For the general case, center horizontally and vertically, this is an example that you can adapt:

Sub CenterHV()
Dim r As Range

Set r = Worksheets("Project").Range("BH2000:BK2000")

Application.Goto Reference:=r, Scroll:=True
ActiveWindow.SmallScroll _
    Up:=(ActiveWindow.VisibleRange.Rows.Count - r.Rows.Count) / 2, _
    ToLeft:=(ActiveWindow.VisibleRange.Columns.Count - r.Columns.Count) / 2
End Sub

 

[RobbieC]

Thanks for your reply. I have adapted my range so that I have equal number of rows above and below on my BIG monitor (as with the columns either side). The range is now H12:K24 - this means that my 'box' is in the center of my screen with plenty of space around it.

Would this mean I change the above code to:

Sub CenterHV()Dim r As Range


Set r = Worksheets("Project").Range("H12:K24")


Application.Goto Reference:=r, Scroll:=True
ActiveWindow.SmallScroll _
    Up:=(ActiveWindow.VisibleRange.Rows.Count - r.Rows.Count) / 2, _
    ToLeft:=(ActiveWindow.VisibleRange.Columns.Count - r.Columns.Count) / 2
End Sub

I presume I'd apply this code to 'ThisWorkbook(code)' in the vba editor? What do I do to automate Sub CenterHV()?

Sorry for all the questions... Thanks

 

[pgc01]

Would this mean I change the above code to:

Did you try it?
Is it working OK?

 

[RobbieC]

No, I couldn't get it to run (see above). I applied the code onto the 'ThisWorkbook(code)' in the vba editor. Is this correct?

Do I then have to alter my 'Sub ProjectInfo_button' code to activate the new code on click?

Thanks

 

[pgc01]

Before applying it to the button just copy the code you posted to a general module (insert a module, if it's the first it's named Module1) and then execute it.

I can run you code and it works OK if I make the window small (for ex. 20 rows x 12 columns).

In my case it it won't work with the whole monitor because in that case I have 40 rows x 30 columns and your range is too close to the left corner of the worksheet and there's no way it can be at the center.

It will work with a range like AH112:AK124.

Please try

 

[RobbieC]

Ah, I understand now. I got her workin'!

Brilliant. Thanks very much!

happy new year to you buddy

 

[pgc01]

You're welcome.
... and happy new year to you too.