User form
 

Hi all,

I am trying to adapt the information from
http://www.contextures.com/xlUserForm01.html to make a userform for my
worksheet. I got everything working, almost!!! The form enters data in A2
of my sheet and I can't figure out how to make it enter it into the next
available cell in the range of F17:F50. There were alot of places I had to
change things in Debra's code to work for my form and I think I must have
done something wrong. Any help would be appreciated.
Here is the code that I have so far:

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

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a employee name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter New Employee Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

'clear the data
Me.txtName.Value = ""

End Sub

Thanks
Mike Rogers

User form
 

This bit of code

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

finds the last used row in column A (1), so c hange that to

'find first empty row in database
iRow = ws.Cells(Rows.Count, "F") _
.End(xlUp).Offset(1, 0).Row

and this bit copies it

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

so change that to

'copy the data to the database
ws.Cells(iRow, "F").Value = Me.txtName.Value


Before the copy though, you need to increment iRow by one, as you have found
the last used cell, not the first free one.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike Rogers" wrote in message
...
Hi all,

I am trying to adapt the information from
http://www.contextures.com/xlUserForm01.html to make a userform for my
worksheet. I got everything working, almost!!! The form enters data in
A2
of my sheet and I can't figure out how to make it enter it into the next
available cell in the range of F17:F50. There were alot of places I had
to
change things in Debra's code to work for my form and I think I must have
done something wrong. Any help would be appreciated.
Here is the code that I have so far:

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

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a employee name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter New Employee Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

'clear the data
Me.txtName.Value = ""

End Sub

Thanks
Mike Rogers

User form
 

Bob,
Thanks for the help!! I can see now how it finds column F and the next
empty row. I made those two changes and it worked. I did not try to
increment iRow by one because I don't really know how or where to do that.
But bottom line...It works!!!
Thanks again for your help.

"Bob Phillips" wrote:

This bit of code

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

finds the last used row in column A (1), so c hange that to

'find first empty row in database
iRow = ws.Cells(Rows.Count, "F") _
.End(xlUp).Offset(1, 0).Row

and this bit copies it

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

so change that to

'copy the data to the database
ws.Cells(iRow, "F").Value = Me.txtName.Value


Before the copy though, you need to increment iRow by one, as you have found
the last used cell, not the first free one.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Mike Rogers" wrote in message
...
Hi all,

I am trying to adapt the information from
http://www.contextures.com/xlUserForm01.html to make a userform for my
worksheet. I got everything working, almost!!! The form enters data in
A2
of my sheet and I can't figure out how to make it enter it into the next
available cell in the range of F17:F50. There were alot of places I had
to
change things in Debra's code to work for my form and I think I must have
done something wrong. Any help would be appreciated.
Here is the code that I have so far:

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

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a employee name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please enter New Employee Name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

'clear the data
Me.txtName.Value = ""

End Sub

Thanks
Mike Rogers