PDA

View Full Version : If cells have figures already then don't fill



wildpianist
01-18-2008, 03:32 AM
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



If IsEmpty then
MsgBox "You have already entered details"
end if

Bob Phillips
01-18-2008, 04:52 AM
It would be something like


If IsEmpty(Range("C3").Value Then
'do something
End If


but your requirements are somewhat vague.

wildpianist
01-18-2008, 04:56 AM
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


If IsEmpty(Range(Date).Value Then
'whatever
End if


Is it that simple?

Bob Phillips
01-18-2008, 05:19 AM
and the other one that did the same was what?

wildpianist
01-18-2008, 05:26 AM
Apologies xld.

The previous code you kindly gave me

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


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
01-18-2008, 07:21 AM
Oops, posted to wrong thread



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

wildpianist
01-18-2008, 07:36 AM
Good Grief - what a lot of code!

Thank you very much xld.

wildpianist
01-18-2008, 07:42 AM
Why do I get an error message with the .Cells and .Range, keep getting Invalid or unqualified reference :(

Bob Phillips
01-18-2008, 08:28 AM
What line?

wildpianist
01-18-2008, 09:04 AM
If .Cells(i + START_ROWS - 1, col).Value = "" Then

and

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

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


Seems not to like anything with a . before it.

Bob Phillips
01-18-2008, 09:08 AM
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
01-18-2008, 09:32 AM
Apologies. The With line was rem-ed. My fault. Now I get a slight error with the For i = 1 To rng.cells.count 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
01-18-2008, 10:27 AM
Here is the workbook.

Bob Phillips
01-18-2008, 11:10 AM
You changed the data :-)



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

wildpianist
01-18-2008, 11:34 AM
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
01-18-2008, 12:36 PM
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
01-19-2008, 04:07 AM
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
01-19-2008, 04:23 AM
That will be a lot of Msgboxes which will intrude. Better to accumulate them and one MsgBox and the end with all details?

wildpianist
01-19-2008, 04:27 AM
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
01-19-2008, 04:28 AM
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.

Bob Phillips
01-19-2008, 04:42 AM
Now you have totally lost me!

wildpianist
01-19-2008, 04:47 AM
LOL Sorry xld!

When a user enters figures in the userform it prefills the cells corresponding to the date in the sheet of the Agent ie bloggsj in Agent A. Which is spot on. -

The user has to enter all fields in the userform to go across to the sheet which is fine and then when they try to reenter any more details it just doesn't fill which is what I want to happen. But I need it to tell the user in form of a MsgBox that they have already filled all the boxes already and can't re enter any more details until the next day. So same as it is, but just need a msgbox if someone tries to reenter any more detail for that day.

:D

Bob Phillips
01-19-2008, 05:11 AM
So you mean that if say they enter 18 textboxes in ons hit, then they have another go and enter say 14, then we add the 12, and tell them about the 2 that they couldn't.

wildpianist
01-19-2008, 05:40 AM
nah - they'll have to enter all 30 boxes in one hit. So the processes they dont do they leave blank, hence why I'd like any blank textboxes to go across as a 0 then if they try to enter different figures in any or all of the boxes to say "No!" in a box :)

wildpianist
01-21-2008, 01:15 AM
How do I get it that if the TextBox is blank to automatically put a "0" in the cell?

wildpianist
01-23-2008, 02:40 AM
Hello. I'm still stuck with this :(


Private Sub CommandButton3_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 "doej": Set sh = Worksheets("Agent B")
Case "mallettt": Set sh = Worksheets("Agent C")
Case "beadlej": Set sh = Worksheets("Agent F")
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
'If Me.Controls("TextBox" & i).Text = "" Then
' cell.Value = "0"
'End If

End If
End If
End With
End If
Application.Visible = True

Me.Hide

End Sub


This works fine. As you can see at the bottom I have tried to tell the workbook that if the textbox value is blank then the cell value I would like to show "0". This code also stops any duplication or overwriting for that day. But what I'd also like it to do that if a user has already entered something for that day for a MsgBox to come up to inform that user that they would have to wait until tomorrow.

:)