Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2018, 03:53 PM
trevorc trevorc is offline Return Freeze panes location Windows 7 32bit Return Freeze panes location Office 2013
Competent Performer
Return Freeze panes location
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 09-17-2018, 04:17 PM
macropod's Avatar
macropod macropod is offline Return Freeze panes location Windows 7 64bit Return Freeze panes location Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Perhaps you could SendKeys to issue the Ctrl-Home command, then test the destination address.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-17-2018, 04:37 PM
trevorc trevorc is offline Return Freeze panes location Windows 7 32bit Return Freeze panes location Office 2013
Competent Performer
Return Freeze panes location
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #4  
Old 09-18-2018, 01:22 PM
trevorc trevorc is offline Return Freeze panes location Windows 7 32bit Return Freeze panes location Office 2013
Competent Performer
Return Freeze panes location
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #5  
Old 09-18-2018, 02:14 PM
macropod's Avatar
macropod macropod is offline Return Freeze panes location Windows 7 64bit Return Freeze panes location Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #6  
Old 09-18-2018, 02:21 PM
trevorc trevorc is offline Return Freeze panes location Windows 7 32bit Return Freeze panes location Office 2013
Competent Performer
Return Freeze panes location
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
Reply With Quote
  #7  
Old 09-18-2018, 02:24 PM
macropod's Avatar
macropod macropod is offline Return Freeze panes location Windows 7 64bit Return Freeze panes location Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #8  
Old 09-18-2018, 02:49 PM
trevorc trevorc is offline Return Freeze panes location Windows 7 32bit Return Freeze panes location Office 2013
Competent Performer
Return Freeze panes location
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
Reply

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 Freeze panes location 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:46 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft