Replies

LOUNGERS…I received the following reply (and attachment) from Hans at Eileens’ Lounge………..

Re: Checking for duplicate/similar values

Postby HansV » 12 Apr 2013, 06:24
You can use the following array formula confirmed with Ctrl+Shift+Enter in N8:

=IF(MAX(COUNTIF(D8:M8,Q$8:Q$16&”*”))>1,1,””)

Fill down to N19.

For this to work, all values in D8:M19 should be text values. You can turn the numbers 1, 2, etc. into text values by prefixing them with an apostrophe ‘.
To turn new entries into text automatically, format D8:M19 as Text.

See the attached version.

Thank you,…saw that article a while back…iCloud works just fine…it is OLook that has the problem…..

It is set as 8am to 5pm, and a work week is Mon-Fri….I have hunch it has something to do with using icloud……..at the moment, when I make an appointment in Calendar, it is made in the iCloud calendar (see 2nd screen shot attached)…if I uncheck the icloud selections and use the regular OL calendar, that problem disappears. I would be happy to do that, except that when it is in that selection mode, whenever I open the Calendar, it opens both the ‘regular’ calendar and the icloud one (showing 2 calendars), which is a nuisance.

OK…sorry & thank you…I didn’t know there was such a policy.

Hi Steve….thank you for your reply, altho I can’t seem to actually find it…I have checked the cross post at eileenslounge but there is no message orattachment..??..??

Thank you for that information, RG….alwasy appreciate everything that I find here.

Thank you Simon…..as with many things, the simpler the better….what I am doing is trying to find a format that will allow users to enter numbers (eg: 930, 1000, 1400 etc) and have them automatically formatted as times — your suggestion is perfect bc users don’t need to hit shift-colon to enter the time separator – only the numbers, so this is ideal….thank you !

Thank you for that Steve…I will set it up like that and give it a test.

Hi Steve….yes, there is code for the POSTPONE procedure, actually I think you helped me with it…that code is:

Option Explicit
Public Sub Postpone(Target As Range, strStatusCol As String, strDataStartCol As String, strDataEndCol As String)
Const MsgTitle = “Trying to re-schedule ?…sorry…”
Dim NewDateRow As Long
Dim i As Long
Dim iTargetCol As Integer
Dim lngRow As Long
Dim wks As Excel.Worksheet
Dim rngCell As Excel.Range

Set wks = Target.Worksheet
lngRow = Target.Row
iTargetCol = Target.Column

Application.EnableEvents = False

‘Locate the first row of the requested new date
With wks
On Error GoTo BadDate
NewDateRow = Application.WorksheetFunction.Match(Val(Target.Value), .Range(“d:d”), 0)
On Error GoTo 0

If .Cells(NewDateRow, strStatusCol) = “Closed” Then GoTo BadDate

‘Populate the “Rescheduled from” cell
‘ ## APPEARS TO BE POPULATING COL D, BUT THAT’S THE ONE WE CHANGED ANYWAY?! ##
‘ Target.Value = .Cells(Target.Row, iTargetCol – iOffset + 1)

‘Locate the first available row
For i = 0 To 10
If .Cells(NewDateRow + i, strDataStartCol) = “” Then
Exit For
End If
Next i
If i = 11 Then
GoTo BadDate
Else
NewDateRow = NewDateRow + i
End If

‘Move data to new row for columns 1-6 before target
For Each rngCell In .Range(.Cells(NewDateRow, strDataStartCol), .Cells(NewDateRow, strDataEndCol)).Cells
rngCell.Value = .Cells(lngRow, rngCell.Column).Value
If Right(UCase(Target.Value), 1) = “M” Then .Cells(lngRow, rngCell.Column).ClearContents
Next rngCell

Target.ClearContents

.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, _
AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
End With
GoTo ExitHandler

BadDate:
On Error GoTo 0
MsgBox “” & “The selected date is not a Court day.”, _
vbCritical, MsgTitle
Target = “”

ExitHandler:
Application.EnableEvents = True
End Sub
Public Sub MoveData(lngOld As Long, lngNew As Long, strCol As String)
Range(strCol & lngNew) = Range(strCol & lngOld)
Range(strCol & lngOld).ClearContents
End Sub

……to move (or copy) the data in columns F:AE from one row to another (eg: from row 25 to row 47), you go to B25 and enter 47 (to copy) or 47M (to move) the data to row 47……it the sheet is protected, it triggers the ENDMACRO MsgBox (“You did not enter a valid time”) and doesn’t perform the copy (move)………if the sheet is unprotected, it works fine….my diffciulty is knowing where to insert the UNPROTECT and PROTECT code…..does this explain it better?

Hi Steve….I went to Chip Pearson’s site and found some code that enables me to enter a time (eg: 9:30am) as 930 in column K, and it will auto insert the colon separator….however, if the worksheet is protected, if I enter something in column B, it triggers the EndMacro message box and the macro won’t run….if I unprotect the sheet, it works fine…..can you tell me what to add to the Chip Pearson code that will fix this? I imagine it is some sort of ActiveSheet.Unprotect line (and a Protect line at the end, but I don’t know where to insert these lines into the code…I have attached the code as a text file…thank you.

Will do…thank you again.

What is the Hive Cleanup service? Do I need it, or if not, how do I shut it off…..I am on a network at my office and I also have ‘work’ VPN on my computer, so if I shut off the Hive Cleanup and deleted userenv.log, will that impair my access or use of the network?

Hi Joe….is it even I file that I (or the computer) needs? I recall seeing a couple of posts that said the file isn’t needed for ordinary, everyday computing….what do you think?

Hi Joe….is it even I file that I (or the computer) needs? I recall seeing a couple of posts that said the file isn’t needed for ordinary, everyday computing….what do you think?

Hi all…thanks for your help….I was able to do the following to fix this problem, altho I am not sure exactly what it is that did the trick….I had run a CHKDSK but this did not correct or fix the corrupt and unreadable file (userenv.log). So, I started in SAFE mode again and this time, I went to C:WINDOWSDebugUsermodeuserenv.log and renamed the userenv.log file as userenv.bak Then, I rebooted and it re-created userenv.log and everything was fixed…..not sure why but it worked.