Macro to Split Window in Same Place Even If Columns Added or Hidde

P

PBJ

The following macro groups and formats a set of text boxes, splits the active
window, and then freezes panes. The SplitColumn and SplitRow properties,
however, seem to count only the VISIBLE rows and columns, but I need the
window to always split along the top and left of the same named cell called
“Window_Split_Cell†(with a range currently defined as cell D20). It needs to
split at this location even if users have added or hidden columns to the
left. As it stands, if columns are added or hidden, the macro splits in the
wrong place.

Sub RegroupObjects()
On Error Resume Next
ActiveSheet.Shapes.Range(Array("Text Box A", "Text Box B", "Text Box
C")). _
Select
Selection.ShapeRange.Group.Select
Selection.Name = "Project_Info"
With Selection
.Placement = xlFreeFloating
.PrintObject = True
.OnAction = "UngroupObjects"
End With
With ActiveWindow
.SplitColumn = 4 'This is the problem line. I've tried .SplitColumn
= (Range("Window_Split_Cell")), 'which doesn't do anythiing at all, and
..SplitColumn = (Range("Window_Split_Cell").Column), which runs 'into the same
counting-visible-columns-only problem.
.SplitRow = 20
End With
ActiveWindow.Panes(1).Activate
Range("D17:D18").Select
ActiveWindow.FreezePanes = True
End Sub

Help! What am I doing wrong?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top