User Forms Help, with writing to Database sheet

CraigTurnerEE

New Member
Joined
Mar 16, 2009
Messages
5
Hey Guys, I am getting my butt kick with this user form, I am creating. I am trying to use a user form to simplify our time sheet application.

I trying to enter 2 cells of information(Project # and Project Description) from the user form to the excel sheet "daily", depending on which day of the week that is selected. Starting on the cell C6, then if information is entered, on that day, then it goes to the next line.

Currently, Everything else is working but, I keep righting over the same line over and over.

I am used a similar code string before, and never had a problem with it going to the next line, can anyone help? I have to be missing something, and now I am frustrated with not being able to fix it. See sub below:


Private Sub EnterProjectInformation_Click()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Daily")
Dim Icolumn As Long
Dim iRow As Long

'find first empty row on "daily Sheet"
iRow = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).Row

'Set Day of the Week

If Trim(Me.DayofWeek_Combobox.Value) = "Thursday" Then
Icolumn = 3
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Friday" Then
Icolumn = 7
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Saturday" Then
Icolumn = 11
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Sunday" Then
Icolumn = 15
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Monday" Then
Icolumn = 20
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Tuesday" Then
Icolumn = 24
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Wednesday" Then
Icolumn = 28
End If




'Write Data to Sheet
ws1.Cells(iRow, Icolumn).Value = Me.ProjectDescription_Text.Value
ws1.Cells(iRow, Icolumn - 1).Value = Me.ProjectNumber_Text.Value

'Clear Data
Me.ProjectDescription_Text = ""
Me.ProjectNumber_Text = ""
Me.ProjectNumber_Text.SetFocus
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This line:
Code:
iRow = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).Row
is returning a number 5 more than the last used row number in column A. Since your data is not being written to column A, you will probably keep getting the same number back! If you want to write to the next availabel row in the relevant column, then you need to assign your iColumn value first, then use that in place of 1:
Code:
iRow = ws1.Cells(Rows.Count, iColumn).End(xlUp).Offset(5, 0).Row
 

CraigTurnerEE

New Member
Joined
Mar 16, 2009
Messages
5
That work, and keeps me from writing over the same line, over and over, but now, I can see to get the row correct row in there. any suggestions?

The first entry is in the correct spot, but I entered that in the spread sheet.

The Entrys at the bottom, and the ones that the user form entered.

moz-screenshot.jpg
Capture.jpg
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How should it know where to put the entries? I don't see a time component and you appear to have two distinct areas into which the data could go (before and after lunch)
 

CraigTurnerEE

New Member
Joined
Mar 16, 2009
Messages
5

ADVERTISEMENT

I hadn't gotten to that part yet, I was thinking, I would avoid entering it, at any particular time, the final sheet that gets printed to PDF doesn't have that information on it, I was going to move the lunch tab, to the bottom, so that you only enter time in and time out on it, so that it knows what time to deducted from the total hours. Does that make sense?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Not really! I suggest you simply start at the first row and loop until the next cell is blank:
Code:
Dim lngRow as long
' start at row 4
lngRow = 4
' your code to get IColumn here
Do until isblank(cells(lngrow, IColumn))
   lngRow = lngRow + 1
Loop
' now you can use lngRow as the output row
 

CraigTurnerEE

New Member
Joined
Mar 16, 2009
Messages
5

ADVERTISEMENT

I will try that, I appreciate all the help Rory, Thank you.
 

CraigTurnerEE

New Member
Joined
Mar 16, 2009
Messages
5
Dim lngRow as long
' start at row 4
lngRow = 4
' your code to get IColumn here
Do until isblank(cells(lngrow, IColumn))
lngRow = lngRow + 1
Loop
' now you can use lngRow as the output row
Worked great except, isblank doesn't exist in 2007, I didn't get the old format, but the code that did work was "IsEmpty"
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Sorry - that was a mistake on my part - it is IsEmpty in code!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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