|
VB code help
Hi all,
I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Anthony,
You need to cancel the closure of the workbook at some point. Try inserting: Cancel = True after: x.Offset(0, 2).Select I have not otherwise looked at your code. --- Regards, Norman "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Norman,
thanks for help, but by inserting Cancle=True didn't actualy do anything! any other ideas ? thanks "Norman Jones" wrote: Hi Anthony, You need to cancel the closure of the workbook at some point. Try inserting: Cancel = True after: x.Offset(0, 2).Select I have not otherwise looked at your code. --- Regards, Norman "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Anthony
Norman, thanks for help, but by inserting Cancle=True didn't actualy do anything! any other ideas ? thanks I may have mis read your code. Try inserting the Cancel = True instruction after the line: MsgBox "Todays Date not found" ' edit as needed The workbook_BeforeClose procedure is invoked when an instruction to close the workbook is issued. Including the instruction Cancel = True cancels the original close instruction. If, the code correctly reaches the MsgBox line, then the Cancel instruction should prevent the file from closing. --- Regards, Norman "Anthony" wrote in message ... Norman, thanks for help, but by inserting Cancle=True didn't actualy do anything! any other ideas ? thanks "Norman Jones" wrote: Hi Anthony, You need to cancel the closure of the workbook at some point. Try inserting: Cancel = True after: x.Offset(0, 2).Select I have not otherwise looked at your code. --- Regards, Norman "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Dim TimeCheck As Date
Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
As always - Bob to the rescue !
Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
oops my mistake,
the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
I just tested it, and it seems to work.
Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Bob.
BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Bob,
I have today's date in C7, a 'name' in D7 and 'Y' in E7 and still get reply "varify sweep completed" so what am I doing wrong as to complete the log and close the worksheet if todays date is found in Column C a name shud be in column D and a 'Y' in E, if not then the error respnse. cheers "Bob Phillips" wrote: I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Norman,
can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Anthony,
This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Norman
This code, given by Bob and yourself........ Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes ifneeded MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If .....this kinda works but as I said before if you have data entered into columns C,D and E it still shows the "verify sweep complete" msg box, where as it should exit any ideas as to why this is not working? thanks every so much Anthony "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Anthony,
My posted code is a little different. Did you try it, In my test book, if columns C,D and E are correctly populated, the file closes. --- Regards, Norman "Anthony" wrote in message ... Norman This code, given by Bob and yourself........ Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes ifneeded MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If ....this kinda works but as I said before if you have data entered into columns C,D and E it still shows the "verify sweep complete" msg box, where as it should exit any ideas as to why this is not working? thanks every so much Anthony "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Antony,
Just to add, you did not answer the questions I posed. --- Regards, Norman "Norman Jones" wrote in message ... Hi Anthony, My posted code is a little different. Did you try it, In my test book, if columns C,D and E are correctly populated, the file closes. --- Regards, Norman |
VB code help
Norman,
Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Anthony,
I found it failed if the date cell contained a formula date, so I change dit to this and it works, at least for me Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select End If 'this is new location End If End With End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Hi Anthony,
Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? For test purposes, I made the very simplest of worksheets: I entered a sequence of dates in cells C6:C21; the date in cell C11 being today's date. In D11 I entered a name and in E11 I entered Y. With this arrangement the workbook closes; if I then delete the name entry, I get the msgbox and the workbook remains open. In this simple test, no other cells were populated. You did notice that, in the second instance, I set Cancel = True Regards, Norman "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated thanks |
VB code help
Bob,
see my last post to Norman, his code almost worked, the one you just posted, again closes without alowing user to input data - helllpppp ! "Bob Phillips" wrote: Anthony, I found it failed if the date cell contained a formula date, so I change dit to this and it works, at least for me Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select End If 'this is new location End If End With End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS |
VB code help
Hi Anthony,
Bob's code worked for me providing I added: Cancel = True after the line: x.Offset(1).Select --- Regards, Norman "Anthony" wrote in message ... Bob, see my last post to Norman, his code almost worked, the one you just posted, again closes without alowing user to input data - helllpppp ! "Bob Phillips" wrote: Anthony, I found it failed if the date cell contained a formula date, so I change dit to this and it works, at least for me Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select End If 'this is new location End If End With End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS |
VB code help
Norman/Bob,
Guys thanks so much for your patience with this - now its driving me nuts. You say it works for you, I copy and execute the same as you do and mine does'nt I'll expalin exactly what I am doing here, maybe you can see my error. This is the exact code I have placed into my before close event...... Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = True End If 'this is new Location End If End With End If I have the column C formatted as Custom dd mmmm yyyy so, first test, nothing populated in any cell, I close the workbook and the msg box "todays date not found", which is correct, I then click on OK and the log closes !!! sorry guys but can you help further many thanks "Norman Jones" wrote: Hi Anthony, Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? For test purposes, I made the very simplest of worksheets: I entered a sequence of dates in cells C6:C21; the date in cell C11 being today's date. In D11 I entered a name and in E11 I entered Y. With this arrangement the workbook closes; if I then delete the name entry, I get the msgbox and the workbook remains open. In this simple test, no other cells were populated. You did notice that, in the second instance, I set Cancel = True Regards, Norman "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated |
VB code help
I thought he wanted to close in that circumstance? Your original code had
Cancel = False, so I just removed it. -- HTH RP (remove nothere from the email address if mailing direct) "Norman Jones" wrote in message ... Hi Anthony, Bob's code worked for me providing I added: Cancel = True after the line: x.Offset(1).Select --- Regards, Norman "Anthony" wrote in message ... Bob, see my last post to Norman, his code almost worked, the one you just posted, again closes without alowing user to input data - helllpppp ! "Bob Phillips" wrote: Anthony, I found it failed if the date cell contained a formula date, so I change dit to this and it works, at least for me Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select End If 'this is new location End If End With End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS |
VB code help
How about mailing me a workbook?
-- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman/Bob, Guys thanks so much for your patience with this - now its driving me nuts. You say it works for you, I copy and execute the same as you do and mine does'nt I'll expalin exactly what I am doing here, maybe you can see my error. This is the exact code I have placed into my before close event...... Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = True End If 'this is new Location End If End With End If I have the column C formatted as Custom dd mmmm yyyy so, first test, nothing populated in any cell, I close the workbook and the msg box "todays date not found", which is correct, I then click on OK and the log closes !!! sorry guys but can you help further many thanks "Norman Jones" wrote: Hi Anthony, Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? For test purposes, I made the very simplest of worksheets: I entered a sequence of dates in cells C6:C21; the date in cell C11 being today's date. In D11 I entered a name and in E11 I entered Y. With this arrangement the workbook closes; if I then delete the name entry, I get the msgbox and the workbook remains open. In this simple test, no other cells were populated. You did notice that, in the second instance, I set Cancel = True Regards, Norman "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Hi all, I have had help putting this code together, but can't get it to work properly. The code checks a worksheet named 'sweep log' Column C for today's date, if its not found then a msg box "todays date not found" is displayed, or if it is found then a check that data is also entered into the adjoining columns D and E. Problem is that the checks are carried out and the correct msg boxes are shown but on acknowledment of these the worlsheet closes, instead of allowing the data to be entered. The code has been placed within the Before Close event, and is shown here... Dim TimeCheck As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed SweepCheck = False Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays date then go to the empty cell below x.Offset(0, 2).Select Exit Sub End If 'this is new location End If End With End If it must be something simple, but being a novice, I can't put my finger on it any help apreciated |
VB code help
Hi Bob,
Given Anthony's experience, I no longer know. Doubtless, however, your kind offer to review Anthony's book will clarify matters. --- Regards, Norman "Bob Phillips" wrote in message ... I thought he wanted to close in that circumstance? Your original code had Cancel = False, so I just removed it. -- HTH RP (remove nothere from the email address if mailing direct) |
VB code help
Bob,
the actual workbook is almost 7mb ! so I won't send that but I will send a copy of the worksheet that the code is to work in, is that ok? "Bob Phillips" wrote: How about mailing me a workbook? -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman/Bob, Guys thanks so much for your patience with this - now its driving me nuts. You say it works for you, I copy and execute the same as you do and mine does'nt I'll expalin exactly what I am doing here, maybe you can see my error. This is the exact code I have placed into my before close event...... Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = True End If 'this is new Location End If End With End If I have the column C formatted as Custom dd mmmm yyyy so, first test, nothing populated in any cell, I close the workbook and the msg box "todays date not found", which is correct, I then click on OK and the log closes !!! sorry guys but can you help further many thanks "Norman Jones" wrote: Hi Anthony, Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? For test purposes, I made the very simplest of worksheets: I entered a sequence of dates in cells C6:C21; the date in cell C11 being today's date. In D11 I entered a name and in E11 I entered Y. With this arrangement the workbook closes; if I then delete the name entry, I get the msgbox and the workbook remains open. In this simple test, no other cells were populated. You did notice that, in the second instance, I set Cancel = True Regards, Norman "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If |
VB code help
Yeah, that is fine. Test it first to make sure it behaves as you are saying.
-- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Bob, the actual workbook is almost 7mb ! so I won't send that but I will send a copy of the worksheet that the code is to work in, is that ok? "Bob Phillips" wrote: How about mailing me a workbook? -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... Norman/Bob, Guys thanks so much for your patience with this - now its driving me nuts. You say it works for you, I copy and execute the same as you do and mine does'nt I'll expalin exactly what I am doing here, maybe you can see my error. This is the exact code I have placed into my before close event...... Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(Rows.Count, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlValues, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True ElseIf x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = True End If 'this is new Location End If End With End If I have the column C formatted as Custom dd mmmm yyyy so, first test, nothing populated in any cell, I close the workbook and the msg box "todays date not found", which is correct, I then click on OK and the log closes !!! sorry guys but can you help further many thanks "Norman Jones" wrote: Hi Anthony, Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? For test purposes, I made the very simplest of worksheets: I entered a sequence of dates in cells C6:C21; the date in cell C11 being today's date. In D11 I entered a name and in E11 I entered Y. With this arrangement the workbook closes; if I then delete the name entry, I get the msgbox and the workbook remains open. In this simple test, no other cells were populated. You did notice that, in the second instance, I set Cancel = True Regards, Norman "Anthony" wrote in message ... Norman, Ok I placed your code into my Beforeclose event so removing the Private Sub Workbook_BeforeClose(Cancel As Boolean) at the top and the End Sub at the end. But still the workbook closes after showing the "varify sweep complete" msg box. Can you tell me exactly what data you placed in cells C7,D7 and E7 to make it work?? Also sorry I missed you other questions, didn't notice them at the end, so in answer to them.... If today's date is found, must both the corresponding D an E cells be populated? Yes If the D / E cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. If the date is found but no other data then either column D or E can be selected Hope this helps, now can you solve it for me thanks again Anthony "Norman Jones" wrote: Hi Anthony, This may be getting nearer to what you want: '========== Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, _ SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If x.Offset(0, 2) < "Y" Then If x.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(1).Select Cancel = False Exit Sub End If 'this is new location End If End With End If End Sub '<<========== However several things are unclear to me. If today's date is found, must both the corresponding D an F cells be populated? If the D / F cells are not populated, which cell is to be selected. The changes in the above code relect my (current) best guesses. --- Regards, Norman "Anthony" wrote in message ... Norman, can you help anymore with this ? see my previous update, many thanks "Norman Jones" wrote: Hi Bob. BTW, I only coded what Norman suggested :-)) The temerity!! --- Regards, Norman "Bob Phillips" wrote in message ... I just tested it, and it seems to work. Are you sure you have those dates and a Y offset 2 columns? BTW, I only coded what Norman suggested :-)) -- HTH RP (remove nothere from the email address if mailing direct) "Anthony" wrote in message ... oops my mistake, the code supplied by Bob and Norman works better but even after entering all the data into columns C,D and E I still get the propmt to enter data, the workbook should now close, any other ideas "Anthony" wrote: As always - Bob to the rescue ! Thanks "Bob Phillips" wrote: Dim TimeCheck As Date Dim WS As Worksheet Dim sRng As Range Dim x As Object Dim sDate As Date TimeCheck = Format(Now(), "h:mm") If TimeCheck "09:00" Then sDate = DateSerial(Year(Now()), Month(Now()), Day(Now())) Set WS = Sheets("sweep log") With WS Set sRng = Range(Cells(6, 3), Cells(65536, 3).End(xlUp)) Set x = sRng.Find(What:=sDate, LookIn:=xlFormulas, SearchDirection:=xlNext) If x Is Nothing Then MsgBox "Todays Date not found" ' edit as needed Cancel = True Exit Sub End If If ActiveCell.Offset(0, 2) < "Y" Then If ActiveCell.Offset(0, 1) = "" Then 'change to Yes if needed MsgBox "verify sweep completed" 'this is old location WS.Activate 'if "Y" is not found in same row as todays _ 'date then go to the empty cell below x.Offset(0, 2).Select Cancel = True Exit Sub End If 'this is new location End If End With End If |
| All times are GMT +1. The time now is 09:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com