Monday, August 2, 2010

Protect Excel Cells in a worksheet

This post is regarding the query, that how can user protect some of the cells in a worksheet for editing.

Steps to do this as follow:


Default when you protect your worksheet it lock all the cells for editing, but in case if you want some of the cells can be edited by user then, for that you have to make that cells unlock. But how can we do that?

Answer is,

  • On the Format menu, click Cells, and then click the Protection tab.
  • Click to clear the Locked check box and click OK.

                               Or

                        Press Ctrl + 1

  • On the Tools menu, point to Protection, and then click Protect Sheet. Type a password if you want one, and then click OK.


Done !!!



NOTE: If you lock a cell and protect the worksheet, then you cannot type data into the cell, modify the data currently in the cell, or change other attributes of the cell (such as cell formatting).

Tuesday, July 13, 2010

Block unwanted Websites

Many people want to Block some websites that are harmful to there PC. Parents want to take care of there children by blocking porn websites and some offices blocks some special websites that may harm there network.

There are many software available on internet (Pay/Free) to block such websites.

So, is there a way to stop viewing unwanted web-pages without software? Answer is YES.

So, Let's see how can we do without software....

Steps:

1) Browse C:\WINDOWS\system32\drivers\etc
2) Find the file named "HOSTS"
3) Open it in notepad
4) Under "127.0.0.1 localhost"
   Add 127.0.0.2 www.sitename.com (Note: when ever you try to use this url it will divert to the given IP address.. and site won't be accessible...
5) Done!

You can add as many as rows in the host file to block the site... but there is still there could be a problem...


127.0.0.1 localhost
127.0.0.2 www.site1.com
127.0.0.3 www.site2.com
127.0.0.4 www.site3.com
etc...

Even you can add the site list in "Restricted Sites" under Internet Option -> Security.

Add Dynamic Value in Excel Shape Object

On Many forums and discussion page I have found people talk about adding value in shape object with VBA code. But I always try to avoid VBA unless and until, can't find solution with Excel inbuilt features....

So, here I am just going to say about how you can easily add dynamic Value of any cell in your Shape objects.

Step1: Draw a shape in excel.
Step2: Select shape, and click on Formula Bar.
Step3: Write Cell address in formula bar...

You are done!!!

Wednesday, July 7, 2010

Set your Image in your Folder

Hi, in many blogs I have seen this trick and same I am adding in my blog too.... so enjoy your folder with your images....

First of all, select ur desired folder and hit “ALT+ENTER” a properties dialog will pop up.

Then go to » Customize tab » Customize. There, choose any icon, click Apply and OK.

Now in the address bar of your folder type “\desktop.ini” before your folder’s path.

A file named “desktop.ini” will open in notepad.

Add following lines on it.

[ExtShellFolderViews]
{BE098140-A513-11D0-A3A4-00C04FD706EC}={BE098140-A513-11D0-A3A4-00C04FD706EC}
[{BE098140-A513-11D0-A3A4-00C04FD706EC}]
IconArea_Image=C:\path\folder\background.jpg



Save it!!!

Now Just do refresh, and it is ready to show image in your folder. (you can change the path of image as you want)


To remove the default icon chosen during the creation of the desktop.ini and restore the default folder icon, delete these lines of code from within the desktop.ini file:

[.ShellClassInfo]
IconFile=%SystemRoot%\system32\shell32.dll
IconIndex=(some number)

Tuesday, July 6, 2010

Beaware while coping sensitive data

Fact About Ctrl + C (the ‘copy’ command)

Ctrl+C (copy) we use it every day. But it could be not a very safe thing to do on Internet.
why ?

We do copy various data by Ctrl + C for copying & pasting else where.

This copied data is stored in clipboard and is accessible from the net by a combination of Javascripts and ASP. if u dont believe me, Just try this:

1) Copy any text by Ctrl + C

2) Click the Link: http://www.sourcecodesworld.com/special/clipboard.asp

3) You will see the text you copied was accessed by this web page.

So, don't keep sensitive data (like passwords, credit card numbers,PIN etc.) in the clipboard while surfing the web. It is
extremely easy to extract the text stored in the clipboard to steal your sensitive information.

Steps to Protect against clipboard hacking:

Go to internet options of explorer -_ tools menu -> Internet option -> Security Custom level

In settings – Select disable under allow past operations via script.

I HOPE THIS WILL BE HELPFUL FOR U GUYS..SO BE SAFE..

Monday, June 28, 2010

UDF (User Defile Function) in EXCEL

About User Defined Functions

Excel provides the user with a large collection of functions inbuilt, more than enough to satisfy the average user. Many more functions can be added by installing the various add-ins that are available on internet.

Most of the calculations can be easily done with the inbuilt functions provided by Microsoft,  but it isn't long before you find yourself wishing that there was a function that did a particular job, and you can't find anything suitable in the list. You need a UDF.

A UDF (User Defined Function) is simply a function that you create yourself with VBA. UDFs are often called "Custom Functions". A UDF can remain in a code module attached to a workbook, in which case it will always be available when that workbook is open. Alternatively you can create your own add-in containing one or more functions that you can install into Excel just like a commercial add-in.

UDFs can be accessed by code modules too. Often UDFs are created by developers to work solely within the code of a VBA procedure and the user is never aware of their existence.

Like any function, the UDF can be as simple or as complex as you want. Let's start with an easy one...

A function that will calculate Simple interest.

Open a new workbook and then open the Visual Basic Editor (Tools > Macro > Visual Basic Editor or ALT+F11).

You will need a module in which to write your function so choose Insert > Module. Into the empty module type: Function SimpleInterest and press ENTER.
The Visual Basic Editor completes the line for you and adds an End Function line as if you were creating a subroutine.
You will find it as follow:

Function SimpleInterest()

End Function
 For calculation of simple interest we need 3 values from user i.e. Principal Amount, Rate of Interest, and Number of period. So, we will add 3 parameter to the function, and calculation of the Simple Interest.

 
Function SimpleInterest(pAmt As Double, rate As Integer, period as Integer)
 Dim si as Double
 si = (pAMT * rate * period)/ 100                  ' (PRN/100)
 SimpleInterest = si                               ' return value 
End Function

Now we can test our function right away. Switch to the Excel window and enter figures for principal, rate of interest and period in separate cells. In a fourth cell enter your function as if it were one of the built-in ones. In this example cell A2 contains the Principal Amount (1000), cell B2 has Rate of Interest (12%) and cell C2 has period(24). In D2 I typed =SimpleInterest(A2,B2,C2) and the new function calculated the simple interest  (28.8)...









A function that will give Name of Weekday

There is a function in Excel that return me the number of Week, but I want name of the week not a number!!! So, let's create function that will show us name of week(like "Monday").
We can do this by doing manipulation the inbuilt function. But I don't it to do every time so I decided to create a Custom user define function, that will full fill my requirement.


So, Let's Start...

Function WeekDayName(InputDate As Date)
    Dim wDay As Integer    Dim wDayName As String
 
    wDay = Weekday(InputDate, vbSunday)         ' vbSunday is set as defaule start date
    Select Case wDay
        Case 1
            WeekDayName = "Sunday"
        Case 2
            WeekDayName = "Monday"
        Case 3
            WeekDayName = "Tuesday"
        Case 4
            WeekDayName = "Wednesday"
        Case 5
            WeekDayName = "Thursday"
        Case 6
            WeekDayName = "Friday"
        Case 7
            WeekDayName = "Saturday"
    End Select
End Function

I've called my function "WeekDayName" and it takes a single argument, which I call "InputDate" which (of course) has to be a date. Here's how it works...
  • The first line of the function declares a variable that I have called "WeekDayNumber" which will be an Integer (i.e. a whole number).
  • The next line of the function assigns a value to that variable using Excel's WEEKDAY function. The value will be a number between 1 and 7. Although the default is 1=Sunday, I've included it anyway for clarity.
  • Finally a Case Statement examines the value of the variable and returns the appropriate piece of text. 








Were do we find our custom function in Excel?
You can also find the functions listed in the Function Wizard (sometimes called the Paste Function tool). Use the wizard to insert a function in the normal way (Insert > Function).
Scroll down the list of function categories to find User Defined and select it to see a list of available UDFs...


Extract Flash Game from Excel with VBA

Open new Excel file and insert new ActiveX Button control past following code in it's Click function.

Private Sub CommandButton1_Click()
    Dim tmpFileName As String, FileNumber As Integer 
    Dim myFileId As Long 
    Dim myArr() As Byte 
    Dim i As Long 
    Dim MyFileLen As Long, myIndex As Long 
    Dim swfFileLen As Long 
    Dim swfArr() As Byte 
    tmpFileName = Application.GetOpenFilename("office File(*.doc;*.xls),*.doc;*.xls", , "Select Excel / Word File") 
    If tmpFileName = "False" Then Exit Sub 
    myFileId = FreeFile 
    Open tmpFileName For Binary As #myFileId 
    MyFileLen = LOF(myFileId) 
    ReDim myArr(MyFileLen - 1) 
    Get myFileId, , myArr() 
    Close myFileId 
    Application.ScreenUpdating = False 
    i = 0 
    Do While i < MyFileLen 
        If myArr(i) = &H46 Then 
            If myArr(i + 1) = &H57 And myArr(i + 2) = &H53 Then 
                swfFileLen = CLng(&H1000000) * myArr(i + 7) + CLng(&H10000) * myArr(i + 6) + _ 
                CLng(&H100) * myArr(i + 5) + myArr(i + 4) 
                ReDim swfArr(swfFileLen - 1) 
                For myIndex = 0 To swfFileLen - 1 
                    swfArr(myIndex) = myArr(i + myIndex) 
                Next myIndex 
                Exit Do 
            Else 
                i = i + 3 
            End If 
        Else 
            i = i + 1 
        End If 
    Loop 
    myFileId = FreeFile 
    tmpFileName = Left(tmpFileName, Len(tmpFileName) - 4) & ".swf" 
    Open tmpFileName For Binary As #myFileId 
    Put #myFileId, , swfArr 
    Close myFileId 
    MsgBox "SaveAs " & tmpFileName 
End Sub