vba to center sheet on range...

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I have a macro button in my custom Ribbon Menuto go to a worksheet (Project) which has the following code:

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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,879
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:

Code:
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
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:
Code:
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
 

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,879
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

Active Member
Joined
Dec 14, 2016
Messages
369
Office Version
  1. 2010
Platform
  1. Windows
Ah, I understand now. I got her workin'!

Brilliant. Thanks very much!

happy new year to you buddy :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,408
Messages
5,595,953
Members
414,035
Latest member
billbumkins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top