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

 

[Fat Cat]

It must be something to do with the format of the SDate.Text matching that of the cells on the sheet or where it is looking.

Try changing the "what" assignment to a date that you know is on the sheet
eg;

what = DateValue("10/10/2005")

and then see if it finds it.

If it does, its a Format problem

If it doesn't, its probably looking in the wrong place

I got this code working OK;

Private Sub CommandButton1_Click()

Dim found As Boolean
Dim what As Variant
Dim sht1 As Worksheet

what = DateValue("10/10/03")                            'this is what to look for
Set sht1 = Sheets("DailyProd")                  'this is the sheet to look in

With sht1.Range("A:A")                          'look at column A
    Set c = .Find(what, LookIn:=xlValues)
    If Not c Is Nothing Then                    'we found the text
        ans = MsgBox("There is a record already created for  & Me.Sdate.Text" & vbCr & vbCr & "Confirm OVERWRITE of existing data ?", vbYesNoCancel, "Overwrite Data ?")
        Select Case ans
        Case 6      'Yes
                Set TargetCell = Range(c.Address)
                'add code here for logging etc
        Case Else   'No, Cancel or X
                Exit Sub
        End Select
    Else            'we didn't find it
        Set TargetCell = Range(Range("A65536").End(xlUp).Address).Offset(1, 0)
    End If
End With

Call TransferData(TargetCell.Address)           'call the data transfer sub passing the target cell address

End Sub

 

[jindon]

Hi

See if this works

Dim x, y
x = [{"ouaaye","OWNER","inakgr","appjcl","inabgi"}]
y = Application.Match(Environ("username"), x, 0)
If Not IsError(y) Then
    If y = 1 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
    Else
        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
End If

 

[autigers]

It must be something to do with the format of the SDate.Text matching that of the cells on the sheet or where it is looking.

If it does, its a Format problem

Used yours as test and found it ... rechecked the formats and found that looking for mmddyyyy when format of cell was long date ....
Not sure why format changed like that, so I placed code in before running button code to make sure the format of colum was correct ....

Thanks guys !!!!

 

[autigers]

ok now that one is resolved .... the next part of the code is still having trouble ...it still enters data at row 15 ....

 

[autigers]

Got it ..... Guess I should have told you that before that ok button was pressed, there was a different entry from a group on the same form, but selected a different sheet the hid it. After hiding it the next sheet would become active which was a config sheet and the first available row was guess which one !!! yep .... 15 !!!
But I was thinking that by using Tony's solution

Hi

The address for the next record is being determined from the current sheet rather than trying to find the next blank row on the output sheet (Dailyprod)

Try changing
Set TargetCell = Range(Range("A65536").End(xlUp).Address).Offset(1, 0)
to
Set TargetCell = sheets("Dailyprod").Range(Range("A65536").End(xlUp).Address).Offset(1, 0)


Tony

... maybe i put it in the wrong place ... still confused ... Got it to work by selecting Sheets("DailyProd") at beginning of code

 

[Fat Cat]

Hi autigers,

Sorry I didn't get back to you sooner, feeding kids and all that entails.

I had assumed that by specifying sht1 for the With statement

With sht1.Range("A:A")

that everything inside the With .. End With would reference sht1.

Your experience teaches me this is not the case, so I shall be wary of it.

Selecting the sheet would ensure you are getting the correct last row.
Alternatively, you could force this by adding sht1. infront of every Range statement

eg;

Private Sub CommandButton1_Click() 

Dim found As Boolean 
Dim what As Variant 
Dim sht1 As Worksheet 

what = DateValue(Me.SDate.Text)             'this is what to look for 
Set sht1 = Sheets("DailyProd")                  'this is the sheet to look in 

With sht1.Range("A:A")                          'look at column A 
    Set c = .Find(what, LookIn:=xlValues) 
    If Not c Is Nothing Then                    'we found the text 
        ans = MsgBox("There is a record already created for  & Me.Sdate.Text" & vbCr & vbCr & "Confirm OVERWRITE of existing data ?", vbYesNoCancel, "Overwrite Data ?") 
        Select Case ans 
        Case 6      'Yes 
                Set TargetCell = sht1.Range(c.Address) 
                'add code here for logging etc 
        Case Else   'No, Cancel or X 
                Exit Sub 
        End Select 
    Else            'we didn't find it 
        Set TargetCell = sht1.Range(Range("A65536").End(xlUp).Address).Offset(1, 0) 
    End If 
End With 

Call TransferData(TargetCell.Address)           'call the data transfer sub passing the target cell address 

End Sub

 

[autigers]

Agreed ... one would think that by referencing the sheet within the With would call that sheet or at least make it the target sheet .... odd ....
I have used your code and works flawlessly now ....


I understand the kids thing ... my turn tonight .... Mr.Mom !!!

Have a good one

Jamison

 

[acw]

Jamison

What version of Excel are you using???


Tony

 

[autigers]

Jamison

What version of Excel are you using???

Hi Tony

At work which is where this app will run is 2000. QVC is planning on upgrades to 2003 this summer.

I run '03 at home

 

[autigers]

Hi

See if this works

Dim x, y
x = [{"ouaaye","OWNER","inakgr","appjcl","inabgi"}]
y = Application.Match(Environ("username"), x, 0)
If Not IsError(y) Then
    If y = 1 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
    Else
        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
End If

Hi jindon
The code works just fine. Thank you