Macro IF statement to make Forms button visible/invisible

zac_fozz

New Member
Joined
Jan 5, 2010
Messages
6
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):

Code:
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.

Code:
'================================================================================
'- 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
'================================================================================
 

Forum statistics

Threads
1,136,778
Messages
5,677,679
Members
419,712
Latest member
LearningCR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top