Simple vba question.

[vbacoder12]

I have an option explicit sub which is running a series of subs one of my last subs is to freeze panes. Some times it freezes the correct amount of panes some times not. Also if I run this outside of the option explicit sub it works fine here is the vba

Sub freezerow
'
' freezerow macro
'
'
With ActiveWindow
.splitcolumn=3
.splitrow=6
End With
ActiveWindow.Freezepanes=TRUE
End Sub

 

[Alex Blakenburg]

Some times it freezes the correct amount of panes some times not.

Do you actually mean the correct "amount" or do you mean the correct position ?
That code is position dependant and depends what cell is visible in the top left corner of the screen when you run the code

 

[vbacoder12]

Do you actually mean the correct "amount" or do you mean the correct position ?
That code is position dependant and depends what cell is visible in the top left corner of the screen when you run the code

I mean correct position

 

[vbacoder12]

I mean correct position

I want it to be 1st 3 columns of the worksheet and first 6 rows

 

[Alex Blakenburg]

Give this a try:

Sub freezerow()
'
' freezerow macro
'
'
    Dim currCell As Range
    
    Application.ScreenUpdating = False
    
    Set currCell = ActiveCell
    Application.Goto Reference:=ActiveSheet.Range("A1"), _
            scroll:=True
    With ActiveWindow
        If .FreezePanes = True Then .FreezePanes = False
            .FreezePanes = True
    End With
    
    Application.Goto Reference:=currCell

End Sub

 

[vbacoder12]

Give this a try:

Sub freezerow()
'
' freezerow macro
'
'
    Dim currCell As Range
   
    Application.ScreenUpdating = False
   
    Set currCell = ActiveCell
    Application.Goto Reference:=ActiveSheet.Range("A1"), _
            scroll:=True
    With ActiveWindow
        If .FreezePanes = True Then .FreezePanes = False
            .FreezePanes = True
    End With
   
    Application.Goto Reference:=currCell

End Sub

Does your cursor have to be on a certain cell?

 

[Alex Blakenburg]

Oops sorry, I added the line in blue.

Sub freezerow()
'
' freezerow macro
'
'
    Dim currCell As Range
   
    Application.ScreenUpdating = False
   
    Set currCell = ActiveCell
    Application.Goto Reference:=ActiveSheet.Range("A1"), _
            scroll:=True
    Application.Goto Reference:=ActiveSheet.Range("D7")
    With ActiveWindow
        If .FreezePanes = True Then .FreezePanes = False
            .FreezePanes = True
    End With
   
    Application.Goto Reference:=currCell ' Optional add --> , scroll:=True

End Sub

 

[vbacoder12]

I added my split column n row piece it worked perfect

 

[MARK858]

Can I ask what issue you were getting with the code in post 7 that required you to add "split column n row" to the code please, just not sure why the code in post 7 wasn't adequate to carry out your request.

Btw, having "Office Version: Prefer Not To Say" in your profile is not very helpful if you are going to be asking questions

 

[vbacoder12]

Can I ask what issue you were getting with the code in post 7 that required you to add "split column n row" to the code please, just not sure why the code in post 7 wasn't adequate to carry out your request.

Btw, having "Office Version: Prefer Not To Say" in your profile is not very helpful if you are going to be asking questions

Sorry I didn't even know anything about my profile. I had to put it between application.goto reference:=activesheet.range ("a1"),scroll:true

I have another question about another code that isn't working way is thought should I create a new post