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]

 

[Jerry Sullivan]

Hi and Welcome to the Board,

If you want to pre-populate some of your fields,
you can change the part of your code that currently finds the last row of all columns

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row

to something like this that finds the last row of one of your columns that isn't pre-populated.

iRow = ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row

Regarding sorting, this will depend a little on how you want to handle the pre-populated cells.

Does the serial number or other columns want to get sorted or stay fixed?

You can use the macro-recorder to get this started by recording your manual steps.

Then just revise the fixed sort range that was recorded with a variable that references your current data range to be sorted
(using code like the examples above to find the last row).

 

[alokgarg47]

Thank you for your awesomeness....

But, it still doesn't solve my problem. With the present codes, it finds the last empty cell in the column B.
I did some experiment, if i insert something in A15... leaving all A3-A14 blank, the next entry goes to B16. It doesn't go to B3. (the third row being blank)
Is there some way i could find the first empty cell in that column?

And i want all other cells to get sorted along with the name. Otherwise, i would end up having data from one patient in another's column. Ha!

Thanx
Alok

 

[Jerry Sullivan]

Hi Alok,

Did you replace the existing code with the code I suggested:

iRow = ws.Cells(Cells.Rows.Count, "B").End(xlUp).Row

This will find the last row with data in Column B. In the example you gave it will return 2 (the last row with data). You can add 1 to get the first blank row.

 

[alokgarg47]

Yeah I had replaced mine.

Now, it works!
The +1 helped! Thanks to you.

+1 JS411

PS: I am a toddler with visual basic... i would torture u more, if i get stuck with anything... Hope u dont mind. Thanks a lot!

 

[Jerry Sullivan]

Glad to hear that worked.

And i want all other cells to get sorted along with the name. Otherwise, i would end up having data from one patient in another's column. Ha!

I appreciate that, particularly if I ever find myself a patient at your facility.

Obviously the patient-specific data wants to sort together. Since you are pre-populating some cells, there was a chance that one or more of those might be list-oriented like index numbers.

My advice still stands to use the macro-recorder to get started. Just ask if you need help making that work.

 

[alokgarg47]

I hope you never find me in this facility! Haha.

Have a good day.

 

[AndyOwl]

Re: VBA Newbie - suggestions

Hi

Can I make a couple of suggestions to make your life easier?

Firstly, you run the following code twice, at least:

'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

You could put this in a separate routine - for example:

Private Sub InitialiseControls()
 
< Put code here >
 
End Sub

You could then call the InitialiseControls procedure whenever you want to set the initial control values.

If that was too easy and obvious, how about writing a routine to initialise all of your controls? Something like:

Private Sub BetterInitialisation()
    Dim c As Control
    Dim tb As TextBox
    Dim cb As CheckBox
    
    'loop over all of controls on form
    For Each c In Me.Controls
    
        If TypeOf c Is TextBox Then
        
            Set tb = c
            tb.Text = ""
            
        ElseIf TypeOf c Is CheckBox Then
        
            Set cb = c
            cb.Value = False
            
        Else
        
            'ignore all other controls
            
        End If
        
    Next c
End Sub

I've just finished a complete blog on user forms in Excel, which takes you from the basics upwards. Let me know if you find it useful.

 

[alokgarg47]

I am stuck here once again! Tried searching various blogs, but no help..

At the end of this Userform entry, i wanna hyperlink a folder (same name as one of the entries in the userform) to a particular cell in that row.

I tried fiddling around, but it doesnt work

'hyperlinkme
MRN = Me.txtMRNBox.Text
URL = "D:\1Brain Tumor Database\Patient Files\MRN"
MkDir "URL"
ws.Cells(iRow, 13).Hyperlink.Address:="URL"

Any suggestions?

 

[Jerry Sullivan]

You could try this....

MRN = Me.txtMRNBox.Text
URL = "D:\1Brain Tumor Database\Patient Files\" & MRN & "\"

On Error Resume Next
MkDir URL
On Error GoTo 0

ws.Hyperlinks.Add Anchor:=ws.Cells(iRow, 13), _
    Address:=URL, TextToDisplay:=URL