How to create drop down list with symbols in excel

 

If you want to create drop down list like below,

 

1. Click on data menu
2. Data validation - data validation - list
3. Allow -- Click on List button
4. On Source - Select the list of values that to be displayed on the list.
5. Click on OK

 

 

 

 

 If  you want to create drop down list like below but with sybmols,

 

1. Click on data menu
2. Data validation - data validation - list
3. Allow -- Click on List button
4. On Source - Select the list of values that to be displayed on the list.
5. Click on OK

If you want to get symbols then you need to type alt + 0245 (from numeric key pad)

Connect To SQL Server Using VBA

1. Open Excel  -- Developer Tab -- Select Visual Basic -- Double click on Sheet1 (or require sheet)

Tools - Reference -  Microsoft ActiveX Data Objects 2.6 Library

Sub FetchFromSQLServer()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim constring As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
constring = "provider=SQLOLEDB.1;Data Source=SQLServer2005,1433;Initial Catalog=kalyandb;User id='user1';password='admin"
con.Open constring

rs.ActiveConnection = con
rs.Open "Select * from Colors"
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

Insert Formula in excel cell using VBA

Below syntax is used to insert formula into excel sheet

ActiveSheet.Cells(f, 6).Formula = "=DATE(YEAR(B2),MONTH(B2),DAY(B2))"

Range(c2).formula = "=A1 + B1"

for i = 1 to 10 step 1
           Range(c2).formula =  "=A"& i & "+B" & i
next
 

Cell Pointer Missing in Excel 2007

To hide / enable cell pointer in excel 2007


1. Office Button / File -- Options
2. Select Advanced
3. Check the option as per the screen shot

 

How to Rollup days to months and years in Excel Pivot

If we have multiple dates, we can rollup them into months and years in excel pivot but we need to make sure it satisfies the below things

1. Format should be same for all dates to verify this, we need to apply filter for date column and then click on dropdown, generally excel will group rows by years and months, in the first image the few values are grouped to years few are not, that means there is an issue with the format, first correct it and verify, the second image doesn’t displays any extra records means you can go a head with 2nd step.

2. Insert – Pivot
3. Drag the date column into columns or rows section
4. Right click on any date --- select group – hold ctrl and select months and years
5. Move the column to columns section or rows section as per your requirement.

Loop Through Worksheets Using VBA

Sub loopThruSheetNames()


Dim ws As Worksheet
Dim i As Integer
i = 1
MsgBox "Total Sheets in current workbook are " & ThisWorkbook.Worksheets.Count
For Each ws In ThisWorkbook.Sheets
MsgBox "Name of Sheet " & i & " is " & ws.Name
i = i + 1
Next

End Sub

Working with worksheets using VBA

Sub WorkSheetsDemo()

Sheets.Add 'It adds a new worksheet before the active sheet
'To add worksheet at last please use the below syntax
'Sheets.Add After:=Worksheets(Sheets.Count)
'To add a worksheet at required position, we can specify name of the worksheet
Sheets.Add After:=Worksheets("Sheet2")

'To Rename a worksheet
Sheets("sheet9").Name = "MySampleSheet"

End Sub

Example to Read Cell Values using VBA

Sub getCellValues() 'Example to Read Cell Values
Dim value As Integer
Worksheets("sheet1").Range("N6").Activate
value = ActiveCell.value
MsgBox value
Worksheets("sheet1").Range("P4").Activate
value = ActiveCell.value
MsgBox value
End Sub

How to Place a value in required Cell using VBA

Sub printCellValues() 'How to Place a value in Required Cell in WorkSheet using VBA

Worksheets("sheet1").Range("E7").value = "kalyan"
End Sub

Worksheets("SheetName").Range("CellAddress").Value  = Required Value

What is VBA

Visual basic for Applications is a  Event Driven programming language from Microsoft. In a nutshell it is used to build user defined functions, for automation of applications, by using windows API and through dynamic link libraries (dll). Using Microsoft Excel we can develop macro programming using VBA, VBA coding constructs are similar to Visual Basic.