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.
:)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.