+ Reply to Thread
Results 1 to 3 of 3

Visibility Property as variable

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Visibility Property as variable

    Hello,

    I am trying to make the visibility property (True or false) as a variable based on the selection of a value in the VBA. But it is giving me an error. Is that not possible?
    Please help...

    PHP Code: 
    Sub Worksheet_Change(ByVal Target As Range)
    Dim Selection As String
    'List of checklists below
    Checklists = Array("ABC", "DEF", "XYZ")

    If Target.Row = 7 And Target.Column = 13 Then '
    To activate the macro only when the dropdown selection changes

    Dim sirt 
    As String
    sirt 
    True



    For Each ShtName In Checklists
    Worksheets
    (ShtName).Visible False
    Next ShtName

    Selection 
    Range("M7").Value

         

        
    If Selection "Show All" Then 'To show all checklist with Show all is selected
            
                For Each ShtName In Checklists
                Worksheets(ShtName).Visible = True
                Next ShtName
            
            ElseIf Selection <> "None" Then
            
            
                Worksheets(Selection).Visible = sirt
            

        End If

    End If


    End Sub 
    Getting error on
    PHP Code: 
    Worksheets(Selection).Visible sirt 
    Please make the Post as solved, when you get your answer & Click * if you like my suggestion

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Visibility Property as variable

    I am actually trying to shorten the code to something like below

    PHP Code: 
    Sub Worksheet_Change(ByVal Target As Range)
    Dim Selection As String
    Dim Vis 
    As String

    'List of checklists below
    Checklists = Array("CL General Document", "CL Testcase", "CL FHA Confirmation", "CL Safety Plan Confirmation", "CL Prod. Integration&Validation", "CL Safety-Analyses Confirmation", "CL SW tool Qualifi Confirmation", "CL Functional Safety Assessment", "CL Requirement Review", "CL Design Review (Sys Arch)", "CL Design Review (SW Arch)", "CL Design Review (Software)")

    If Target.Row = 7 And Target.Column = 13 Then '
    To activate the macro only when the dropdown selection changes

    Selection 
    Range("M7").Value

    If Selection "Show All" Then Vis True Else Vis False

    For Each ShtName In Checklists
    Worksheets
    (ShtName).Visible Vis
    Next ShtName


            
            
    ElseIf Selection <> "None" Then
            
            
                Worksheets
    (Selection).Visible True
            


        End 
    If




    End Sub 

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Visibility Property as variable

    you have declared the variable as string. Try declaring as variant instead

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Modifying the column or row property of a variable?
    By Googlyhead in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-16-2014, 07:56 PM
  2. [SOLVED] Command Bar - Changing a variable's property?
    By kickme93 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-06-2013, 07:37 AM
  3. merge string and variable into property
    By Bambucha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 01:15 PM
  4. [SOLVED] Using a Variable in Range Property
    By Gard5096 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 12-24-2012, 12:13 PM
  5. [SOLVED] Use variable property name
    By konfis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2012, 06:20 AM
  6. [SOLVED] How to capture a Property as a variable
    By TISR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2006, 08:35 AM
  7. how to use object variable to change the property of a
    By Fendic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2005, 02:12 PM
  8. variable for columns(??).property
    By Neal Zimm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1