Software Categories 
Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks What's New at OzGrid
We received our first quarterly statistics from our book,Excel Hacks - 100 Industrial Strength Tips and Tools this week. The book is selling very well with sales so far standing at around 6000 copies. Hopefully this is an indication of the books popularity and things will only continue to get better.
We have finally finished updating our Excel Level 2 lessons. These are now in place and in use by our course participants, along with the updated Level 1 course. Next month we will endeavour to work on the Level 3 course followed in the coming months by our two VBA for Excel courses.
The Training courses are being offered in two modes, as discount downloadable training and now via our new Training Forum.Click here : to see the details. If you are a Forum member, you are able to take the training in our new Training Forum at a discount.Click here : to do this and see our prices. Both Levels 1 and 2 are now available.
Until next month...
One questions that appears from time-to-time is, how can I stop users from viewing and scrolling to restricted areas of a Worksheet. This month I will show you a few ways this can be done. However, it's very important that you understand that Excel is NOT a secure Application and any security measures we use can be broken by a persistent user. For this reason, you should NEVER put data into Excel when it is imperative that nobody be able to view it.Limit Worksheet Scrolling
There are 2 ways we can stop users from scrolling to certain areas of any Worksheet. The first entails hiding Column and Rows, while the second requires a quick change to the ScrollArea Property of the Worksheet. Let's look at the hiding of Column and Rows first.
This method is very easy to put in place and is enough to stop most users from seeing hidden data. Let's say you only want the user to only scroll down to Row 20 and across to Column "J". To do this follow the steps below
At this point we have 2 choices that will make it hard (but far from impossible) for most users to unhide any hidden Columns and Rows.
The first is to go to Tools>Options -View and uncheck "Row & column headers" then click OK. As you can see, this will remove all Column and Row headers from the Worksheet making it hard for any user to unhide the Rows and/or Columns.
The second, which can be used in combination with the above method, it to apply Worksheet Protection via Tools>Protection>ProtectSheet. Since Excel 2000 we have been able to set the amount of sheet protection that can be applied. Make the choices that suit your needs, but be sure not to check "Format columns" and/or "Format rows" as this will mean users can unhide your hidden Rows and Columns.
The second way we will look at is to use the ScrollArea Property of the Worksheet. We can set this manually. Right click on the Sheet Name tab of the Worksheet you wish to restrict scrolling on and select "View Code". Now push F4 and you should see the Worksheets Property Window. Below the "Alphabetic" heading, click in the blank box to the immediate right of the "ScrollArea" Property. In here type: A1:J20 and push Enter. Now, click the top right X of the Window, to get back to Excel and you will note you cannot scroll past Row 20 and/or Column "J". Unfortunately, this setting doesn't stick when we save and close Excel. However, we can overcome this shortfall very easily via some relatively simple VBA code. Again, right click on the Sheet Name tab of the Worksheet you wish to restrict scrolling on and select "View Code". Now, in the Private Module of the Worksheet (white background) copy and paste the code below;
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = "A1:J20"
End Sub
Again click the top right X of the Window, to get back to Excel and as soon as you select any cell you will note you cannot scroll past Row 20 and/or Column "J". To reset the ScrollArea use;
Me.ScrollArea = ""
Application.OnTime FROM EXCEL HELP
Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
expression Required. An expression that returns an Application object.
EarliestTime Required Variant. The time when you want this procedure to be run.
Procedure Required String. The name of the procedure to be run.
LatestTime Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wait until the procedure can be run.
Schedule Optional Variant. True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.
END OF EXCEL HELP
We can use the OnTime Method to achieve the automatically running of Excel Macros. Let's suppose we have a macro that we wish to Run each day at 15:00 (3:00 PM). The first problem will be how to kick-off the OnTime Method. This we can do via the Workbook Open Event. The fastest way to get to the Private Module of the Workbook Object (ThisWorkbook) is to right click on the Excel icon next to "File" and select "View Code"
Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "MyMacro"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime TimeValue("15:00:00"), "MyMacro", , False
End Sub
Where MyMacro is the name of the macro you wish to run and resides in a Standard Module and has the OnTime Method again like below;
Sub MyMacro()
Application.OnTime TimeValue("15:00:00"), "MyMacro"
'YOUR CODE
End Sub
This will run the Procedure MyMacro at 15:00 each day. The use of the Workbook_BeforeClose Event is to turn off the OnTime event. If this is omitted, and we close the Workbook before 15:00:00, the Workbook will automatically open itself at 15:00:00 so that MyMacro can Run. So, if this is desired, remove theWorkbook_BeforeClose Event.
Let's now suppose you want to run this macro (MyMacro) at 15 minute intervals after opening your Workbook. Again we will kick it off as soon as the Workbook Opens so right click on the Excel icon next to "File" and select "View Code". In here put;
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"
End Sub
Now in any Standard Module (Insert>Module) place this;
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:15:00")
Application.OnTime dTime, "MyMacro"
'YOUR CODE
End Sub
Important Point
It is important to note how we pass the time of 15 minutes to the Public Variable dTime. This is so we can have the OnTime Method cancelled in the Workbook_BeforeClose Event by setting the optional Schedule argument set to False. If we didn't pass the time to a variable Excel would not know which OnTime Method to cancel as Now + TimeValue("00:15:00")is NOT static, but is when passed to a variable. If we didn't set the optional Schedule argument set to False the Workbook would automatically open every 15 minutes after closing it and run MyMacro
ADVERTISEMENTS
Artificial neural network software for stock markets!
MAIN SOFTWARE CATEGORIES