Userform help!

[JamieDuncan]

Basically im trying to create a userform that adds data to the last row of a sheet, an address book that will be eventually linked with access.

Can anyone tell userforms arent my strong point

Private Sub Cancel_Click()
Unload AddressInput
End Sub

Private Sub OK_Click()
Dim ADDB As Worksheet
Set ADDB = Sheets("Address Book")
Dim CompanyName As TextBox
Dim Line1 As TextBox
Dim Line2 As TextBox
Dim Line3 As TextBox
Dim VAT As TextBox
lasta = ADDB.Range("A65000").End(xlUp).Row + 1
lastb = ADDB.Range("B65000").End(xlUp).Row + 1
lastc = ADDB.Range("C65000").End(xlUp).Row + 1
lastd = ADDB.Range("D65000").End(xlUp).Row + 1
laste = ADDB.Range("E65000").End(xlUp).Row + 1
lasta = CompanyName.Value
lastb = Line1.Value
lastc = Line2.Value
lastd = Line3.Value
laste = VAT.Value
Unload AddressInput
End Sub

 

[TamsinL]

Hi Jamie,

I don't know whether this will help you as the methods are based on row numbers but I am creating a user form that puts patient data into a medical database and I have written code to search for the first empty row and put the data from text boxes into this row - essentially what you are doing as well I think.

I have a textbox on my userform called RowNumber which displays the current row selected in the spreadsheet.

First I have a function that loops through the spreadsheet to find first empty cell in first column which returns the row number:

Private Function FindLastRow()
Dim r As Long

r = 6
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
    r = r + 1
    
Loop

FindLastRow = r

End Function

(my data starts on row 6 hence r=6 - you will need to change this)

I have a button to create a new record which activates the following code when clicked:

Private Sub CreateButton_Click()

LastRow = FindLastRow
RowNumber.Text = FormatNumber(LastRow, 0) 'updates the row number in the text box

ClearData
CreateButton.Enabled = False
SearchButton.Enabled = False
SaveNewButton.Enabled = True
SaveAmendButton.Enabled = False
FirstButton.Enabled = False
NextButton.Enabled = False
PrevButton.Enabled = False
LastButton.Enabled = False
EnableForm

End Sub

Then once I have found this empty row, I add the data in with the following code:

Private Sub AddData()

Dim r As Long
LastRow = FindLastRow

If IsNumeric(RowNumber.Text) Then
        r = CLng(RowNumber.Text)
        
Else
        MsgBox "Illegal row number"
        Exit Sub
        
End If

If r = LastRow Then
        Cells(r, 1) = PatientID.Text
        Cells(r, 2) = DateBox.Text
        Cells(r, 3) = PPI.Text
        Cells(r, 4) = Dose.Text
        
        DisableSave
        
Else
        MsgBox "Invalid row number"
        
End If

End Sub

I don't know if this will help you. Hopefully there is maybe something you can use.

Tamsin

 

[JamieDuncan]

Thanks Tamsin, edited your code to my needs, now it works great

Here is how i mutilated for my own diabolical needs

Private Function FindLastRow()
Dim r As Long

r = 1
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
    r = r + 1
    
Loop

FindLastRow = r

End Function

Private Sub OK_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Address Book").Visible = True
Sheets("Address Book").Select
With ActiveSheet
        Cells(FindLastRow, 1) = CompanyName.Text
        Cells(FindLastRow - 1, 2) = Line1.Text
        Cells(FindLastRow - 1, 3) = Line2.Text
        Cells(FindLastRow - 1, 4) = Line3.Text
        Cells(FindLastRow - 1, 5) = VAT.Text
End With
    Sheets("Address Book").Select
        Range("A1:E65000").Select
            Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
Sheets("Address Book").Visible = xlVeryHidden
Sheets("Data Input").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Unload AddressInput
End Sub