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

 

[autigers]

The only code I have that this is applicable to is for the end of shift codes.
1) Exports hours into record of Daily Prod from DataInput sheet
2) Inserts record from frm Daily production detail to DailyProd
3) Builds Memo section for a turnover
4) Exports from DailyProd to Sheet "DailyNote" in report form
5) Exports Shift data for Lotus Notes automated copy picture/paste/send
6) Exports Daily Prod data to Access database via

Dim wrkJet As WorkSpace
  Dim dbsJtest As Database
  Dim rstJtest As Recordset
  Dim sht1 As Worksheet

So you can see there are quite a number of areas that would reuire tailoring to suit the If in each code set ......

As for permissons .... they are set via

Environ("username")

Your thoughts ?

 

[Fat Cat]

Hmm, looks like a pretty big project in the making.

I'd sketch out a flow diagram of what you want to happen from when the workbook is first opened and then take it step by step coding each part as you go.

Do all these 6 things happen for each opening of the workbook ?
If so, I'd probably set up each task as a stand alone Public Sub in module1 and then pass to the Sub all the info it needs to know what to do, where to get its data and where to send it etc.

That way each task is self contained and can be worked on till its right. Your main code then would be just logic and questions/answers to determine the info to pass to each Sub

Its raining cats and dogs here (very noisy) so my apologies if I have got the wrong end of the stick

cheers

Mark