tchristie33
asked on
Freeze window panes in excel VB
How do i freeze the window panes in excel vb if I want to call out the sheet name
So......... Don't use this : ActiveWindow.FreezePanes = True
Instead... I want to call out the cell location of where to freeze them as well as the sheet
something like this. (FA is the sheet name)
FA.Cells.Find(what:="total
So......... Don't use this : ActiveWindow.FreezePanes = True
Instead... I want to call out the cell location of where to freeze them as well as the sheet
something like this. (FA is the sheet name)
FA.Cells.Find(what:="total
Last Comment
To find a cell content and freeze pane there:
Private Sub CommandButton1_Click()
Dim Search_Range As Range
Dim c As Range
Worksheets("Sheet2").Activ
Set Search_Range = Range("A1:X10")
With Search_Range
Set c = .Find(What:="total") ', LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=MatchCase,
Set c = .FindNext(c)
Range(c.Address).Select
ActiveWindow.FreezePanes = True
End With
End Sub
Private Sub CommandButton1_Click()
Dim Search_Range As Range
Dim c As Range
Worksheets("Sheet2").Activ
Set Search_Range = Range("A1:X10")
With Search_Range
Set c = .Find(What:="total") ', LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=MatchCase,
Set c = .FindNext(c)
Range(c.Address).Select
ActiveWindow.FreezePanes = True
End With
End Sub
ASKER
I was hoping for 1 line of code to perform this operation. Please note that in my question I said "Don't use this : ActiveWindow.FreezePanes = True"
I don't want to have to activate the page to perform this command....
I don't want to have to activate the page to perform this command....
If a freeze pane already exists, you should turn it off first so that when you set it again the new location will take hold. Try the following:
Sub SetFreezePane ()
Dim c As Range: Set c = Sheet1.Cells.Find("total").Offset(1, 0)
If Not c Is Nothing Then ActiveWindow.FreezePanes = False: ActiveWindow.FreezePanes = True
End Sub
Because the freeze pane is applied to the Windows object, you need to activate the applicable sheet in the window in order to apply the freeze pane accordingly. You could trap which window is currently active and retrn to that window after applying the freeze pane. Also, because you are trying to apply this below a cell content you are trying to find, doing this in 1 line of code is not possbile.
See the following:
See the following:
Sub SetFreezePane()
Dim c As Range
Set c = Worksheets("FA").Cells.Find("total").Offset(1, 0)
If Not c Is Nothing Then
Dim s As Worksheet
Set s = ActiveSheet
Application.Goto c
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
s.Activate
End If
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for actually reading my question and taking the time to respond
tchristie33: irudyk made the same comment as zorvek and provided a solutin and you give all the points to zorvek? At least we came up with solutions (like them or not. There is an option to point split (assist).
TRobinJames,
The Asker specifically stated "So......... Don't use this : ActiveWindow.FreezePanes = True". I read the question as "Can I freeze panes without using the ActiveWindow object?"
Kevin
The Asker specifically stated "So......... Don't use this : ActiveWindow.FreezePanes = True". I read the question as "Can I freeze panes without using the ActiveWindow object?"
Kevin
Visual Basic Classic
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.
165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Worksheets("FA").Activate
Range("B2:F4").Select
ActiveWindow.FreezePanes = True