• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1027
  • Last Modified:

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
0
Cynthia Hill
Asked:
Cynthia Hill
2 Solutions
 
HermaniCommented:
Your code seems ok, did you put it inside Private Sub Worksheet_Change(ByVal Target As Range) ?

Simple example : 1 sheet with a control ("button 1") that is vissible when E6 is filled in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = "$E$6") Then
        If (Target.Value = "") Then
            Sheet1.Shapes("Button 1").Visible = False
        
        Else:
            Sheet1.Shapes("Button 1").Visible = True
        
        End If
        
    End If
    
End Sub

Open in new window

0
 
krishnakrkcCommented:
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
0
 
Swapnil NirmalCommented:
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
0
 
Cynthia HillLead ConsultantAuthor Commented:
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
0
 
Swapnil NirmalCommented:
Like this:


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
End if

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

Application.ScreenUpdating = True

End Sub 

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now