If cells have figures already then don't fill

[wildpianist]

Hello

Carrying on from my previous Date & Excel post I need to enter details and fill the cells once, however I don't want the form to overwrite what I've put in.

I assume the code is something like

[vba]

If IsEmpty then
MsgBox "You have already entered details"
end if
[/vba]

[Bob Phillips]

It would be something like

[vba]
If IsEmpty(Range("C3").Value Then
'do something
End If
[/vba]

but your requirements are somewhat vague.

[wildpianist]

That's great - thanks, however I need it to do the same as the last one that went along and found the date, so something like

[vba]
If IsEmpty(Range(Date).Value Then
'whatever
End if
[/vba]

Is it that simple?

[Bob Phillips]

and the other one that did the same was what?

[wildpianist]

Apologies xld.

The previous code you kindly gave me
[vba]
col = Application.Match(CLng(Date), Rows(2), 0) 'On Error Goto 0

If col > 0 Then
For i = 1 To 30

.Offset(1 + i, col - 1).Value = Me.Controls("TextBox" & i).Text
Next i

End If
[/vba]

which looks along the row and finds the date and prefills all the info in the textboxes on the UserForm. All I need it to do is if there is already something in those cells which have today's date at the top not to overwrite what was already in there.

[Bob Phillips]

Oops, posted to wrong thread

[vba]

Private Sub CommandButton1_Click()
Const MAX_ROWS As Long = 30
Const START_ROWS As Long = 3
Dim rng As Range
Dim cell As Range
Dim col As Long
Dim i As Long
Dim NextRow As Long

With Worksheets("Team Total")

On Error Resume Next
col = Application.Match(CLng(Date), Rows(2), 0)
On Error GoTo 0

If col > 0 Then

NextRow = 2
For i = 1 To MAX_ROWS

If .Cells(i + START_ROWS - 1, col).Value = "" Then

If rng Is Nothing Then

Set rng = .Cells(i + START_ROWS - 1, col)
Else

Set rng = Union(rng, .Cells(i + START_ROWS - 1, col))
End If
End If
Next i

For i = 1 To rng.Cells.Count

.Range(rng.Cells(i, 1).Address).Value = Me.Controls("TextBox" & i).Text
Next i
End If
End With

Me.Hide
End Sub
[/vba]

[wildpianist]

Good Grief - what a lot of code!

Thank you very much xld.

[wildpianist]

Why do I get an error message with the .Cells and .Range, keep getting Invalid or unqualified reference

[Bob Phillips]

What line?

[wildpianist]

[vba]If .Cells(i + START_ROWS - 1, col).Value = "" Then
[/vba]
and
[vba]
Set rng = .Cells(i + START_ROWS - 1, col)
[/vba] and the
[vba]
.Range(rng.Cells(i, 1).Address).Value = Me.Controls("TextBox" & i).Text
[/vba]

Seems not to like anything with a . before it.

[Bob Phillips]

Should not matter, the With statement earlier qualifies them.

I tried it again, and it had one small problem but not a compile error. Can you post your workbook?

[wildpianist]

Apologies. The With line was rem-ed. My fault. Now I get a slight error with the [vba]For i = 1 To rng.cells.count[/vba] line

Also what I am trying to do with the original one I've got
Environ("USERNAME") to switch the sheets to which ever sheet the person logged in is. How would I look to incorporate this as it only selects the Team Total sheet.

I'll upload a workbook later for you.

Thanks again for your kind assistance

[wildpianist]

Here is the workbook.

[Bob Phillips]

You changed the data :-)

[vba]

Private Sub CommandButton1_Click()
Const MAX_ROWS As Long = 30
Const START_ROWS As Long = 3
Dim rng As Range
Dim cell As Range
Dim col As Long
Dim i As Long
Dim sh As Worksheet

Dim user As String
'Dim col As Long
'Dim i As Long

Select Case Environ("USERNAME")

Case "bloggsj": Set sh = Worksheets("Agent A")
Case "mallettt": Set sh = Worksheets("Agent B")
Case "baileyb": Set sh = Worksheets("Agent C")
Case "doej": Set sh = Worksheets("Agent D")
Case "Bob": Set sh = Worksheets("Team Total")
End Select

If Not sh Is Nothing Then

With sh

On Error Resume Next
col = Application.Match(CLng(Date), .Rows(2), 0)
On Error GoTo 0

If col > 0 Then

NextRow = 2
For i = 1 To MAX_ROWS

If .Cells(i + START_ROWS - 1, col).Value = "" Or _
.Cells(i + START_ROWS - 1, col).Value = 0 Then

If rng Is Nothing Then

Set rng = .Cells(i + START_ROWS - 1, col)
Else

Set rng = Union(rng, .Cells(i + START_ROWS - 1, col))
End If
End If
Next i

If Not rng Is Nothing Then

For i = 1 To rng.Cells.Count

.Range(rng.Cells(i, 1).Address).Value = Me.Controls("TextBox" & i).Text
Next i
End If
End If
End With
End If

Me.Hide

End Sub
[/vba]

[wildpianist]

Thats Brilliant!!!! xld

But it still allows me to overtype what was put in. As you've probably guessed I'm writing a productivity spreadsheet - and basically when someone enters their figures for the day they then can't go back in and fiddle with the amounts, until the next day.

[Bob Phillips]

Can you detail an example mate because it worked fine for me.

Maybe post a workbook with some data already in, and tell me what you enter and what gets over-typed.

[wildpianist]

Xld - you are quite right, it does work - however everything needs to be filled in, which is no problem. Is there anyway that if textbox has nothing in it then put zero in a cell?

Also - If there is info in the cell for that user already, can it display a MsgBox to say "You have already inputted data for today etc etc"

[Bob Phillips]

That will be a lot of Msgboxes which will intrude. Better to accumulate them and one MsgBox and the end with all details?

[wildpianist]

Thats what I meant I think.

Each user enters the data of 30 things. Then pressed submit, and prefills the sheet for that day. But if the user then goes back in for that day and tries to change what they have entered then flash up a box saying wait til tomorrow - if they have entered details incorrectly then they tell the person who gets all the figures and amends it then.

[wildpianist]

so basically if cells for that user already have something in for all of them then pop a message box up. I think its something to do with the IsEmpty = False or something like that.