VBA Newbie

[alokgarg47]

Hey guyz, thanks for this amazing blog.
I am a newbie to visual basic and i would really appreciate if you could help me with my stupid problems...

I am creating a database for brain tumors in excel spreadsheet. So, i am using VBA Userform to add the data.
I have attached an image of the first draft (with fake data).

And the following is the code i used for the userform:


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim iPart As Long
Dim ws As Worksheet
Set ws = Worksheets("Glioblastoma")

'find first empty Row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter a part number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = Me.cboSex.Value
ws.Cells(iRow, 4).Value = Me.txtMrn.Value
ws.Cells(iRow, 5).Value = Me.txtDateofbirth.Value
ws.Cells(iRow, 6).Value = Me.txtDateofpresentation.Value
ws.Cells(iRow, 7).Value = Me.txtDateofsurgery.Value
ws.Cells(iRow, 8).Value = Me.txtSurgicalnote.Value
ws.Cells(iRow, 9).Value = Me.txtIdh.Value
ws.Cells(iRow, 10).Value = Me.txtMgmt.Value
ws.Cells(iRow, 11).Value = Me.txtTp53.Value
ws.Cells(iRow, 12).Value = Me.cboTumobank.Value
ws.Cells(iRow, 13).Value = Me.cboPresentstatus.Value
ws.Cells(iRow, 14).Value = Me.txtComments.Value

'clear the data
Me.txtName.Value = ""
Me.cboSex.Value = ""
Me.txtMrn.Value = ""
Me.txtDateofbirth.Value = ""
Me.txtDateofpresentation.Value = ""
Me.txtDateofsurgery.Value = ""
Me.txtSurgicalnote.Value = ""
Me.txtIdh.Value = ""
Me.txtMgmt.Value = ""
Me.txtTp53.Value = ""
Me.cboTumobank.Value = ""
Me.cboPresentstatus.Value = ""
Me.txtComments.Value = ""
Me.txtName.SetFocus

End Sub
Private Sub cmdClear_Click()
'clear the data
Me.txtName.Value = ""
Me.cboSex.Value = ""
Me.txtMrn.Value = ""
Me.txtDateofbirth.Value = ""
Me.txtDateofpresentation.Value = ""
Me.txtDateofsurgery.Value = ""
Me.txtSurgicalnote.Value = ""
Me.txtIdh.Value = ""
Me.txtMgmt.Value = ""
Me.txtTp53.Value = ""
Me.cboTumobank.Value = ""
Me.cboPresentstatus.Value = ""
Me.txtComments.Value = ""
Me.txtName.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Now my problem is, I dont wanna keep an empty row in the spreadsheet. I mean, i wanna add a few more cells with some formula to calculate from the newly added data. for example, in the first column, i wanna fill up the serial numbers beforehand. Now, if i fill those data, the data would get filled up to the next empty row.

I have another query. At the end of each update, i would like to save the data alphabetically (Name column "B2"). How can i add a macro to do that?

Thanks a lot!
Any help will be appreciated

[/URL][/IMG]

 

[alokgarg47]

Thanks a lot! It Works!!