Scroll to selected cell

[Sandy]

I have a piece of code on a button on Sheets("Current Round"). The button is
at the top of the sheet.

If ******Code******* Then
MsgBox "There are incomplete entries etc"

Sheets("Current Round").Range("D109").Select

Exit Sub
End If

How can I force Cell "D109" not only to be selected but also visible in the
window.

Thanks
Sandy

 

[Susan]

i ran a test macro on a sheet & "select" did move the screen to the
selected cell. perhaps you have application.screenupdating set to
false?
hth
susan

 

[Bernie Deitrick]

Sandy,

As long as Current Round is the activesheet:

Application.Goto Sheets("Current Round").Range("D109"), True

HTH,
Bernie
MS Excel MVP

 

[Sandy]

I am sure it is something simple but not the screenupdating - full code on
button :-

Sub InsertHomePar()

If EmptyRng(Sheets("Current Round").Range("D109,C111:C111,C114:K114"))
Then

MsgBox "There are incomplete entries" _
& vbCrLf & _
"in the Home Par section"

Sheets("Current Round").Range("D109").Select

Exit Sub
End If


Sheets("Current Round").Unprotect Password:="*****"

Application.ScreenUpdating = False
Application.EnableEvents = False


Sheets("Current Round").Range("C14:K14").Value = Sheets("Current
Round").Range("C111:K111").Value
Sheets("Current Round").Range("C20:K20").Value = Sheets("Current
round").Range("C114:K114").Value
Sheets("Current Round").Range("H3").Value = Sheets("Current
round").Range("D109").Value

Application.ScreenUpdating = True
Application.EnableEvents = True

Sheets("Current Round").Protect Password:="*****"

End Sub

 

[Sandy]

Tried your suggestion Bernie - but no joy.
The button is on ("Current Round").

I have put complete code on button below :-

Sub InsertHomePar()

If EmptyRng(Sheets("Current Round").Range("D109,C111:C111,C114:K114"))
Then

MsgBox "There are incomplete entries" _
& vbCrLf & _
"in the Home Par section"

Application.GoTo Sheets("Current Round").Range("D109"), True

Exit Sub
End If


Sheets("Current Round").Unprotect Password:="pinev85"

Application.ScreenUpdating = False
Application.EnableEvents = False


Sheets("Current Round").Range("C14:K14").Value = Sheets("Current
Round").Range("C111:K111").Value
Sheets("Current Round").Range("C20:K20").Value = Sheets("Current
round").Range("C114:K114").Value
Sheets("Current Round").Range("H3").Value = Sheets("Current
round").Range("D109").Value

Application.ScreenUpdating = True
Application.EnableEvents = True

Sheets("Current Round").Protect Password:="pinev85"


End Sub

 

[JLGWhiz]

You have to Activate, then select to get the scroll effect.

Sheets("Current Round").Activate
Range("D109").Select

But if you want to do something to the cell you can use the other syntax,
like:

Sheets("Current Round").Range("D109") = "My Cell"

It's a design thing.

 

[Sandy]

That doesn't work either I'm afraid.

You have to Activate, then select to get the scroll effect.

Sheets("Current Round").Activate
Range("D109").Select

But if you want to do something to the cell you can use the other syntax,
like:

Sheets("Current Round").Range("D109") = "My Cell"

It's a design thing.

 

[Bernie Deitrick]

Sandy,

It works for me - I took out the line

If EmptyRng(Sheets("Current Round").Range("D109,C111:C111,C114:K114")) Then

Do you get the Msgbox "There are incomplete...."?

HTH,
Bernie
MS Excel MVP

 

[Sandy]

Yes I get the message box, just doesn't want to goto cell D109. I am
beginning to think it is a piece of code elsewhere that is affecting the
outcome.

It's not the end of the world I can live without it doing the goto.

Thanks for all efforts.
Sandy

 

[Rick Rothstein \(MVP - VB\)]

Just to be sure you haven't accidentally left Excel in a non-responsive
state, execute these two lines in the Immediate window and then try some of
the suggestions again.

Application.ScreenUpdating = True
Application.EnableEvents = True

Rick

 

[Charlie]

Not to ask a dumb question, but when you drag the scroll bars does the sheet
actually scroll or does it just display that useless little yellow "ToolTip"
that shows the row or column? Then the sheet jumps to that row/column when
you release the scrollbar? My workbooks go into that state now and then and
I'm not sure what causes it, but it may be related.

 

[Sandy]

Rick
Did as you suggested - still no scroll to D109.
Events are definitely enabled.

sandy

 

[Sandy]

Charlie
The scroll bar scrolls smoothly and rotating the mouse wheel scrolls
smoothly too.

Sandy

 

[JLGWhiz]

Sandy, I tried both types of buttons just to be sure and they both scroll to
D109 as long as the sheet is active. They did not scroll it to the top of
the window, but they both scrolled it mid screen vertically. I noticed that
you have a message box displayed. Are you closing the message box before
clicking the button? If not, that could be your problem. Events are
suspended while the message box is displayed.

 

[Gord Dibben]

Charlie

Older versions of Excel............pre 2003, I believe but maybe pre
2002.............had the feature you describe.

Later versions do not have that.


Gord Dibben MS Excel MVP

 

[Sandy]

The Current Round worksheet is the active sheet - it is where the button is.
The message box is a result of clicking the button - if a condition is met.
It is then closed but the D109 selection, which comes after the message box,
does not work.
It's a puzzle!
Sandy