vba sorting

[mharper90]

This is my first vba project ever, so please excuse my novice approach.

I work at a summer camp and am creating a user form that allows you to input a camper's name and medications, and upon submission of the form it puts the information into a printer friendly page that has am/lunch/pm initial boxes for each day of the week as I administer their medications. That's the background. I got the form to take the information, display it appropriately on the printer friendly page and even do the proper fancy outlining I needed for this setup.

Now my problem is alphabetizing. I would like to have either an alphabetize button, or preferably automatically alphabetize upon pressing the submit button. The difficulty here is that each camper's "section" is 3 rows by about 12 columns and this information needs to stay together, not just alphabetize by row, but by ever 3 rows I guess.

I've done alot of searching and can't find any info on this. If anyone has any advice, it would be greatly appreciated! Thank you so much in advance! I've got campers showing up next week and I really hope to have this done by then.

 

[mikerickson]

Quick and dirty solution would be to add a helper column. Write the camper's name in each of the 3 rows of that column for that camper and then sort on that column. Each camper's three rows would stay together in the order, relative to each other, that they were in before the sort.

Having said that, do you still need a VBA routine?

 

[mharper90]

Is there a way to do a "blind" helper column? The campers name is already in the 3rd column of the first row, perhaps I could have it duplicated 3 more times at the end of each row, but I wouldn't want this to print. Maybe that column could be purposefully outside the print area?

Also, is there a way to integrate this into the vba? This is my second summer at this camp, so I realize the need for a better solution like this vba project instead of our old method, but because this is my last summer here, I want to set the camp up well to have a user-friendly setup, and I think having as much automated as possible would help.

I'm not on the computer right now where my code is, I can upload it later if that helps?? Thank you so much for your quick and helpful response!

 

[Teeroy]

There are many ways do do it. Two quick ones are You can hide the columns that you don't want to print using a Workbook_BeforePrint VBA event to do the hiding, or you can have permanently hidden helper columns and run the sort from a button on the sheet (with VBA).

 

[mharper90]

I can visualize what you are saying, but unfortunately I'm not sure how to actually implement this. Any chance you could provide some more details to help me? I've pasted part of my code below so you can see where I'm at. (Note, I have made some partial changes to the form but not yet cleaned up some of the code...this code works, but there are a few extra pieces I will be deleting later)

Thanks again!


Private Sub SubmitBttn_Click()
Dim RowCount As Long
Dim ctl As Control

If Me.FirstNameBox.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "New Medication"
Me.FirstNameBox.SetFocus
Exit Sub
End If
If Me.LastNameBox.Value = "" Then
MsgBox "Please enter a Last Name.", vbExclamation, "New Medication"
Me.LastNameBox.SetFocus
Exit Sub
End If
If Not IsDate(Me.DOBBox.Value) Then
MsgBox "The DOB box must contain a date.", vbExclamation, "New Medication"
Me.DOBBox.SetFocus
Exit Sub
End If
If Me.AgeBox.Value = "" Then
MsgBox "Please enter an Age.", vbExclamation, "New Medication"
Me.AgeBox.SetFocus
Exit Sub
End If
If Me.Med1Name.Value = "" Then
MsgBox "Please enter at least 1 Medication.", vbExclamation, "New Medication"
Me.Med1Name.SetFocus
Exit Sub
End If

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.FirstNameBox.Value
.Offset(RowCount, 1).Value = Me.MIBox.Value
.Offset(RowCount, 2).Value = Me.LastNameBox.Value
.Offset(RowCount, 3).Value = Me.AreaBox.Value
.Offset(RowCount, 4).Value = Me.Med1Name.Value

If Me.Med1Brkfst.Value = True Then
.Offset(RowCount, 5).Value = "a.m."
.Offset(RowCount, 6).Value = "a.m."
.Offset(RowCount, 7).Value = "a.m."
.Offset(RowCount, 8).Value = "a.m."
.Offset(RowCount, 9).Value = "a.m."
.Offset(RowCount, 10).Value = "a.m."
.Offset(RowCount, 11).Value = "a.m."
Else
.Offset(RowCount, 5).Value = "-"
.Offset(RowCount, 6).Value = "-"
.Offset(RowCount, 7).Value = "-"
.Offset(RowCount, 8).Value = "-"
.Offset(RowCount, 9).Value = "-"
.Offset(RowCount, 10).Value = "-"
.Offset(RowCount, 11).Value = "-"
End If

End With

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")

Borders
.Offset(RowCount, 0).Value = DateValue(Me.DOBBox.Value)
If Me.MaleOption.Value = True Then
.Offset(RowCount, 1).Value = "M"
End If
If Me.FemaleOption.Value = True Then
.Offset(RowCount, 1).Value = "F"
End If
.Offset(RowCount, 4).Value = Me.Med1Dose.Value

If Me.Med1Lunch.Value = True Then
.Offset(RowCount, 5).Value = "lunch"
.Offset(RowCount, 6).Value = "lunch"
.Offset(RowCount, 7).Value = "lunch"
.Offset(RowCount, 8).Value = "lunch"
.Offset(RowCount, 9).Value = "lunch"
.Offset(RowCount, 10).Value = "lunch"
.Offset(RowCount, 11).Value = "lunch"
Else
.Offset(RowCount, 5).Value = "-"
.Offset(RowCount, 6).Value = "-"
.Offset(RowCount, 7).Value = "-"
.Offset(RowCount, 8).Value = "-"
.Offset(RowCount, 9).Value = "-"
.Offset(RowCount, 10).Value = "-"
.Offset(RowCount, 11).Value = "-"
End If

End With

RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Sheet1").Range("A1")
.Offset(RowCount, 0).Value = Me.AgeBox.Value + " y/o"
.Offset(RowCount, 3).Value = Me.CabinBox.Value
.Offset(RowCount, 4).Value = Me.CommentsBox.Value

If Me.Med1Dinner.Value = True Then
.Offset(RowCount, 5).Value = "p.m."
.Offset(RowCount, 6).Value = "p.m."
.Offset(RowCount, 7).Value = "p.m."
.Offset(RowCount, 8).Value = "p.m."
.Offset(RowCount, 9).Value = "p.m."
.Offset(RowCount, 10).Value = "p.m."
.Offset(RowCount, 11).Value = "p.m."
Else
.Offset(RowCount, 5).Value = "-"
.Offset(RowCount, 6).Value = "-"
.Offset(RowCount, 7).Value = "-"
.Offset(RowCount, 8).Value = "-"
.Offset(RowCount, 9).Value = "-"
.Offset(RowCount, 10).Value = "-"
.Offset(RowCount, 11).Value = "-"
End If

End With

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Or TypeName(ctl) = "OptionButton" Then
ctl.Value = False
End If
Next ctl

End Sub

 

[Teeroy]

Your setting of the values can be condensed in each block to e.g.

If Me.Med1Brkfst.Value Then txt = "a.m." Else txt = "-"
For i = 5 To 11
    .Offset(RowCount, i).Value = txt
End If

I don't understand why you are looping through the controls and clearing them one by one. You could just unload the userform and all the data is cleared.

As to the sorting, it depends on the version of excel you are using. It is implemented differently from excel 2007 onward and I've only got excel 2003 at home.