VBA Scrolling ?

Question

I have a number of sheets in a workbook.
Each sheet has various rows of data. I have a For..Next loop to open each sheet.
Each sheet must be analyzed for zero balances in COL M.
A nested Loop controls the number of times to go "down" the Col. If the cell >0, then the loop continues "down" CoL M.
The available number of rows may not contain data and at a point the next sheet is opened.

The starting Cell is Selected and it's value returned. If the value = 0, Then, I must Scroll to Left and test the cell for data.

After the first starting cell, when scrolling is done, "down" and or "left", I cannot get the value of those cells.

The Cursor is not there so I cannot Activate and/or Select the cell for testing.

(ClearContents), is performed based on 0 value in Col M
and >0 value in Left cell. Other non-contiguous cells are then also cleared of contents, which means that I must have access to those cells, (Activated and/or Selected).

How can I get values in those?

Is there a way to record the cell address when arrived at by scrolling?
Old Lotus 123 used to allow an address to be saved in a Name, like "Address", and one could use "Address" just like "A5" to act upon the current "Address".

Answer 1

You do not need to select the cells to test its values. Try to use Cells(x,y).value, where:

x - row number;
y - column number.

For instance, if your active sheet is 'Plan1' and you want to obtain the value of cell "M5" on the sheet 'Plan2' you must write:

Plan2.Cells(13,5).value

or better:

yourVariable = Plan2.Cells(13,5).value


Good programming!

Nelson

Visit my site!!!
http://www1.webng.com/nbittencourt/index_e.htm

Answer 2

The workbook ScrollEvents.xls contains a class module called CDetectScroll that subclasses the Active Window in Excel and intercepts the WM_VSCROLL message, which occurs when the user scrolls vertically, and the WM_HSCROLL message, which occurs when the user scrolls horizontally. While it is technically possible to write the subclassing code itself in VBA, that isn't really a feasible solution. VBA simply isn't fast enough to cope with the flood of messages sent by Windows. Instead, the ScrollEvents project uses a third-party subclassing component called SSubTimer6, implemented in a DLL file called SSubTmr6.dll. This DLL is available for free on the vbAccelerator web site. You can download the zip file here, which contains the DLL and some VB6 example code, and read much more about how it works here. See this page for information about downloading and installing SSubTmr6 on your PC and referencing it in your VBA project. This DLL is required in order to use the code in the CDetectScroll class.

CDetectScroll is always working with the ActiveWindow and only with scrolling initiated by the user by clicking on a scrollbar. It will not respond to scrolling caused by VBA code.

The CDetectScroll class will raise any of 12 events, depending on how the window was scrolled
ScrollLineUp
ScrollLineDown

ScrollPageDown
ScrollPageUp

ScrollLeft
ScrollRight

ScrollLineLeft
ScrollLineRight

ScrollPageLeft
ScrollPageRight
ScrollEndHorizontalScroll
ScrollEndVerticalScroll
Each of these events passes a reference to the top-left cell in the VisibleRange of the window and a reference to the window being scrolled. To use CDetectScroll and its events, you must declare a variable WithEvents of the class CDetectScroll in an object module, such as a separate class module (as it is in the ScrollEvents.xls workbook), the ThisWorkbook module, a Userform's code module, or one of the worksheet's code modules. You declare the variable as follows

This solution changes the presentation itself, so the picture's placement on the slide will be modified. Therefore the presentation will give you the option of saving the updated presentation when you close it. If you save it, the picture will begin next time, where it finished this time (and therefore, not need to move at all). If you do not save it, than all changes will be lost, not just the ones to this picture.



2) The only speed control is by adjusting the amount the .Left value is altered by. If you change that number from 1 to 5, the picture will move 5 times faster, but will still take the same number of "steps" so the motion may become jerky.



3) VBA does not run from the viewer, or on systems with macro security set to high.



Conclusion:

If you will be running the presentation, on a machine you can set-up, and remember to restore the picture to the original position, this is an excellent option



http://www.softpedia.com/downloadTag/VBA

Answer 3

i won't mirror the habit you describe while entering into documents interior the final row of a worksheet. Is it accessible there is already a macro putting the scroll property to scroll up a a million/2 website?