Userform help!

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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 :rolleyes:

Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

TamsinL

New Member
Joined
Oct 5, 2006
Messages
10
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:

Rich (BB code):
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:

Rich (BB code):
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:

Rich (BB 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

Board Regular
Joined
Aug 23, 2006
Messages
132
Thanks Tamsin, edited your code to my needs, now it works great

:devilish: Here is how i mutilated for my own diabolical needs :devilish:

Code:
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

Code:
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
 

Forum statistics

Threads
1,141,625
Messages
5,707,476
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top