Vb Macro Coeds

click and get file names as list on seprate sheets just give file extension and file folder location

Auto Run [24/12/2001] (back to top) Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open() Msgbox "Hello ' I am in Module1' " End Sub

when u open this workbook this macro will run

for more excel help go to http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm#A

use the Auto Open method or the he workbook.

This code would be located in the module. However if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open. Private Sub Workbook_Open() Msgbox "Hello 'I am in this workbook'" End Sub

when u open this workbook this macro will run

he code must be in the workbook ays General) and select Workbook.

Current Cell Content [24/12/2001]

(back to top)

Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this ex message box is displayed. Replace this with a macro should you require another course of action.

Value Ab© 3 8/23/2009

Result Blank cell Text Formula Date

first select yellow cells

Sub ContentChk() If ActiveCell = "" Then MsgBox "Blank cell" 'replace this line with your macro Else If Application.IsText(ActiveCell) = True Then MsgBox "Text" 'replace this line with your macro Else If ActiveCell.HasFormula Then MsgBox "formula" 'replace this line with your macro Else End If If IsDate(ActiveCell.Value) = True Then MsgBox "date" 'replace this line with your macro Else End If End If End If End Sub

ourse of action. In this example a

Current Cell Position Sometimes we need to know the current cell position. This would do the trick. select any cell and know which cell is selected 6,1 7,1 8,1 9,1 10,1

6,2 7,2 8,2 9,2 10,2

Sub MyPosition() myRow = ActiveCell.Row myCol = ActiveCell.Column Msgbox myRow & "," & myCol End Sub

Click reset to enable select buttos sr 1 2 3 4 5

name nidhi sejal rucha tanvi anokhi

gujarati 54 48 54 58 65

hindi 43 46 58 49 44

english 48 54 58 46 58

maths 58 46 54 58 46

science social study 48 46 47 58 58 46 54 58 48 46

total 297 299 328 323 307

Select Select Select Select

Sub DeleteNames() Dim NameX As Name For Each NameX In Names ActiveWorkbook.Names(NameX.Name).Delete Next NameX End Sub

Select Select Select Select

Goto (a range) To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Sales". You may also use an alternative method ie the Range select method. Naming a range in excel is recommended rather than specifying an absolute cell reference. Sub GoHere() Application.Goto Reference:="Sales" End Sub i have use following code

Sub Goname() Application.Goto reference:="name" End Sub Sub gogujarati() Application.Goto reference:="gujarati" End Sub Sub gohindi() Application.Goto reference:="hindi" End Sub Sub goenglish() Application.Goto reference:="english" End Sub Sub gomaths() Range("maths").Select End Sub Sub goscience() Range("science").Select End Sub Sub gosocial_study()

OR

Range("Sales").Select

Sub goenglish() Application.Goto reference:="english" End Sub Sub gomaths() Range("maths").Select End Sub Sub goscience() Range("science").Select End Sub Sub gosocial_study() Range("social_study").Select End Sub Sub gototal() Range("total").Select End Sub

n use the Goto method. t called "Sales". You elect method. Naming a ing an absolute cell

nge("Sales").Select

Function MyName() As String MyName = ThisWorkbook.Name End Function Function MyFullName() As String MyFullName = ThisWorkbook.FullName End Function Function SheetName(rAnyCell) Application.Volatile SheetName = rAnyCell.Parent.Name End Function

Formula =MyName() =MyFullName() =CELL("filename") =sheetname(A1)

Sheet1

Result #VALUE! #VALUE! 'file:///opt/pdfcoke/conversion/tmp/scratch8/21572660.xls'#$Sheet15 #VALUE!