| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll to selected cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 On Jul 24, 8:51*am, "Sandy" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
Sandy,
As long as Current Round is the activesheet: Application.Goto Sheets("Current Round").Range("D109"), True HTH, Bernie MS Excel MVP "Sandy" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 "Susan" wrote in message ... 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 On Jul 24, 8:51 am, "Sandy" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sandy, As long as Current Round is the activesheet: Application.Goto Sheets("Current Round").Range("D109"), True HTH, Bernie MS Excel MVP "Sandy" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
That doesn't work either I'm afraid.
"JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sandy, As long as Current Round is the activesheet: Application.Goto Sheets("Current Round").Range("D109"), True HTH, Bernie MS Excel MVP "Sandy" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Sandy, As long as Current Round is the activesheet: Application.Goto Sheets("Current Round").Range("D109"), True HTH, Bernie MS Excel MVP "Sandy" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 "Sandy" wrote in message ... That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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" wrote: That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
Rick
Did as you suggested - still no scroll to D109. Events are definitely enabled. sandy "Rick Rothstein (MVP - VB)" wrote in message ... 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 "Sandy" wrote in message ... That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
Charlie
The scroll bar scrolls smoothly and rotating the mouse wheel scrolls smoothly too. Sandy "Charlie" wrote in message ... 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" wrote: That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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. "Sandy" wrote: Charlie The scroll bar scrolls smoothly and rotating the mouse wheel scrolls smoothly too. Sandy "Charlie" wrote in message ... 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" wrote: That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 On Thu, 24 Jul 2008 09:15:02 -0700, Charlie wrote: 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" wrote: That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scroll to selected cell
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 "JLGWhiz" wrote in message ... 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. "Sandy" wrote: Charlie The scroll bar scrolls smoothly and rotating the mouse wheel scrolls smoothly too. Sandy "Charlie" wrote in message ... 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" wrote: That doesn't work either I'm afraid. "JLGWhiz" wrote in message ... 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Scroll Lock for selected multiple sheets | Excel Worksheet Functions | |||
| Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
| Adding a scroll bar to a cell to scroll its contents. | Excel Programming | |||
| scroll window to display selected cell in visible area? | Excel Programming | |||
| Macro to take selected cells times a selected cell | Excel Programming | |||
Linear Mode
