Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hidding sheets all but one (2003)

    Hello,

    I am trying to create a macro that when the user presses the "select" buttom, they will be shown only the worksheet that they have selected from the inputsheet worksheet. The selection "Type of Transaction" will determine the sheet they will see and all other ones will be hidden including the "Inputsheet" worksheet. For example, if they select-company 1, input invoice, they will only get the "Input Invoice" worksheet. I am also attaching a small file for reference. Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hidding sheets all but one (2003)

    You've used check boxes for "Select type of transaction", so the user can tick more than one of them. What should happen if multiple check boxes are ticked? Leave all related sheets visible?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hidding sheets all but one (2003)

    I was trying to use the option boxes for the second part in the "Select Type of Transaction" ,but when I did that it would uncheck the "Select Company" ones. I would like to use options boxes instead on both. Is there a way to do this? This way they can only select one.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hidding sheets all but one (2003)

    You can use option buttons and put a group box around them - the button for the Group Box on the Forms toolbar is shown below.
    Attached Images Attached Images

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hidding sheets all but one (2003)

    OK. Thanks for the info Hans. I updated the sheet, but I still need to be able to do the function above in my prior post. I also forgot to mention that once the user press the "Select" buttom, the "InputSheet" should have everything uncheck for the next user. Any help would be great.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hidding sheets all but one (2003)

    You can assign this macro to the Select button.
    <code>
    Sub SelectIT()
    Dim strSheetName
    Dim wsh As Worksheet
    Dim shp As Shape
    If ActiveSheet.Shapes("Option Button 23").ControlFormat.Value = xlOn Then
    strSheetName = "Invoice Input"
    ElseIf ActiveSheet.Shapes("Option Button 24").ControlFormat.Value = xlOn Then
    strSheetName = "Manager Approve Invoice"
    ElseIf ActiveSheet.Shapes("Option Button 25").ControlFormat.Value = xlOn Then
    strSheetName = "Input amendment"
    ElseIf ActiveSheet.Shapes("Option Button 26").ControlFormat.Value = xlOn Then
    strSheetName = "BUDGET LINE ITEM TRANSFER"
    ElseIf ActiveSheet.Shapes("Option Button 27").ControlFormat.Value = xlOn Then
    strSheetName = "Invoice adjustments"
    End If
    For Each shp In ActiveSheet.Shapes
    If Left(shp.Name, 6) = "Option" Then
    shp.ControlFormat.Value = xlOff
    End If
    Next shp
    With Worksheets(strSheetName)
    .Visible = xlSheetVisible
    .Select
    End With
    For Each wsh In Worksheets
    wsh.Visible = (UCase(wsh.Name) = UCase(strSheetName))
    Next wsh
    End Sub
    </code>
    Replace "Option Button 23" etc. with the names of the option buttons you have created for the 5 sheets. The names should begin with Option.

Posting Permissions

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