asked on

Hide a form control based on the value of a cell or named range

Hello - I am trying to hide an excel form control if a specified cell has a value of "".

Below is what I have so far. You may notice I am using two different methods to refereance the form control below. Neither method is working.

Public Sub TextBox1_Change()

'Trying two different ways...found this method looking at a previous post to the forum.
If Range("DirectRpt1").Value = "" Then Sheets("Direct Reports").Shapes("Check Box 33").Visible = False

'This is the method I used originally
If Range("DirectRpt2").Value = "" Then [Check Box 34].Visible = False
If Range("DirectRpt3").Value = "" Then [Check Box 35].Visible = False
If Range("DirectRpt4").Value = "" Then [Check Box 36].Visible = False
If Range("DirectRpt27").Value = "" Then [Check Box 59].Visible = False
If Range("DirectRpt28").Value = "" Then [Check Box 60].Visible = False
If Range("DirectRpt29").Value = "" Then [Check Box 61].Visible = False
If Range("DirectRpt30").Value = "" Then [Check Box 62].Visible = False

End Sub

ASKER CERTIFIED SOLUTION

Hermani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

krishnakrkc

Hi

Try

Private Sub TextBox1_Change()
    Sheets("Direct Reports").Shapes("Check Box 33").Visible = Range("DirectRpt1").Value <> vbNullString
End Sub

Open in new window

Kris

Swapnil Nirmal

keep all checkbox hidden at first.

Use worksheet_change, if there is value make it visible with the code you mentioned.

What is the problem in your code? what are you not able to achieve

Cynthia Hill

ASKER

Hermani - I found your post helpful.

I couldn't get it to work with the exact code structure you provided, but was able to modify it to make it work for me. Thanks!

I have a follow-up question though. Below is the code I came up with. It works without an END IF statement. In fact, when I tried to add the END If...it stopped working (?). How / where do I need to add the End If's? Would I need just one END IF after all the If-Then-Else statements? Or one END IF after every single If-Then-Else statment (resulting in multiple END IF statements)?

Private Sub TextBox3_Change()

Application.ScreenUpdating = False

If Sheets("Direct Reports").Range("AL45").Value = 1 Then
Sheet14.Shapes("Check Box 33").Visible = True
Else Sheet14.Shapes("Check Box 33").Visible = False

If Sheets("Direct Reports").Range("AL46").Value = 1 Then
Sheet14.Shapes("Check Box 34").Visible = True
Else Sheet14.Shapes("Check Box 34").Visible = False

Application.ScreenUpdating = True

End Sub

SOLUTION

Swapnil Nirmal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial