Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Scroll Area
In response to a previous question of mine on Scroll increment, Dave Peterson referred me to Debra Dalgleish's site http://contextures.com/xlfaqApp.html#Unused . I was able to get my scroll increment to behave properly with the code: LN = Sheets("Comp").Range("B11") + 10 Set rng1 = Cells(1, 1) Set rng2 = Cells(LN, 19) Set rng = Range(rng1, rng2) Sheets("Lists").ScrollArea = rng.Address A = LN & ":50000" Sheets("Lists").Rows(A).Locked = False Sheets("Lists").Range(Cells(LN, 1), Cells(50000, 1)).EntireRow.Delete However, the result is not really dynamic, since one has to shut down and restart Excel to have the new scroll range to take effect, which Debra's site acknowledges for "older versions of Excel." I am using Excel 2003. Is there a way to defeat the need to restart Excel? Regards All, Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Scroll Area
I have Excel 2003 and i don't need to reopen the spreadsheet I use A1 and A2 on sheet2 to define the upper left and bottom right cells Sub setscroll() Dim myrange As String myrange = Sheet2.Range("A1") & ":" & Sheet2.Range("A2") Sheet1.ScrollArea = myrange End Sub when I change the values in A1 and A2 and run my code, the scroll area in Sheet1 is changed. I've never heard of having to restart before. "jswalsh33" wrote in message ... In response to a previous question of mine on Scroll increment, Dave Peterson referred me to Debra Dalgleish's site http://contextures.com/xlfaqApp.html#Unused . I was able to get my scroll increment to behave properly with the code: LN = Sheets("Comp").Range("B11") + 10 Set rng1 = Cells(1, 1) Set rng2 = Cells(LN, 19) Set rng = Range(rng1, rng2) Sheets("Lists").ScrollArea = rng.Address A = LN & ":50000" Sheets("Lists").Rows(A).Locked = False Sheets("Lists").Range(Cells(LN, 1), Cells(50000, 1)).EntireRow.Delete However, the result is not really dynamic, since one has to shut down and restart Excel to have the new scroll range to take effect, which Debra's site acknowledges for "older versions of Excel." I am using Excel 2003. Is there a way to defeat the need to restart Excel? Regards All, Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Scroll Area
Patrick, I am sorry, I was not specific enough in specifying my problem. You are correct, the scroll area does change without restarting Excel. My problem is the increment of the scroll bar. My scroll areas are widely different vertically, from about 50 rows to 2500 rows. When I run the 2500 row version, the vertical scroll bar becomes very small and the number of rows when incremented on moving the bar becomes very large. When I switch back to a 50 row version the bar remains very small and the increment very large. If I restart Excel and select the 50 row version, the scroll bar becomes large and the increments small. This is the problem discussed in Debra's site. I was looking for a way around restarting Excel other than making separate worksheets of the two versions. Thanks for your help. Jim "Patrick Molloy" wrote: I have Excel 2003 and i don't need to reopen the spreadsheet I use A1 and A2 on sheet2 to define the upper left and bottom right cells Sub setscroll() Dim myrange As String myrange = Sheet2.Range("A1") & ":" & Sheet2.Range("A2") Sheet1.ScrollArea = myrange End Sub when I change the values in A1 and A2 and run my code, the scroll area in Sheet1 is changed. I've never heard of having to restart before. "jswalsh33" wrote in message ... In response to a previous question of mine on Scroll increment, Dave Peterson referred me to Debra Dalgleish's site http://contextures.com/xlfaqApp.html#Unused . I was able to get my scroll increment to behave properly with the code: LN = Sheets("Comp").Range("B11") + 10 Set rng1 = Cells(1, 1) Set rng2 = Cells(LN, 19) Set rng = Range(rng1, rng2) Sheets("Lists").ScrollArea = rng.Address A = LN & ":50000" Sheets("Lists").Rows(A).Locked = False Sheets("Lists").Range(Cells(LN, 1), Cells(50000, 1)).EntireRow.Delete However, the result is not really dynamic, since one has to shut down and restart Excel to have the new scroll range to take effect, which Debra's site acknowledges for "older versions of Excel." I am using Excel 2003. Is there a way to defeat the need to restart Excel? Regards All, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scroll Area | Excel Programming | |||
Dynamic scroll area | Excel Programming | |||
Scroll Area | Excel Discussion (Misc queries) | |||
Scroll Area | Excel Programming | |||
how to set scroll area | Excel Programming |