Macro IF statement to make Forms button visible/invisible

[zac_fozz]

I am relatively new to macros. I'm using Excel 97. I've made a spreadsheet for our office staff to use to enter their hours worked (and vacation taken, etc.) for the week.

At the top of the sheet, the user chooses their name from a drop down menu (in cell B3), created by a Data Validation List. Then they enter their hours. At the bottom of the sheet there is a cell (E20) for them to enter their initials. I've worked up a rather complicated IF statement to conditionally format the "initials cell" yellow when the name at the top corresponds to the appropriate initials.

I've also created a button (using the forms toolbar, not the control toolbar) that the user will click to save the spreadsheet to a folder on our network. What I am trying to do is make that button invisible until an IF statement is true.

Here's the code for the IF statement (it's actually two IF statements but I'd like it to be one, I hit the maximum 7 nested IF's limit):

=IF(B3="Lolly Lou",OR(E20="LL",E20="LAL"),IF(B3="Sunny Day",OR(E20="SD",E20="SDM"),IF(B3="Sad Dog",OR(E20="SD",E20="SAD"),IF(B3="Ziggy Farce",OR(E20="ZDF",E20="ZF")))))

=IF(B3="Wally Hideaway",OR(E20="WLH",E20="WH",E20="BLH",E20="BH"),IF(B3="Jilly Willy",OR(E20="JW"),IF(B3="Lucas Yucas",OR(E20="LBY",E20="LY"))))

And here's the code to hide the button (I got this from another thread):

Sub Button23_Click()

Dim cmd As Object
    Set cmd = Worksheets("Timesheet").Shapes("Button 23")
    
    cmd.OLEFormat.Object.Visible = Not cmd.OLEFormat.Object.Visible

End Sub

Sorry this post is so long. Can anyone help?
Thanks!

 

[BrianB]

When using VBA it is rarely necessary to use complicated formulas (which is why I am not very good at them)

Here I use a lookup list of names & initials. The Names column can be the one used for data validation. Because of possible duplicate names we need to use Find rather than VLOOKUP().

I prefer to use "Enabled" rather than "Visible" because the latter is a nuisance in setting up.

'================================================================================
'- CHECK VALID ENTRY IN CELL (E20) AGAINST A LOOKUP LIST
'- USING WORKSHEET CHANGE EVENT
'- this code goes into the Worksheet code module. Right click tab. View code.
'================================================================================
'- Needs a 2 column lookup list for Name - Initials
'- Here :
'-          Name is in cell B3. Initials entered into E20
'-          Lookup Column 1 : is 'Name' Data Validation List range "I2:I20"
'-          Lookup column 2 : 'Initials' range "J2:J20"
'- Brian Baulsom January 2010
'--------------------------------------------------------------------------------
Dim Initials As String
Dim FoundCell As Range
Dim FoundValid As Boolean
'================================================================================
Private Sub Worksheet_Change(ByVal Target As Range)
'================================================================================
    If Target.Address <> "$E$20" Then Exit Sub
    '----------------------------------------------------------------------------
    FoundValid = False
    '- FIND NAME IN THE LOOKUP LIST
    '- (cannot use VLOOKUP() for possible duplicate names)
    Set FoundCell = Range("I2:I20").Find(What:=Range("B3").Value) ' LOOK UP NAME
    '----------------------------------------------------------------------------
    '- VALUE FOUND
    If Not FoundCell Is Nothing Then
        FirstAddress = FoundCell.Address
        '------------------------------------------------------------------------
        '- FIND : LOOP THROUGH LOOKUP LIST (may be duplicate names)
        Do
            '- GET INITIALS OF FOUND NAME (can use upper/lower case initials)
            Initials = UCase(FoundCell.Offset(0, 1).Value) ' convert to upper case
            If UCase(Target.Value) = Initials Then
                FoundValid = True   ' MATCH FOUND
                Exit Do             ' GET OUT OF THE LOOP
            End If
            '-------------------------------------------
            '- LOOK FOR ANOTHER MATCH
            Set FoundCell = Cells.FindNext(FoundCell)
        Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
        '-----------------------------------------------------------------------
        '- CHECK RESULT
        If FoundValid = True Then
            ActiveSheet.Buttons("Button 23").Enabled = True
            'ActiveSheet.Buttons("Button 23").Visible = True
        Else
            MsgBox (Range("B3").Value & " " & Range("E20").Value & vbCr _
                        & "Invalid Name/Iinitals combination")
        End If
    End If
    '----------------------------------------------------------------------------
End Sub
'================================================================================