Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Scrolling Too Fast

Fellow Forum Members,
Is there a back door way to tweak the scrolling in Excel 2003? The dislike I have with the scrollbar is that the scrolling is way too fast when I have 4,000 rows of data. But when I have 100 rows of data the scrolling is just right. I realise that Excel is designed to work like this, but is there some way to tweak the scrolling so that when I have 4000 rows of data the scrolling performs as though I have 100 rows instead? Any suggestions will be greatly appreciated.


Post your answer or comment

comments powered by Disqus
The scrollbar moves about an inch and the rows scroll about 3500 just in one
sheet. Is there a way to adjust or fix that?

When selecting a range of cells, and you have to begin scrolling off the
bottom on the screen:

At first the scrolling is painfully too slow to be usable. Then almost
instantly, the auto-scrolling to too fact, that you've scrolled past the end
of your spreadsheet by thousands of rows.

Everyone has the same problem, ping-ponging around as they try to reach
their target cell. Eventually you have to just sit there in "slow"
auto-scroll mode, holding down the mouse still, waiting for it to scroll into
view.

Tweak the scroll speed to what it should be - and i think everyone would
have the same "sense" of what is too fast, or too slow. As an example:
Internet Explorer's second fastest scroll speed when in
"middle-mouse-button-down-scrolling" is the top speed Excel should have.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hello. I have a macro that scrolls to certain columns. It works fine as long as some of the columns are not hidden. Sometimes it is neccessary for columns to be hidden. When some columns are hidden, it scrolls too far. It appears that my present macro doesn't count the columns which are hidden.

....
ActiveWindow.ScrollColumn = 50
....

When I check the column # with =column(), the column # returned is 120 (there's lots of hidden columns). Is there another way to do this so it scrolls to the right column? I need something that will work whether the columns are hidden or not.

Much appreciated,
JCMus

When selecting a range of cells, and you have to begin scrolling off the
bottom on the screen:

At first the scrolling is painfully too slow to be usable. Then almost
instantly, the auto-scrolling to too fact, that you've scrolled past the end
of your spreadsheet by thousands of rows.

Everyone has the same problem, ping-ponging around as they try to reach
their target cell. Eventually you have to just sit there in "slow"
auto-scroll mode, holding down the mouse still, waiting for it to scroll into
view.

Tweak the scroll speed to what it should be - and i think everyone would
have the same "sense" of what is too fast, or too slow. As an example:
Internet Explorer's second fastest scroll speed when in
"middle-mouse-button-down-scrolling" is the top speed Excel should have.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

When I select a number of rows by click and drag method, the rows scroll by
too fast to control. Is there a way to slow down this scrolling?

Seems like a post only stays on page 1 (where it's most visible) for about 15-25 minutes. Within an hour you're on page 3 or 4, where you'll never be noticed if you haven't been already!

Can't we open a few more subtopic boards. Rather than "All Excel/VBA questions", why not at least:

1) General Excel questions
2) Excel Macro and VBA questions

Or maybe,
1) Excel Beginner questions
2) Excel Advanced
3) General Excel
4) Macros and VBA for Excel

Or, something! Too many posts on one board.

Anyone else agree?

--------------------------------------------------------------------------------

I created a new workbook, very small at 85kb, and it scrolls up/down and left/right very slowly almost jerky. I don’t have this issue with any of my other spreadsheets. Can anyone identify what the issue might be?

1. Save the whales. Collect the whole set.
2. A day without sunshine is, like, night.
3. On the other hand, you have different fingers.
4. I just got lost in thought. It was unfamiliar territory.
5. 42.7 percent of all statistics are made up on the spot.
6. 99 percent of lawyers give the rest a bad name.
7. I feel like I'm diagonally parked in a parallel universe.
8. You have the right to remain silent. Anything you say will be
misquoted, then used against you.
9. I wonder how much deeper the ocean would be without sponges.
10. Honk if you love peace and quiet.
11. Remember half the people you know are below average.
12. Despite the cost of living, have you noticed how popular it remains?
13. Nothing is foolproof to a talented fool.
14. Atheism is a non-prophet organization.
15. He who laughs last thinks slowest.
16. Depression is merely anger without enthusiasm.
17. Eagles may soar, but weasels don't get sucked into jet engines.
18. The early bird may get the worm, but the second mouse gets the cheese.
19. I drive way too fast to worry about cholesterol.
20. I intend to live forever - so far so good.
21. Borrow money from a pessimist - they don't expect it back.
22. If Barbie is so popular, why do you have to buy her friends?
23. Mind like a steel trap - rusty and illegal in 37 states.
24. Quantum mechanics: The dreams stuff is made of.
25. The only substitute for good manners is fast reflexes.
26. Support bacteria - they're the only culture some people have.
27. When everything's coming your way, you're in the wrong lane and going the wrong way.
28. If at first you don't succeed, destroy all evidence that you tried.
29. A conclusion is the place where you got tired of thinking.
30. Experience is something you don't get until just after you need it.
31. For every action there is an equal and opposite criticism.
32. Bills travel through the mail at twice the speed of checks
33. Never do card tricks for the group you play poker with.
34. No one is listening until you make a mistake.
35. Success always occurs in private and failure in full view.
36. The colder the x-ray table the more of your body is required on it.
37. The hardness of butter is directly proportional to the softness of the bread.
38. The severity of the itch is inversely proportional to the ability to reach it.
9. To steal ideas from one person is plagiarism; to steal from many is research.
40. To succeed in politics, it is often necessary to rise above your principles.
41. Monday is an awful way to spend 1/7th of your life.
42. You never really learn to swear until you learn to drive.
43. Two wrongs are only the beginning.
44. The problem with the gene pool is that there is no lifeguard.
45. The sooner you fall behind the more time you'll have to catch up.
46. A clear conscience is usually the sign of a bad memory.
47. Change is inevitable except from vending machines.
48. Get a new car for your spouse - it'll be a great trade!
49. Plan to be spontaneous - tomorrow.
50. Always try to be modest and be proud of it!
51. If you think nobody cares, try missing a couple of payments.
52. How many of you believe in telekinesis? Raise my hand...
53. Love may be blind but marriage is a real eye-opener.
54. If at first you don't succeed, then skydiving isn't for you.

Hi all

I have a macro that sets two print ranges and prints them. If I step through the macro or run it from the code editor it works fine. If I run the macro from the menu or from a button it sets the print ranges but doesn't print. It seems to run too quickly. I have tried slowing the macro down but to no avail. Can anyone help?

Thanks
Regards
Liz

Macro:

Sub PrintSearch()

ActiveSheet.PageSetup.PrintArea = "$a$1:$b$50"
ActiveSheet.PrintOut Copies:=1, Collate:=True
ActiveSheet.PageSetup.PrintArea = "$c$1:$f$50"
ActiveSheet.PrintOut Copies:=1, Collate:=True

End Sub

Slowed down version (in theory):

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub PrintSearch()

ActiveSheet.PageSetup.PrintArea = "$a$1:$b$50"
Sleep 2500

ActiveSheet.PrintOut Copies:=1, Collate:=True
Sleep 2500

ActiveSheet.PageSetup.PrintArea = "$c$1:$f$50"
Sleep 2500

ActiveSheet.PrintOut Copies:=1, Collate:=True

End Sub

I have an excel workbook with comments on all items in column A.
I have a screen split between column A & B so that I can scroll through the
columns keeping column A visible.
Unfortunately when I scroll too far to the right the comments no longer are
visible.
(ie When I scroll to column N I can still see column A, but even with the
mouse on column A the comments are not visible, until I scroll back to the
left)

I am looking for a VBA macro that I can use to fix this problem.
I suspect it will use the .scrollcolumn property but frankly I am stuck.

I've got an annoyance when using Excel. Sometimes when I attempt to
select a series or rows that extend beyond the size of the screen, my
machine will scroll so fast when I reach the end of the viewed area
that it goes well beyond where I want it to stop. Is there a simple
way to deal with this?

Russ

I'm a macro newbie....so please forgive me if this question is really elementary!

I created a pivot table while recording it in a macro. When I tried to run the macro after I created it, I get a run time error '1004' that says AddFields method of PivotTable class failed. When it brings up the pivot table worksheet, not all of the fields were showing. Is it possible that the macro runs too fast? The first data field that is to be used in the pivot table isn't listed, which is why I wonder if it's running too quick. If so, is there anything I can to do slow it down? Appreciate any suggestions!!

Hello again!

My previous thread went away too far too fast. The problem was solved, but I think the code could be written more efficiently:

http://www.ozgrid.com/forum/showthread.php?t=36719

Below is a great code if you have only one place (a1) for input and one (f1) for total.


	VB:
	
 Button_Click() 
     'Put in a value into a1, press the button, the value pops up in f1'
    Range("F1").Value = CInt(Range("A1").Value) + CInt(Range("F1").Value) 
     
    Range("A1").Select 
    Range("A1").Clear 
     'ready for another input'
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


However, I have 6 of those A-F pairs (A1:A6 and F1:F6). In that case I must write the code 6 times and use 6 buttons...

I wonder if there is a way to re-write the code so that it applies to every pair, thereby using only one button for update.

My best

/Horpe

I have data that has to be massaged and the data isn't very consistent. While I work out all the "exceptions to the rules", I put in an 'ON ERROR GOTO label' to just stuff some asterisks in a cell so I know to check this data.

If I step through the code, the ON ERROR works flawlessly. But if I RUN the macro, after a few hundred lines, I'll hit an error and the code stops as though I DON'T have an ON ERROR command. If I stop the macro and step through on that very same activecell, it works fine. Its as though the macro runs too fast and skips the ON ERROR accidentally! Does anyone have any suggestions? (See below. Thanks.


	VB:
	
 
Sub Match_records() 
    Dim Vmiddle 
    Dim Vfront 
    Dim Vback 
     
VTop: 
    Do Until IsEmpty(ActiveCell) 
        If InStr(ActiveCell, "v. (") > 0 And InStr(ActiveCell, " EBC ") = 0 Then 
            On Error Goto Verror 
            Vmiddle = Left(ActiveCell, InStr(ActiveCell, ";") - 1) 
            Vfront = Mid(ActiveCell, InStr(ActiveCell, ";") + 2, ((InStr(ActiveCell, "(") - 1) - (InStr(ActiveCell, ";") +
1))) 
            Vback = Mid(ActiveCell, InStr(ActiveCell, "(") - 1, 50) 
            ActiveCell.Offset(0, 1) = Vfront & Vmiddle & Vback 
        End If 
         
        ActiveCell.Offset(1, 0).Activate 
         
    Loop 
    Exit Sub 
     
Verror: 
    ActiveCell.Offset(0, 1) = "***" 
    ActiveCell.Offset(1, 0).Activate 
    Goto VTop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I'm trying to write some VB to draw a line shape and then move it across the screen. I have managed to draw the line and have used the following code to move it:


	VB:
	
 linetest = ActiveSheet.Shapes(1) 
For iCount=1 To 100 
    linetest.Left = linetest.Left + 1 
    linetest.Top = linetest.Top + 1 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problems I now have are the following:

i) I can only actually see the line move if I run the code using the debugger and step through the loop. If I just run the code then I only see the end position of the line (note, this is not an issue with the calculation being too fast for Excel to display the line. I tried the code with a delay loop in it and it made no difference). I can get it to work by calculating the active sheet for each iteration of the loop, but this means I have no control over the speed of the movement; incidentally it moves very slowly). Can anyone suggest a fix?

ii) This seems a poor way to move the line. Is there anyway to move the start and end node of the line rather than using the left, top, width and height properties (i.e. can I get the old coordinates of the points and set them to different numbers)?

Thanks

I have a worksheet containing three comboboxes (amongst other things), and as well as having an option to run that worksheet and the comboboxes on a 'single' basis, I have another macro that cycles through the worksheet a number of times with different combobox values (and gives me different output as per the changed comboboxes).

When I run the macro on a 'single' basis all is good, but when I run the macro the values I see in the comboboxes do not visibly change even though the output has changed to match the comboboxes.

My first step was to put a 'stop' in the macro to see where it was going wrong....but when I did this the values do visibly changed. Is my PC going too fast, or...?

I have made a spreedsheet that displays data gathered by an add-in called 'Uniformance' (Written by Honeywell). Uniformance receives information from a scan station that monitors process equipment. Everything works fine but I can't work out a way to have the data displayed in excel in real time. If I want to see the latest data I have to press F9 to get everything to update and recalculate.

I found a clock on the net that someone had written to display the current time in excel and if I build my spreadsheet around this clock and run the clock I find that my data updates automatically as I would like. The only problem here is that the clock updates too fast and If I have heaps of formulas and graphs the spread sheet flickers and boggs down. I only need the sheet to update aprox. every ten seconds.

Can anyone help? I know very little about VBA as I have only started playing with Excel this year and the only knowledge I have is from the Excel help and the net.

Thanks, Liam

Hello,

Me again, almost have my Excel VBA problems solved, and I've learned a number of things about Excel VBA (though I'm still much more comfortable in Access VBA, which I have been using for years)... Thanks for all those who have helped my ignorance the past few days, much appreciated, and I wish you all a Merry Christmas... (Though, I may stick around occassionally to learn more from this excellent message board.)

I have a problem, where an automated procedure to refresh data contained in a template is working, but it only seems to properly save the data for the last file loaded (data refreshed from).

The code (please see below) works as follows:

1) filesearch method locates specified data file, then calls function
2) function activates sheet containing template
3) function uses SendKeys command to initiate data refresh for template
4) function uses SendKeys command to tell data refresh which file to load in (based upon filesearch method in #1)
5) function then tells sheet to "Save As" spreadsheet using filename from filesearch (#1 above)
6) filesearch initiates again to locate next data file, iterating until all data files have been located, then stops

Here's the code:


	VB:
	
 Open_And_Siphon_UPC_Data_1() 
    Dim vaFileName As Variant 
    Dim MyDir As String 
     
    MyDir = ThisWorkbook.Path 
     
     'ProcessData "hello" 'to call SUBroutine for testing
     
    With Application.FileSearch 
        .NewSearch 
        .LookIn = MyDir 
        .SearchSubFolders = False 
        .Filename = ".upc" 
        If .Execute > 0 Then 
            Application.ScreenUpdating = False 
            For Each vaFileName In .FoundFiles 
                ProcessData vaFileName 
            Next 
        Else 
            MsgBox "There were no files found." 
        End If 
        Application.ScreenUpdating = True 
    End With 
     
End Sub 
 
Sub ProcessData(ByVal Fname As String) 
     
    Dim saveName As String 
     
     'NOTE: I want to cycle through the files using the sheet in this workbook
     '      titled "VBA Program" (Sheet1), while on the second sheet titled
     '      "UPC_Template" (Sheet2) I want to refresh the data using the file
     '      names discovered from above in "Open_And_Siphon_UPC_Data_1()"
     
     'Activate the UPC_Template worksheet
    Worksheets("UPC_Template").Activate 
     
     'Send keys CTRL+R as this is what I recorded the macro to use as a shortcut,
     'this will activate the recorded macro to initiate the Data Refresh...
    Application.SendKeys ("^r") 'calling macro with CRTL and R
    Application.SendKeys (Fname) 'variable from above
    Application.SendKeys ("~") 'same as "{ENTER}"
     
    saveName = Fname & ".xls" 
    ActiveSheet.SaveAs Filename:=saveName 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code is doing as I desire, but it is not saving the actual data loaded into the template, except for the last save (then the data is there).

I wondered if the commands are too fast, meaning that the data is not totally loaded in before the SaveAs command initiates - thus saving blanks except on the final SaveAs.

Any ideas??? Much appreciated...

Colin

Mods :
Apologies. I was too fast on the trigger
Can you please modify subjct to "Calculating Results & Scores"
Thanks
-marc
=========================================
Hi all -

Apologies for being so forward
But I have no idea what is going on with attached
Can anyone give me some pointers

I figured out how slope and intercept were calculated
The score is actual score received
Combined score is average of scores received

The remainder I so far am unable to resolve

Appreciate any help

Thanks
-marc

Hi all,

Currently everyday we enter a list of estimated transactions into our system - the total value isn't finalised until the next business day - and amend them manually the next day. I'm looking to automate the process by creating a sequnce number for each transaction that yhe system will be able to pick up and delete the estimates and then use some other code to enter the correct values.

To create the sequnce numbers I was just going to use and IF/NOW function to enter a sequence number equal to the date and time right down to the second. My problem is that if I create a macro for this it will run too fast that all transactions will end up with the same number. Is there a way to make the code wait a second before creating the next number?

cheers,

Hello.

I hope my topic title wasn't too confusing. I'll try to explain it as best I can here. My motive of trying to achieve this is to get a tidier spreadsheet.

I have a workbook consisting of 4 sheets, three of which are basically the same. They each consists of a code (range A), description (range B) and a quantity (range C). In range D I have added a counter button which I found in this (thanks a lot for that) thread.

Each time I finish a case I press the button and it adds 1 to the quantity for the respective code. At the end of the day I have to plot the results in a different spreadsheet. Since I have a total of 132 codes I've spread them over several sheets so I don't have to scroll too much but it's still a tiny bit of "investigation" work to locate them all at the end of the day. To combat this I made a fourth sheet called summary where I've copied all the codes (all 132 of them) and I'm using the code =SheetX!CY to copy the respective number from the different sheets to this one. On any given day I use a maximum of 10-15 codes which makes the summary sheet a whole lot more cluttered than it really needs to be.

What I would love to be able to do is that if and only if eg. cell C4 sheet 1 (plus 2 and 3) contains a number (or a number different from 0) it would copy cell A4, B4 and C4 from sheet 1 (+ 2 and 3) to the first empty row in sheet 4 (or summary as I've called it). If cell C4 is empty or contains the value 0 dont do anything. The same goes for C5 (copy A5, B5 and C5) and so forth. This way my summary sheet will only consists of a few rows displaying which codes I've used that day.

Is this feasible or am I causing you guys a lot of headache for asking this?

Hi. I have this problem I wrote a macro quite long . Sometimes, it doesn´t do what it suppose to do. If i run it again, using f8, it runs ok. It seams that sometimes it runs too fast and do not do all the instructions. How can i solve this problem?

Thanks.

** PLEASE DO NOT REPLY TO THIS MESSAGE **
If you do have queries they will be better in separate messages dealing with just the bit you need.

Keywords : API CALLS, SENDKEYS, PRINT SCREEN, EXTERNAL APPLICATIONS

I am currently using CD writing software which has a dialog for track settings. The equaliser settings are visual, so would be difficult to record manually. With up to 30 tracks to a CD I need an automatic method of copying screen shots into Excel.

To make programming easier at this early stage I use Windows Calculator as the external application. I have also put more subroutines than usual to keep the thing clearer - and knowing I might not need some of them later.

The code below doesn't do much with Calculator, but does include almost everything needed (perhaps more) for a project like this - and does work on my PC. A big problem with running external applications is that the macro runs too fast, and does not allow time for things to happen on screen. eg when manipulating a menu with SendKeys. To overcome this there are a couple of ways of putting delays in the code. There is then the need to balance overall runtime with accuracy. Running such code on a server gives huge problems with variable speeds depending on "traffic" at the time.

It is not possible to do a screenshot using SendKeys. The API code for this does the same job by mimicing Alt + PrintScreen keypresses. Generally I have found the API method of mimicing keystrokes more reliable than SendKeys but it needs more work - such as by requiring separate KeyUp and KeyDown commands.

Just a mention - it is possible to mimic Mouse activity using API. Luckily it is not necessary here. If you think using keystrokes is complicated .......... !!!
Hope some of you find this useful.
Code:
'======================================================================
'- MACRO TO RUN PRINT SCREEN MULTIPLE TIMES ON AN EXTERNAL APPLICATION
'- AND PASTE PICTURES INTO A WORKSHEET
'- Needs a range named 'Gallery' in the active sheet - 1 cell per picture.
'- Check path to Calculator in code below
'- Brian Baulsom September 2005
'======================================================================
'- API to get window handle by using the name
Declare Function FindWindow Lib "user32.dll" _
   Alias "FindWindowA" (ByVal lpClassName As Any, _
    ByVal lpWindowName As Any) As Long
'-------------------------------------------------------------------------
'- API to bring window to top
Declare Function BringWindowToTop Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
'--------------------------------------------------------------------------
'- API to get handle of the active window (no need to know its name)
Declare Function M_GetActiveWindow Lib "user32" _
   Alias "GetActiveWindow" () As Long
'--------------------------------------------------------------------------
'- API to get window caption (my app. changes the dialog caption/name)
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
   (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" _
    (ByVal hWnd As Long) As Long
'---------------------------------------------------------------------------
'- API used to to print screen (mimics keystrokes). Cannot use SendKeys.
Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal _
    bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Const VK_KEYUP = &H2
Private Const VK_SNAPSHOT = &H2C
Private Const VK_MENU = &H12
'==========================================================================
'- general
Dim ExcelWindowName As String
Dim ExcelHandle As Long
Dim ws As Worksheet
Dim PictureGallery As Range
Dim WindowHandle As Long  ' Windows window ID
Dim WindowName As String  ' Window Caption (Case sensitive)
Dim PictureNumber As Integer
Dim PictureCell As Range
Dim PictureSet As ShapeRange
Dim rsp As Variant
'--------------------------------------------------------------------------
'- SendKeys
Dim EscapeKey As String
Dim AltKey As String
Dim CtrlKey As String
Dim ShiftKey As String
Dim TabKey As String
Dim EnterKey As String
Dim UpKey As String
'===========================================================================


'===========================================================================
'- MAIN ROUTINE
'===========================================================================
Sub MAIN()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    '- get Excel API info
    ExcelWindowName = Application.Caption
    ExcelHandle = M_GetActiveWindow()
    PictureNumber=1
    '----------------------------------------------------------------------
    '- need to know original window name of external application
    WindowName = "Calculator"
    Initialise
    ws.Range("A1").Select ' remove focus from button on sheet
    '======================================================================
    '- manipulate external application
    '======================================================================
    Get_Window  ' activate Calculator window
    '-------------------------------------------
    SendKeys EscapeKey, True ' clear total
    For p = 1 To 3
        SendKeys "2", True
        DoEvents
        SendKeys "{+}", True
        DoEvents
        Application.Wait Now + TimeValue("00:00:01")    ' WAIT 1 SECOND
        Print_Screen
    Next
    '=====================================================================
    '- finish - bring Excel to the top
    rsp = BringWindowToTop(ExcelHandle)
    Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    MsgBox ("Done")
End Sub
'== EOP ====================================================================

'===========================================================================
'- FUNCTION TO GET ACTIVE WINDOW CAPTION
'- ** can use VBA ActiveWindow.Caption instead for Excel ******
' This does not seem to work with Calculator  or some other applications
'===========================================================================
Private Function ActiveWindowCaption() As String
    Dim MyCaption As String
    Dim MyLen   As Long
    Dim hWnd As Long
    '-------------------------
    hWnd = M_GetActiveWindow()
    MyCaption = String(GetWindowTextLength(hWnd) + 1, Chr$(0))
    GetWindowText hWnd, MyCaption, Len(MyCaption)
    ActiveWindowCaption = MyCaption
End Function

'=====================================================================
'- INITIALISE
'=====================================================================
Private Sub Initialise()
    Set ws = ActiveSheet
    WindowHandle = 0
    '---------------------------------------
    '- SendKeys
    EscapeKey = "{ESCAPE}"
    AltKey = "%"
    CtrlKey = "^"
    ShiftKey = "+"
    TabKey = "{TAB}"
    EnterKey = "~"
    UpKey = "{UP}"
    '----------------------------------------------------------------
    Set PictureGallery = ws.Range("A1:H50")
    PictureGallery.Rows.RowHeight = 70
    PictureGallery.Columns.ColumnWidth = 15
    '---------------------------------------------------
    On Error Resume Next ' might be no existing pictures
    Set PictureSet = ws.Pictures.ShapeRange
    PictureNumber = PictureSet.Count + 1
End Sub

'=====================================================================
'- PRINT SCREEN & PASTE INTO THE WORKSHEET
'- events and SendKeys work slowly, so need lots of delays in the code
'- EDIT - Since adding DoEvents I have been able to comment some of them out
'=====================================================================
Private Sub Print_Screen()
    '- API print screen
    keybd_event VK_MENU, 0, 0, 0        ' Alt key down
    DoEvents
    keybd_event VK_SNAPSHOT, 0, 0, 0    ' PrintScreen key down
    DoEvents
    keybd_event VK_SNAPSHOT, 0, VK_KEYUP, 0
    DoEvents
    keybd_event VK_MENU, 0, VK_KEYUP, 0
    DoEvents
    'Application.Wait Now + TimeValue("00:00:01")    ' WAIT 1 SECOND
    '---------------------------------------------------------------
    '- paste picture to worksheet
    Set PictureCell = PictureGallery.Cells(PictureNumber)
    ws.Paste Destination:=PictureCell
    DoEvents
    'Application.Wait Now + TimeValue("00:00:01")    ' WAIT 1 SECOND
    Set PictureSet = ws.Pictures.ShapeRange
    '---------------------------------------------------------------
    '- format new picture
    With PictureSet(PictureNumber)
        .Top = PictureCell.Top
        .Left = PictureCell.Left
        .LockAspectRatio = msoFalse
        .Height = 70#
        .Width = 70#
        .Placement = xlFreeFloating
    End With
    'Application.Wait Now + TimeValue("00:00:01")
    '----------------------------------------------------------------
    PictureNumber = PictureNumber + 1
End Sub

'=====================================================================
'- GET WINDOW TO TOP
'- record the original handle because the application window caption changes
'=====================================================================
Private Sub Get_Window()
    '-----------------------------------------------------------------
    ' Initial check for open window
    If WindowHandle = 0 Then
        WindowHandle = FindWindow(CLng(0), WindowName)
    End If
    '------------------------------------------------------------------
    '- If handle is still zero window name was not found
    If WindowHandle = 0 Then
        RunCalculator
        'rsp = MsgBox("There is no window called " & WindowName, vbOKOnly + vbCritical, " NO WINDOW")
        WindowName = ActiveWindowCaption
        WindowHandle = M_GetActiveWindow()      ' get active window handle
    Else
        rsp = BringWindowToTop(WindowHandle)
    End If
End Sub


'====================================================================
'- RUN CALCULATOR
'====================================================================
Private Sub RunCalculator()
    x = Shell("C:Calc.exe", 1)
    WindowHandle = FindWindow(CLng(0), WindowName)
    rsp = BringWindowToTop(WindowHandle)
End Sub


sometimes when creating a spreadsheet I highlight too much or accidentally open up large areas of a sheet that I don't need and never want to see again. The scrolling bar is reduced to a line as it expects to have to scroll vast distances and therefore scrolls too quickly. It also increases the size of a document in KB. How can I fix this? I've even tried to cut or copy the sections I'm using but this becomes complicated as I have 14 different sheets all linked on a single document so you can imagine......

I'm sure there is a genius on here who knows how to fix this and I am sure there is a simple solution but I'm baffled. Please help....


No luck finding an answer? You could always try Google.