#1
|
|||
|
|||
Return Freeze panes location
Hi All, another quandary. Is it possible to return the location of were the freeze panes was set. E.G. If I select say "D4" and freeze the panes, can I use VB to get that location so I can do something different than if the freeze panes was at say "G5".
What I'm trying to do is set the turn on/off the freeze panes via a button click, but want to always keep the top 2 rows frozen. the 2 states would be freeze panes at "3:3" or freeze panes "H4" Here's my code so far... Code:
Sub Freeze_Panes() curcel = ActiveCell.Address(0, 0) If ActiveWindow.FreezePanes = True Then ActiveWindow.FreezePanes = False ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane" Else Range("H4").Select ActiveWindow.FreezePanes = True ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane" End If Range(curcel).Select 'select original cell End Sub |
#2
|
||||
|
||||
Perhaps you could SendKeys to issue the Ctrl-Home command, then test the destination address.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks for the reply, I can see what your doing, but it seems a bit long winded for me, I did find that using .SplitRow And .SplitColumn I can determine the location faster, I will update my code when I get a chance.
|
#4
|
|||
|
|||
change the freeze panes location with VBA macro
Turns out it was easy,
Code:
Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H If ActiveWindow.SplitRow = 3 And ActiveWindow.SplitColumn = 7 Then ActiveWindow.SplitRow = 3 ActiveWindow.SplitColumn = 0 ActiveWindow.FreezePanes = True ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane" Else ActiveWindow.SplitRow = 3 ActiveWindow.SplitColumn = 7 ActiveWindow.FreezePanes = True ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane" End If End Sub |
#5
|
||||
|
||||
Simpler:
Code:
Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H With ActiveWindow .SplitRow = 3 If .SplitColumn = 7 Then .SplitColumn = 0 ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCr & "Pane" Else .SplitColumn = 7 ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freeze" & vbCr & "Pane" End If .FreezePanes = True End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Yes it is, which made me think this...
Code:
Sub Freeze_Panes() 'button click macro to freeze panes at row 4 and col A or Col H If ActiveWindow.SplitColumn = 7 Then ActiveWindow.SplitColumn = 0 ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Freeze" & vbCrLf & "Pane" Else ActiveWindow.SplitColumn = 7 ActiveSheet.Shapes("FP").TextFrame.Characters.Text = "Un-Freze" & vbCrLf & "Pane" End If ActiveWindow.FreezePanes = True End Sub |
#7
|
||||
|
||||
I prefer the With … End With structure. More importantly, though, your omission of 'ActiveWindow.SplitRow = 3' means you're no longer controlling the possibility that someone might have manually changed the row split.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
I'm the only user so not a problem, and the macro button is only on the ActiveWindow, so don't need the With...End With.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Default location for the Office Document Cache Server drafts location. | dianahbr | Office | 0 | 03-02-2018 12:37 PM |
Return to previous location in Word 2016 | stevelsd | Word | 2 | 09-30-2017 06:18 AM |
How to Customize Outlook Panes | Steve_B | Outlook | 0 | 07-08-2014 09:05 AM |
Freeze more than just the top row, or first column | Gary Drumm | Excel | 4 | 07-23-2013 11:34 AM |
Outlook 2007 win7x32 Archive location/backup location question | michael.monaco | Outlook | 0 | 11-19-2012 02:07 PM |