Results 1 to 4 of 4

VBA Hide text boxes based on Cell value, Code?

This is a discussion on VBA Hide text boxes based on Cell value, Code? within the Excel Questions forums, part of the Question Forums category; I have the code below on a worksheet to modify a form based if it's a Purchase order or not. ...

  1. #1
    Board Regular
    Join Date
    Feb 2005
    Posts
    62

    Default VBA Hide text boxes based on Cell value, Code?

    I have the code below on a worksheet to modify a form based if it's a Purchase order or not. It works but it seems like it could be much shorter. I tried several different ways to write it but I can't get it to work any other way. I was hoping someone could tell me if there is a better way to do this or if this is the correct way.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' If cell E5 = "Purchase Order" remove sales tax % from E33 and Hide Text Boxes 1 and 8
        
        If Target.Address = Range("E5").Address Then
            If Target = "Purchase Order" Then
                Range("E33").Value = 0
                With ActiveSheet.Shapes("Text Box 1")
                    If ActiveSheet.Range("E5") = "Purchase Order" Then
                     .Visible = False
                    End If
                End With
                With ActiveSheet.Shapes("Text Box 8")
                    If ActiveSheet.Range("E5") = "Purchase Order" Then
                      .Visible = False
                     End If
                End With
                Else
                    Range("E33").Value = 0.06
                With ActiveSheet.Shapes("Text Box 8")
                    If ActiveSheet.Range("E5") <> "Purchase Order" Then
                      .Visible = True
                    End If
                End With
                With ActiveSheet.Shapes("Text Box 1")
                    If ActiveSheet.Range("E5") <> "Purchase Order" Then
                     .Visible = True
                    End If
                End With
             End If
        End If
    End Sub
    Thanks for any help
    Tom

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,049

    Default Re: VBA Hide text boxes based on Cell value, Code?

    I haven't tested it, but maybe...

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' If cell E5 = "Purchase Order" remove sales tax % from E33 and Hide Text Boxes 1 and 8
        
        If Target.Address = Range("E5").Address Then
            If Target.Value = "Purchase Order" Then
                Range("E33").Value = 0
                ActiveSheet.Shapes.Range(Array("Text Box 1", "Text Box 8")).Visible = False
            Else
                Range("E33").Value = 0.06
                ActiveSheet.Shapes.Range(Array("Text Box 1", "Text Box 8")).Visible = True
             End If
        End If
        
    End Sub
    
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Posts
    62

    Default Re: VBA Hide text boxes based on Cell value, Code?

    Perfect! I tried something very similar and it would hide the text boxes but it wouldn't unhide them, not sure why.

    Thanks Domenic!
    Tom

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,049

    Default Re: VBA Hide text boxes based on Cell value, Code?

    You're very welcome! Glad I could help!

    Cheers!
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com