Navigation into Worksheets using Listbox

You need to add a ListBox in a UserForm and copy this two simple procedures:

Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ListBox1.AddItem sht.Name
Next sht
End Sub

Private Sub ListBox1_Click()
Sheets(ListBox1.ListIndex + 1).Activate
End Sub

Once you show the UserForm it will add the names of the sheets in the ListBox, then when you click on one of the sheets name in the ListBox it will select the corresponding sheet in the WorkBook. I hope it works

Change the worksheet name

Change the worksheet name by asking user

Private Sub Workbook_Open()

Dim name As String
name = InputBox("Sheet name, please?")
Sheets(1).Select
ActiveSheet.name = name
Range("A1").Value=name

End Sub

To split firstname,middle name & lastname

In C4 (first name) use:
=LEFT(B4,FIND(" ",B4)-1)

In D4 (middle name or initial) use
=IF(ISERR(FIND(" ",B4,1+FIND(" ",B4))),"",MID(B4,FIND(" ",B4)+1,FIND(" ",B4,1+FIND(" ",B4))-FIND(" ",B4)-1))

In E4 (last name) use
=IF(ISERR(FIND(" ",B4,1+FIND(" ",B4))),MID(B4,FIND(" ",B4)+1,LEN(B4)),MID(B4,1+FIND(" ",B4,1+FIND(" ",B4)),LEN(B4)))

Note:
a] This will put a blank in middle name if there are only first and last names.
b] It will not work if you have Jr. or II/III in the names and no middle initial.
You could extrapolate these formulas to handle it by looking at the number of blanks though.

insert data into Access Table from Excel

Bellow code lists two functions -
- First to insert data into Access Table from Excel
- Second to fetch data from the Access table to Excel (Sheet 2)

Sub InsertRecord()
Dim Com As New ADODB.Command
Set Com = New ADODB.Command

Dim ConStr As String, ComStr As String

ConStr = "Provider = Microsoft.jet.oledb.4.0; data source = H:\trial.mdb; Persist Security Info=False"
ComStr = "Insert into PersonalRecords(Name, Age, DOB) Values ('" + Range("B2").Text + "','" + Range("B3").Text + "','" + Range("B4").Text + "')"

Com.ActiveConnection = ConStr
Com.CommandText = ComStr

Dim RecordsAffected As Integer
Call Com.Execute(RecordsAffected, CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
End Sub

''''
Sub FatchRecords()
Dim Com As New ADODB.Command
Set Com = New ADODB.Command

Dim ConStr As String, ComStr As String

ConStr = "Provider = Microsoft.jet.oledb.4.0; data source = H:\trial.mdb; Persist Security Info=False"
ComStr = "Select * from PersonalRecords"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Call rs.Open(ComStr, ConStr, adOpenForwardOnly)

Call Worksheets(2).Range("A2").CopyFromRecordset(rs)

With Sheet2.Range("A1")
Dim offset As Integer
offset = 0
Dim Field As ADODB.Field
For Each Field In rs.Fields
.offset(0, offset).Value = rs.Fields(offset).Name
offset = offset + 1
Next Field
End With

Sheet2.UsedRange.EntireColumn.AutoFit
End Sub

Get data from MS Access using ADODB.

Use the below code to retrieve the data from access database.

Dim DB as new ADODB.DATABASE
dim RS as New ADODB.Recordset
set db = ("Some File Name")
set rs = db.openrecordset("Tablename")

while not rs.eof
cells(x,1) = rs(0)
rs.movenext
wend

Excel Speaks

You can make Excel speak by using the following Macro...

In Cell "A1" type text, you want Excel to speak and than run the following macro

Sub SayThisCell()

Cells(1, 1).Speak

End Sub


Hope you all enjoy it.

vbYesNo

There are times you may want users to click Yes or No. Just insert this line of code. Here the Select Case statement is used.

YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
Case vbYes
'Insert your code here if Yes is clicked
Case vbNo
'Insert your code here if No is clicked
End Select

Loading User forms with MultiPage

To select a page in a Multipage object is fairly simple. Just remember the page 1 has a value of 0, page 2 a value of 1 and so forth. To load a form with a specific page in mind, try using these codes.

Sub page2 ()
UserForm1.MultiPage1.Value = 1 'this sets page 2
UserForm1.Show 'this displays the user form after page 2 has been set
End

User Forms

Adding user forms in your macro is simple. With user forms you can create GUIs (Graphical User Interface) for user who do not have much excel knowledge and make you excel programs more professional looking. Go to your Visual Basic Editor window & click on Insert, select user form and a user for will appear along with the toolbox. Now you can add labels, buttons, text boxes and many more items. The property window will allow you to customise your user form. To display your user form use these codes.

UserForm1.show 'to load form
Unload Me 'to close the form with a macro

Upper Case

To change text in a selected range to upper case use this code.

Sub UpperCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub

Top of the screen

To make the activecell be at the top of the screen & to the left on the screen try this.

Sub TopLeft()
ActiveCell.Select
With ActiveWindow
.ScrollColumn = ActiveCell.Column
.ScrollRow = ActiveCell.Row
End With
End Sub

Title Case

To change text in a selected range to title case use this code.

Sub TitleCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Application.Proper(cell)
End If
Next
End Sub

Timer

To create a macro to measure time before executing the next line of code use this simple code.

Sub timer ()
Application.Wait Now + TimeValue("00:00:10")
MsgBox ("10 sec has elasped")
End Sub

Text Manipulation

I received many queries regarding text manipulation. Here are some useful text functions which you could use to EDIT your text.

Sub myEdit()
MsgBox Left("abcd", 2) 'Displays 2 characters from Left
MsgBox Right("abcd", 2) 'Displays 2 characters from Right
MsgBox Len("abcd") 'Displays number of characters
End Sub

Select Data Range

This is a useful when you need to select the whole range of data to copy to another sheet (especially a large range).

Sub SelAllData()

Application.ScreenUpdating = False
Dim myLastRow As Long
Dim myLastColumn As Long
Range("A1").Select
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address
myRange = "a1:" & myLastCell
Application.ScreenUpdating = True
Range(myRange).Select

End Sub

Sentence Case

To change text in a selected range to sentence case use this code. This code was supplied by Simon Huggins. He did a fine job of making the code work for both earlier & current versions of Excel. Thanks for the contribution.

Sub SentenceCase()
For Each cell In Selection.Cells
s = cell.Value
Start = True
For i = 1 To Len(s)
ch = Mid(s, i, 1)
Select Case ch
Case "."
Start = True
Case "?"
Start = True
Case "a" To "z"
If Start Then ch = UCase(ch): Start = False
Case "A" To "Z"
If Start Then Start = False Else ch = LCase(ch)
End Select
Mid(s, i, 1) = ch
Next
cell.Value = s
Next
End Sub