Using UserName in Select Case

[jomili]

I have a macro, code below, which fires when the workbook is opened. It checks to see if the username is written in column A of Sheet2; if it is, it writes the date beside it. If it isn't, it writes the username in the next open cell in column A, puts the date beside it, saves itself, then displays a timed messag of my choosing.

Right now I can put the UserName (StrUser) in my message, but I want to personalize it more by putting the actual name (User) in the message, and I'm trying it with a Select Case routine, but it's not working. I always plugs in "I don't know who you are", though I've verified my StrUser name is correct. I'd appreciate it if anyone can point out my problem in the code below. BTW, the "Sheet1.Select" statements are because I want my users to only be looking at sheet1.

Thanks.

Option Explicit
Private Sub Workbook_Open()

    Dim strUser As String, lrow As Long
    strUser = Environ("username")
    Dim cell As Range
    Dim User As String
    

Application.ScreenUpdating = False
Sheet1.Select
    
' check to see if the file was opened in readonly mode, if so exit sub
    If Me.ReadOnly = True Then Exit Sub
        
' can write and save changes - not read only
With Sheets("Sheet2")
        ' can write and save changes - not read only
        For Each cell In .UsedRange
            If cell.Value = strUser Then
                cell.Offset(0, 1).Value = Date
                Exit Sub ' username is present
            End If
        Next cell
    
       With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Value = strUser
            .Offset(0, 1).Value = Date
        End With
  
       Select Case Cells(Rows.Count, 1).End(xlUp).Text

          Case "LUPOREJ"
            
             User = "John"

          'Case "IDUNNO"

              'User = "Somebody else"
        
          Case Else

              User = "I don't know who you are."

    End Select
    End With
    Me.Save 'save now to capture username in log
       Sheet1.Select
        CreateObject("WScript.Shell").Popup "Hello" & User & " This is a first attempt at a messaging system within Excel for specific worksheets." & vbCrLf & "This workbook will close itself after 7 seconds, which should be enough time to read this message." & vbCrLf & "If I've done it right you should not see this msgbox again.  Thanks for trying it.", 7, "This Msgbox will close itself."
        'Run "Msgbox_Test()"
            
           
           
   Application.ScreenUpdating = True
   

End Sub

[davegugg]

I think it should be:

Select Case .Cells(Rows.Count, 1).End(xlUp).Text

instead of

Select Case Cells(Rows.Count, 1).End(xlUp).Text

I believe without that extra period in front of cells, it refers to the active sheet, which would be sheet 1 in your case.

If that doesn't do it, try doing a

Debug.Print Cells(Rows.Count, 1).End(xlUp).Text

right before the select case statement and see what the immediate window says is there.

Just out of curiosity, is there a reason you are using .text instead of .value?

[snb]

somewhat simpler:

Private Sub Workbook_Open()
  on error resume next
  If Me.ReadOnly = True Then Exit Sub

  sheet2.columns(1).find(Environ("username")).offset(,1)=format(date,"dd-mm-yyyy")
  if err.number>0 then sheet2.Cells(.Rows.Count, 1).End(xlUp).Offset(1).resize(,2)=split(Environ("username")&"|"&format(date,"dd-mm-yyyy"),"|")

   CreateObject("WScript.Shell").Popup "Hello " & Environ("Username") & iif(err.number>0,", I don't know who you are", "") & ". This is a first attempt at a messaging system within Excel for specific worksheets." & vbCr & "This workbook will close itself after 7 seconds, which should be enough time to read this message." & vbCr & "If I've done it right you should not see this msgbox again.  Thanks for trying it.", 7, "This Msgbox will close itself."

   Application.ScreenUpdating = True
End Sub

[jomili]

Dave,
You were right on the dot! I can't believe one period made the difference, but it sure did. I would never have found that. Thanks so much!

SNB,

I like your simpler code, but have some questions:
I want to exchange the Environ("Username") for their actual name, and don't see a routine in your code to do that. For instance, in using Dave's solution, the message prints up as "Hello John" instead of "Hello LUPOREJ".

[jomili]

SNB,

I'm having trouble implementing your code. I'm getting a Compile Error at this line

Sheet2.Columns(1).Find(Environ("username")).Offset(, 1) = Format(Date, "dd-mm-yyyy")
  If Err.Number > 0 Then Sheet2.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Split(Environ("username") & "|" & Format(Date, "dd-mm-yyyy"), "|")

It's highlighting the "(.Rows." piece. Here's the full macro I tried to make using your code:

Option Explicit
Private Sub Workbook_Open()
Dim User As String
  On Error Resume Next
' check to see if the file was opened in readonly mode, if so exit sub
  If Me.ReadOnly = True Then Exit Sub
Application.ScreenUpdating = False
Sheet1.Select
        
' can write and save changes - not read only
Sheet2.Columns(1).Find(Environ("username")).Offset(, 1) = Format(Date, "dd-mm-yyyy")
  If Err.Number > 0 Then Sheet2.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Split(Environ("username") & "|" & Format(Date, "dd-mm-yyyy"), "|")
            
       Select Case .Cells(Rows.Count, 1).End(xlUp).Text

           Case "LUPOREJ"
            
             User = "John"

          'Case "IDUNNO"

              'User = "Somebody else"
        
          Case Else

              User = "I don't know who you are."

    End Select
    Me.Save 'save now to capture username in log
       Sheet1.Select

        CreateObject("WScript.Shell").Popup "    Hello " & User & ", this is a first attempt at a messaging system within Excel for specific worksheets." & vbCrLf & "This message will self-destruct after 10 seconds, which should be enough time to read this message." & vbCrLf & "    If I've done it right you should not see this msgbox again.  Thanks for trying it.", 10, "This Msgbox will close itself."
           
   Application.ScreenUpdating = True
   
End Sub

Also, anybody; the UserName may come in in all caps, or all lowercase, or a mixture. How do I configure my Select Case to recognize the UserName regardless of the case?

[Leith Ross]

Hello jomili,

To make the Case case insensitive, you can first convert your string to all upper or lower case letters.

I don't see a With statement before the Select Case. The period in front of Cells will cause you problems. You should remove it to refer to the ActiveSheet.

      Select Case UCase(.Cells(Rows.Count, 1).End(xlUp).Text)

           Case "LUPOREJ"
            
             User = "John"

          'Case "IDUNNO"

              'User = "Somebody else"
        
          Case Else

              User = "I don't know who you are."

    End Select

[Kyle123]

I put a sheet2. in the below and it works fine now

With Sheets("Sheet2")
            For Each cell In sheet2.Range("A1:A25") 
            If cell.Value = strUser Then
                cell.Offset(0, 1).Value = Date
                Exit Sub ' username is present
            End If
        Next cell

[jomili]

When I use your code I get a compile error, Variable not Defined, highlighting the "Sheet2" I just added. I'm using Excel 2003.

[Kyle123]

Thats odd , have you changed the Name of Sheet2 in the properties window? If so try the below

For Each cell In Sheets("sheet2").Range("A1:A25")

I think your problem is that you have selected sheet 1, the code is then looping through column A in sheet 1 rather than 2.

[jomili]

Sorry about the oddness. Yes, turns out Sheet2 was actually named Sheet3(Sheet2). I renamed Sheet3 to Sheet2 and now that part appears to be working, so I think it's all working right now. Thanks so much for sticking with me and helping me get through this one. I'll mark it solved now.