Replies

Whew. That does make a big difference. I missed that from his earlier message, and I didn’t download the original attachment.

Agreed that the reply was standard, but Rudi is missing the DAO library?! That is a pretty serious library to be lacking… I would think that a re-install of MS Access might be in order.

You’re most welcome.

As a charter member of the [slap to the forehead] club, I can assure you that we have a lot of company.

Is the Patient ID field indexed in the Pathology Report and Image tables? If so, are duplicates allowed?

I agree that formulas cannot apply formatting in the sense of font attributes (bold, underline, italics, size).

Part of my earlier suggestion involved implementing the TEXT function so that the value in B2 would match the “format” of the first column in his lookup table which included leading zeros. I am curious if the poster has tried this suggestion.

I applied the range name MyTable to Provider!$A$5:$C$1179.

Then I used the following formula:

=VLOOKUP(TEXT(TRIM(B2),”000000″),MyTable,3,0)

It seemed to work. For non-matches (#N/A), you might want to alter the formula a bit.

Regarding column G in the “Downloaded Sep 06 PS Expense” sheet:

You are applying a Trim function to the lookup table (Payroll JE_W_Cash Benefits!$N$8:$O$177). The only column that needs to be trimmed is column N of that block. Typically if I’m using a column or lookup purposes, I’ll make sure that all leading spaces have been trimmed. It cuts down on a lot of overhead in my formulas.

You are also applying a Value function to a non-numeric result.

Your original formula was:
=IF(ISNA(VLOOKUP(TRIM(B3),TRIM(‘PAYROLL JE_ W_ CASH BENEFITS’!$N$8:$O$177,1,FALSE))),0,VALUE(VLOOKUP(TRIM(B3),TRIM(‘PAYROLL JE_ W_ CASH BENEFITS’!$N$8:$O$177,1,FALSE))))

I reduced it to:
=IF(ISNA(VLOOKUP(TRIM(B3),’PAYROLL JE_ W_ CASH BENEFITS’!$N$8:$O$177,1,FALSE)),0,VLOOKUP(TRIM(B3),’PAYROLL JE_ W_ CASH BENEFITS’!$N$8:$O$177,1,FALSE))

You are returning the first column from the lookup table? If that was really your intention. you could get by with:

=IF(ISNA(VLOOKUP(TRIM(B3),’PAYROLL JE_ W_ CASH BENEFITS’!$N$8:$O$177,1,FALSE)),0,B3)

Actually the fGetFullNameOfLoggedUser routine does exactly what you requested.

Before we edit your routine, what do you want to do to all of the selected Worksheets? It is often advantageous not to select worksheets (or cells, rows, etc.).

The following routine identifies all of the worksheets in the active workbook that are not named “data”:

Public Sub FindMySheets()
On Error goto Err_FindMySheets

      Dim i As Integer, iIndex As Integer
      Dim sWorksheets() As String

      With Application.ActiveWorkbook
          For i = 1 To .Worksheets.Count
               If .Sheets(i).Name  "data" Then
                   iIndex = iIndex + 1
                   ReDim Preserve sWorksheets(iIndex)
                End If
          Next i
     End With

     '**** Do something here with the array

Exit_FindMySheets:
     Exit Sub

Err_FindMySheets:
     Call ErrHandler()
     Resume Exit_FindMySheets

End Sub

If my not-so-vivid imagination, your warning form was going to stay open for a very brief period and then close itself….or get closed when the terminating routine is called.

Elsewhere, like maybe embedded in your MainMenu, switchboard, etc. is a ShutdownIfUserIsLazy routine that is called periodically. It checks to see how long your user has been latent. If the inactivity period exceeds your tolerance, the ShutdownIfUserIsLazy closes the application.

Whenever a user does something, like makes a form dirty (edits data), the LASTACTION will need to be updated.

Public Const  WARNINGTIME as Integer = 55
Public Const  HASTALAVISTA as Integer = 60
Public LASTACTION as Date

Public Sub UpdateMyTimer()
          LASTACTION = VBA.Now()
End Sub


Public Sub ShutdownIfUserIsLazy ()
On Error goto Err_ShutdownIfUserIsLazy 

         Dim dtLatency as Date

         dtLatency = VBA.DateDiff("n", VBA.Now(), LASTACTION)

         if dtLatency = WARNINGTIME then Call ShowWarning()
         if dtLatency >= HASTALAVISTA then DoCmd.Quit

Exit_ShutdownIfUserIsLazy :
         Exit Sub

Err_ShutdownIfUserIsLazy :
         Call ErrHandler(.....)
         Resume Exit_ShutdownIfUserIsLazy 

End Sub         

Or

DoCmd.Quit Options:=acQuitSaveNone

Either way, the cat is skinned.

How do you intend to display your warning message? If you are intending to use a MsgBox call, please don’t. Your code will tend to be interrupted until the user clicks OK to close the message box.

You are better off displaying a form with the message appearing on it.

Any particular reason that a user would be saving things when closing out of the application? In general my users don’t save any objects in my applications. The only changes they make to forms are modifications to filters. Typically those don’t need to be saved.

When I select “show comment” from the context menu, I am able to edit Comment 2…as opposed to selecting Edit Comment from the context menu…

Odd problem.

For future consideration, and I realize nobody asked, consider using a constant for a password.

Suppose my password for protecting 20 of my 24 worksheets is “w1dget”. Rather than search and replace “w1dget” in code, I put the following in a module’s general declaration:

Public Const IGNOREME as String = "w1dget"

Whenever I need to supply my password in code, I refer to IGNOREME instead. Whenever the password is changed, I only need to change this one line in the declarations area, instead searching and replacing.

Your choice of a decent constant name will no doubt be better.

Why are you re-sizing text boxes? As an alternative have you set the CanGrow property to Yes?