Long VB If statement need shortened

[autigers]

Is there a better way to set this code up ?

Private Sub Workbook_Open()

On Error GoTo TheEnd

Sheets("Config").Activate
If Environ("username") = ("ouaaye") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "     Figure you earned it !!"
End With
End If
If Environ("username") = ("OWNER") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("inakgr") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("appjcl") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("inabgi") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
Application.ScreenUpdating = False

 

[acw]

Hi

How about

Private Sub Workbook_Open()

On Error GoTo TheEnd

Sheets("Config").Activate
 If Environ("username") = "ouaaye" Or Environ("username") = "OWNER" Or Environ("username") = "inakgr" Or Environ("username") = "appjcl" Or Environ("username") = "inabgi" Then
   Ans = Cells(2, FindColumn("Config", Environ("username")))
   MsgBox "The password to access the VB environment is  " & Ans & "     Figure you earned it !!"
 End If

Application.ScreenUpdating = False

I'm assuming that FindColumn is a function that requires the sheet name and username and there is more code to follow.


Tony

 

[Tazguy37]

Perhaps:

Private Sub Workbook_Open()

    On Error GoTo TheEnd                'Not sure what this is for
    Sheets("Config").Activate
    If Environ("username") = ("OWNER") Or Environ("username") = ("ouaaye") Or Environ("username") = ("inakgr") Or Environ("username") = ("appjcl") Or Environ("username") = ("inabgi") Then
        With Sheets("Config")
            TheAns = FindColumn(.Name, Environ("username"))
            ans = Cells(2, TheAns)
        End With
        If Environ("username") = ("ouaaye") Then
            MsgBox "The password to access the VB environment is  " & ans & " Figure you earned it !!"
        Else
            MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
        End If
    End If
    Application.ScreenUpdating = False  'This looks to be in the wrong place

End Sub

See inline comments.

 

[autigers]

Below is the entire code for what is going on at wb open ... I am attempting to allow a msgbox with vb properties password to be shown on open to certain users of the wb ... i.e IT department/ admin ...

Both codes above do not populate a message box, I tried that so "Or" and it would only allow a msgbox for the first user in the statement ....

JC

Option Compare Text

Private Sub Workbook_Open()

On Error GoTo TheEnd
Sheets("Config").Activate
If Environ("username") = ("ouaaye") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "     Figure you earned it !!"
End With
End If
If Environ("username") = ("OWNER") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("inakgr") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("appjcl") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If
If Environ("username") = ("inabgi") Then
        With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
        MsgBox "The password to access the VB environment is  " & ans & "                             :O)"
End With
End If

Application.ScreenUpdating = False
Call HideSheets
Call ShowSheets


TheEnd:
Sheets("Log").Visible = xlSheetVisible


'add username date and time
Sheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value = UserName
Sheets("Log").Range("C65536").End(xlUp).Offset(1, 0).Value = NameOfComputer
Sheets("Log").Range("D65536").End(xlUp).Offset(1, 0).Value = Date
Sheets("Log").Range("D65536").End(xlUp).NumberFormat = "dd mmm yyyy"
Sheets("Log").Range("E65536").End(xlUp).Offset(1, 0).Value = Time

'Application.ScreenUpdating = True
Sheets("DailyInput").Select
    
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo TheEnd
    Call HideSheets
TheEnd:
End Sub

Private Sub ShowSheets()
'SHOW ALL SHEETS LISTED IN THE USERS COLUMN ON CONFIG SHEET

On Error GoTo TheEnd

With Sheets("Config")
TheCol = FindColumn(.Name, Environ("username"))

If TheCol = 0 Then GoTo TheEnd

' go thru list and un-hide
For rw = 2 To .Cells(65536, TheCol).End(xlUp).Row
    Shname = .Cells(rw, TheCol).Cells
    If SheetExists(Shname) Then Sheets(Shname).Visible = xlSheetVisible
Next rw
Exit Sub
End With
TheEnd:
pt = MsgBox("Problem occurred hiding sheets.", vbCritical, "Process Failed")
End Sub

Public Function FindColumn(Sh, Hdr) As Integer
With Worksheets(Sh).Rows("1:1")
    Set c = .Find(Hdr, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        FindColumn = c.Column
    Else
        FindColumn = 0
    End If
End With
End Function

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function

 

[autigers]

My apologies TAZ, the code your provided worked .... The other (although looks very similar only showed msgbox the first name was logged in ...

 

[acw]

Hi

Does the messagebox appear with nothing in it or does it not appear at all??? If the former, and it hasn't been saved with the right sheet as the active sheet when it is opened, the it will appear as a blank.

The row
Ans = Cells(2, FindColumn("Config", Environ("username")))
will have to be changed to
Ans = sheets("whatever").Cells(2, FindColumn("Config", Environ("username")))

Also, missed the different messages as noted by Tasguy....

Tony

 

[Fat Cat]

Haven't been able to test this code, so apologies if it doesn't work;

Private Sub Workbook_Open()

Dim goodGuy As Boolean
Dim msgText(2) As String

On Error GoTo TheEnd

Sheets("Config").Activate

Select Case Environ("username")
Case "ouaaye"
    goodGuy = True
    msgText(1) = "The password to access the VB environment is  "
    msgText(2) = "Figure you earned it !!"
Case "OWNER", "inakgr", "appjcl", "inabgi"
    goodGuy = True
    msgText(1) = "The password to access the VB environment is  "
    msgText(2) = "                             :O)"
Case Else
    goodGuy = False
    msgText(1) = ""
    msgText(2) = ""
End Select

If goodGuy = True Then
    With Sheets("Config")
        TheAns = FindColumn(.Name, Environ("username"))
        ans = Cells(2, TheAns)
    End With
    MsgBox (msgText(1) & ans & msgText(2))
End If
goodGuy = False

 

[autigers]

Thats pretty slick Fat Cat .... worked like a charm on all UID's .... Alot of code runing at startup. Was able to peice it all together from this awesome board ....

ACW - I posted my code hoping that would answer Taz'a quez ... Where should the APP.screenupdate be located ?

 

[Tazguy37]

ACW - I posted my code hoping that would answer Taz'a quez ... Where should the APP.screenupdate be located ?

It's looks OK now, but without all the code, it was a bit hard to figure out why it was there.

 

[autigers]

Hi

Does the messagebox appear with nothing in it or does it not appear at all??? If the former, and it hasn't been saved with the right sheet as the active sheet when it is opened, the it will appear as a blank.

The row
Ans = Cells(2, FindColumn("Config", Environ("username")))
will have to be changed to
Ans = sheets("whatever").Cells(2, FindColumn("Config", Environ("username")))

Also, missed the different messages as noted by Tasguy....

Tony

The msgbox appears with nothing in it ... tried your change and that corrected it.
Thanks for all the input ..